Monday, March 30, 2009

csvde to excel Human readable LastLogon or LastLogonTimestamp

I was given the task of making a human legible report of Users, OUs, and Last Login Times. Easy enough right? I logged in to one of the servers as an admin and ran


F:\>csvde -r "(objectClass=user)" -f output.csv
-l cn,givenName,sn,n,ou,lastLogon

* note.. all that is on one line. I wrapped it because blogger was cutting it off.

That worked great, but excel choked on the dates. It turns out this is because the dates are not "normal" timestamp dates. "Normal" timestamps are based on the number of seconds since midnight on 1/1/1970. The timestamps in Active Directory in UTC format, a 64 bit number based on the number of nanosecond since 1/1/1601 divided by 100! For even more complexity, Excel's dates are based on the number of days since 1/1/1900. Arrgh.

As theoretical knowledge goes, that is all fine and good.. but how do we use it in excel?

The formula to convert from Active Directory LastLogon or LastLogonTimestamp is:


=IF(C2>0,C2/(8.64*10^11) - 109205,"")


Explanation:
  • C2 is the cell that contains the Timestamp.

  • The If() statement hides the value if the user has not logged in.

  • (8.64*10^11) is the number of nanoseconds in a day divided by 100.

  • 109205 is the number of days, including leap days, between 1601 and 1900. (Remember, 1900 is when excel dates "start")



That's it. Paste in your formula and format it as a date, or date/time. The times returned are in GMT. All of the other solutions I saw in my google searches pointed to a vbScript solution. Please leave a comment if this helps you.

Thanks,
Ellie

P.s. If you want it in Central US time (GMT-6), subtract 0.25 (That is 6 hours divided by 24 hours in a day). For Eastern time (GMT-5), subtract 0.208333333 .. (5/24).

52 comments:

Ionee said...

Really nice solution! i was also looking for a non-vb solution with excel! thank you!

Anonymous said...

You are GREAT!!!!! Thanks so much. This is what i needed. I would like to ask though i am unsure how to have it display Eastern Time (US & Canada. I cant seem to get the fomula correct. Thanks again

ronald

Ellie said...

Ronald, you didn't leave me an email address to reply to.. here is the formula for US Eastern Time (GMT-5)

=IF(C2>0,C2/(8.64*10^11) - 109205.20833333333,"")

Thanks,
Ellie

Anonymous said...

Thanks again. I am curious though if it may be off. The reason I say is I logged on at about 7:53 this morning but it is showing 8:53 as the log on time. Is it possible it may be an hour off.

My email is ...@... (removed -ellie)

Thanks so much again

Ellie said...

It’s because of daylight savings time..
Between the second Sunday in March and the first Sunday in November Eastern time is GMT – 4 and the formula is

=IF(C2>0,C2/(8.64*10^11) - 109205.166666666666,"")

I'll dig around and see if I can find a way to compensate for daylight savings time.

Anonymous said...

You just don't know how thankful i am for your post. It has helped a great deal and i do thank you.

worked great


ronald

Ranga said...

This is awesome!! Great solution.

Anonymous said...

Brilliant!!

Anonymous said...

Been looking for a formula like this for the past 3 hours! THANKYOU ELLIE!

Anonymous said...

Ellie,

I live in a time zone of GMT +1. What would the formula be? I just couldnt figure it out :$

my email is b1llybr0wn@yahoo.co.uk

Thanks! :)

Anonymous said...

WOW - that was really a great solution!! I, like others didn't want to write a script and create another file. This was fantastic. However, maybe I copied it incorrectly as Excel would not let me overlay the C2 cell with this formula...I had to place it in a row next to it and point to C2. Not a big thing but in case future users run into this problem.

Thanks again for such an innovative fix!!!

Anonymous said...

This is the kind of solution I was thinking about, I got trouble doing the text conversion but it shouldn't be as complicated as a vba script since mathematicaly sounds so simple! Thank you very much!!!!

ravi said...

This is fantastic. You have made the CSVDE function very appealing to ordinary system administrators. Many thanks for sharing this.

ravi said...

If your time zone is GMT+10, subtract 10/24 (with 7-8 decimals) leading to 109204.58333333
If it is GMT-10, Add 10/24 leading to 109205.0.416666666666667.
Hope this helps.

Anonymous said...

Absolute Star - You have sorted a really annoying problem for me

Thanks very much!!

Nilesh Chhajed said...

Thank you. Amazingly simple solution.

Kartikesh Nadar said...

Works as a charm Ellie. Thank you from the bottom of my heart, you really rock.

Szabolcs said...
This comment has been removed by the author.
Szab said...

