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).
61 comments:
Really nice solution! i was also looking for a non-vb solution with excel! thank you!
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
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
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
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.
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
This is awesome!! Great solution.
Brilliant!!
Been looking for a formula like this for the past 3 hours! THANKYOU ELLIE!
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! :)
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!!!
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!!!!
This is fantastic. You have made the CSVDE function very appealing to ordinary system administrators. Many thanks for sharing this.
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.
Absolute Star - You have sorted a really annoying problem for me
Thanks very much!!
Thank you. Amazingly simple solution.
Works as a charm Ellie. Thank you from the bottom of my heart, you really rock.
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)
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.
Thanks for this - Certainly saved me working it out!
certainly helped a lot
thank you.
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
Thanks for the excell formula! You rock!
Thx a lot for this magical Excel formula :)
Great formula - just what I was looking for. Thanks.
Excellent! Works like a charm, thanks!
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
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)
Ellie, Thank you, you rock
Sridhar
Ellie, can you kindly share the formula for GMT +5:30 time zone
For India standard time, it is
=IF(C2>0,C2/(8.64*10^11) - 109205+0.2291,"")
GREAT Solution, thanks a lot
Help me please.
I dont know how to put this formula in Excel. I got error every time.
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!
Very useful, saved me lots of head scratching! Thanks for sharing.
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
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.
Very nice.
Thank you very much.
Yep, you rock!
Thank you! It works!
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.
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.
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!
Sweet. Thank you.
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
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
Still helping people in 2013! Thanks!
Nice Solution saved me hours of pissing about with powershell
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?
Just another thank you - five years later!
Thank You
Thank You~~!!!!
Thank You~~!!!!
Please provide a command to change the below task.
I need too change this command from csvde -f output.csv to csvde -f output_DDMMYYY.csv
How to change the output file to below format in powershell
csvde -f output.csv to csvde -f output_DDMMYYY.csv
Excellent thanks just want observation,,,, could work with Brackets or without them i mean csvde - objectClass=user
Thanks.
Thank you so much. Such an easy solution but I am sure it took you some time to figure out. Thank you
Thank you, I will use this as example in a Access Query
Thanks,
This works perfect in LibreOffice as well :)
Post a Comment