Solved

How do I interpret the date from a CSVDE export?

Posted on 2010-09-22
4
2,838 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:laugle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 57

Expert Comment

by:Mike Kline
ID: 33738787
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
 
LVL 7

Accepted Solution

by:
simonseztech earned 250 total points
ID: 33738811
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
 
LVL 40

Expert Comment

by:Adam Brown
ID: 33738818
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
 

Author Closing Comment

by:laugle
ID: 33738942
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In-place Upgrading Dirsync to Azure AD Connect
This article demonstrates probably the easiest way to configure domain-wide tier isolation within Active Directory. If you do not know tier isolation read https://technet.microsoft.com/en-us/windows-server-docs/security/securing-privileged-access/s…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question