To fully understand the math background here is the "open-formula", which can be easily understood and customized for local time zones. As we know the Active Directory timestamp is the nanoseconds elapsed after 1601 January 1st... or as I call it this is the "Pre Jeanne d'Arc timeformat" ;-) (Yeah. Who the hell's idea was that? :-) So, to have it in readable format using Excel, we have to convert it into seconds->minutes->hours->and days, then add the 1601.01.01. date, and add/or/subtract the UTC difference hours at the end. Of course the A1 cell should be modified to be the first relevant cell used in excel. The x variable in this formula should be replaced with a number that represents the difference between the UTC and your local time. Do not give negative numbers there, just do a subtract instead. Finally you have to customize the cell format in excel to be date, and for the format choose the: 3/14/01 13:30 option for the whole column.

=(((((A1*0.0000001)/60)/60)/24))+DATE(1601,1,1)+TIME(x,0,0)

Anonymous said...

Great Post.... i tried to apply the same formula for the AccountExpires attributes but i get a ######### result. Anyone has the formula for this one ???

Thanks.

Karl Foley said...

Thanks for this - Certainly saved me working it out!

Anonymous said...

certainly helped a lot

thank you.

BMac said...

One other note to consider. Domain controllers do not synchronize the last logon attribute. So you will need to capture the user data from all the DCs and use the most recent logon as the last logon. See the Scripting Guy's notes: http://207.46.16.252/en-us/magazine/2006.01.scriptingguy.aspx

Justin Bennett said...

Thanks for the excell formula! You rock!

Anonymous said...

Thx a lot for this magical Excel formula :)

Mark said...

Great formula - just what I was looking for. Thanks.

Kerry said...

Excellent! Works like a charm, thanks!

Clark said...

Hi Ellie,

This is great, i've just managed to identify over 100 users who have not logged in this month.

Made my day a lot easier.

Thanks

Anonymous said...

This is good stuff and also just to add if you want to convert the GMT to CST (my time zone) 5 hr variance you can use this formula
=F2-TIME(5,0,0)

Sridhar said...

Ellie, Thank you, you rock

Sridhar

Anonymous said...

Ellie, can you kindly share the formula for GMT +5:30 time zone

Elizabeth Greene said...

For India standard time, it is


=IF(C2>0,C2/(8.64*10^11) - 109205+0.2291,"")

Anonymous said...

GREAT Solution, thanks a lot

tomislav said...

Help me please.
I dont know how to put this formula in Excel. I got error every time.

Anonymous said...

You are amazing! I kept looking at other websites and even when the individual asked for a solution in Excel, the response was always a script! This has made my day. Thanks!

Paul said...

Very useful, saved me lots of head scratching! Thanks for sharing.

Patrick Strydom said...

Hi,

Firstly - Very good post!!

Secondly; please excuse the ignorance here but I really never work within Excel. I've applied this to my spread sheet but it returns "#VALUE" in the field, even after I've formatted it as a Date column. Any help would be great!

Patrick

Richard Mueller said...

First, the formual above works great for any Active Directory Integer8 date (represented by a 64-bit integer), including accountExpires, pwdLastSet, and lastLogonTimeStamp. The constant 109205 in the formula works, but actually the number of days between January 1, 1601 (the zero date for Integer8 values in AD) and December 31, 1899 (the zero date for Excel) is actually 109206. But Excel has a bug. If you check the date that corresponds to 1 in Excel, you get January 1, 1900. You find that 59 corresponds to February 28, 1900, 60 corresponds to February 29, 1900, and 61 corresponds to March 1, 1900. But 1900 was not a leap year. There was no February 29, 1900. To adjust for this bug, you use 109205 in the formula.

Anonymous said...

Very nice.
Thank you very much.

Anonymous said...

Yep, you rock!

Anonymous said...

Thank you! It works!

Евгений Котляревский said...

Hi
I've encouneterd some problem:
I'v done an export, but field 'last logon' mostly is empty. Some users have correct lastlogon stamp, but most of users have an empty field. If I view this atribute in atribute editor, user have a correct lastlogon stamp.

Евгений Котляревский said...
This comment has been removed by the author.
Anonymous said...

Hi Team,

In my export data the last logon value is showing like this 1.30066E+17

Kindly let me know how can i covert this to normal data.

Elizabeth Greene said...

Anonymous:
1.30066E+17 is the number in scientific notation. It's equivalent to 1.30066 * 10^23 or 1.30066 with the decimal point moved 23 places to the right. If you dig around in the formatting menu in excel you can turn that off.
Good luck!

Anonymous said...

Sweet. Thank you.

ahamed said...

my value is this = 130113846391280478

=IF(C43789>0,C43789/(8.64*10^11) - 109205)

4/25/13 5:30 PM


=((C43789/10000000/3600/24)-109205) -0.208333333

4/25/13 12:30 PM


but actual value should be 4/25/13 1:30 PM

it is EST timings I believe it is showing in daylight savings. Please help to get the output as 1:30PM

Anonymous said...

Thanks!
This has saved me so much time and effort.

I had tried a formula that added 100ns increments to a date of 1/1/1601 but Excel didn't like dates that long ago. :-)


Alan

Anonymous said...

Still helping people in 2013! Thanks!

Anonymous said...

Nice Solution saved me hours of pissing about with powershell

Tim Keeler said...

I'm having a problem with the formula and my timestamp: 130279977730036000

=IF(C2>0,C2/(8.64*10^11) - 109205,"")

renders: 11/5/17 0:16 (which is incorrect)

correct:
w32tm.exe /ntte 130279977730036000
150787 00:16:13.0036000 - 11/3/2013 4:16:13 PM

Any thoughts on what I'm doing wrong?

Anonymous said...

Just another thank you - five years later!