How do I interpret the date from a CSVDE export?

My domain runs two domain controllers with Windows Server 2003.  I am trying to get data to support an Active Directory cleanup, so I need a clean, usable export of Active Directory using CSVDE.  I have my command written using the filters I need, and it exports beautifully except for one thing:  The dates and times that I need are in numerical format (I believe it is Unicode), for example 129207559115462000 is one date/time for a user's last logon.  The closest I have come to it Julian time / universal time, but it doesn't match up.  Can anyone help me to figure out what date and time this should be?  Is there a script that will convert these values to a more recognizable format?
laugleAsked:
Who is Participating?
 
simonseztechConnect With a Mentor Commented:
csvde to excel Human readable LastLogon or LastLogonTimestamphttp://myserverstuff.blogspot.com/2009/03/csvde-to-excel-human-readable-lastlogon.htmlThe 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")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).
0
 
Mike KlineCommented:
I'd suggest using other tools for decoding  adfind and powershell (quest cmdlets) are two tools that will decode for you
adfind from Joe Richards http://www.joeware.net/freetools/tools/adfind/index.htm
adfind -default  -f "&(objectcategory=person)(objectclass=user)" samaccountname userprincipalname displayname lastlogontimestamp -csv -tdca > c:\LastLogon.csv
by the way if you are trying to cleanup old computers and/or users then old computer by Joe Richards is another great tool (has -users switch)
http://www.joeware.net/freetools/tools/oldcmp/index.htm
Thanks
Mike
0
 
Adam BrownSr Solutions ArchitectCommented:
That's actually an LDAP timestamp. This site: http://www.morecavalier.com/index.php?whom=Apps%2FLDAP+timestamp+converter will allow you to convert it to readable time.
0
 
laugleAuthor Commented:
This is exactly what I needed!  A thousand thank yous!  ACBrown2010 - your solution works if I only had a few dates to convert - I have thousands, so the Excel command was what solved my problem.  Thanks to all for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.