+ Reply to Thread
Results 1 to 7 of 7

Converting AD Field 'lastLogon' to Date & Time

  1. #1
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38

    Converting AD Field 'lastLogon' to Date & Time

    Hi All

    I've extracted data from Active Directory using the CSVDE command and I've been able to manipulate most of the info so that it's nice and user friendly but I'm struggling with the 'lastLogon' field. It gives a number like128601615869175000 which I believe can be converted to a date and time but I'm unsure how. I've found DOS commands and .vbs scripts that will prossibly convert it but I would ideally like an Excel forumula to sort the conversion.

    Has anyone got such a beast or is it not possible?

    Thanks for any help in advance.

    Brelin

  2. #2
    Registered User
    Join Date
    06-24-2008
    Location
    Cambridge UK
    Posts
    53
    The lastLogon attribute is stored in Active Directory as Integer8 (8 bytes). This means it is a 64-bit number, which cannot be handled directly by VBScript. Instead, the LDAP IADsLargeInteger interface provides HighPart and LowPart methods that break the number into two 32-bit components. The resulting value represents the number of 100 nanosecond intervals since 12:00 AM January 1, 1601. The date represented by this number is in Coordinated Universal Time (UTC). It must be adjusted by the time zone bias in the local machine registry to convert to local time.
    From http://www.rlmueller.net/Last%20Logon.htm

  3. #3
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    Thanks for the definition.

    Am I misreading it or can I just start dividing nanoseconds into that number and then converting that all back to dates & times? Not a quick job I can see but it's all I've got at the minute.

    If anyone has any additonal thoughts I'm listening.

    Thanks

    Brelin

  4. #4
    Registered User
    Join Date
    01-22-2009
    Location
    New Plymouth, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    1
    OK so I went looking for an answer to the same question on Friday, the closest I could find to it was this forum, but still no go, so I put some time and thought into it and came up with something, so here goes:

    First up I needed to figure out how to get excel to deal with the now - 1 Jan 1601 issue.

    Now Excel will not deal with dates any earlier than 1 Jan 1900. If you were to convert a date in Excel it would come back with a number, each whole number is the number of days from 1 Jan 1900 (which if you convert comes out as 1, 2 Jan 1900 as 2 etc.)
    Then I needed to know how many days there have been since 1 Jan 1601 and 1 Jan 1900, taking into account leap years (every 4th year except the century unless the century is evenly dividable by 400), the answer I came up with is 190207 days.

    Next up I needed to convert that pesky number of 100 nanosecond intervals since 1601 number into days, so I used the following formula:
    =(CellReference/10000000/3600/24)
    So the 10000000 is the number of 100ns periods in a second, divided by 60 to give hours, the by 24 to give days.

    So if you subtract 190207 from the above number you get the number of days since 1 Jan 1900, now if you convert the cell format to date it will give you the last login date

    So from the top, if the cell you are calculating from is A1, the the working formula is:

    =(A1/10000000/3600/24)-109207

    Remember to change the cell format time to date.

    There you go!

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,419
    Looks like FP about nailed it. Microsoft has a note about this at http://www.microsoft.com/technet/scr...lastlogon.mspx.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    10-14-2009
    Location
    Columbus, Ohio, USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Converting AD Field 'lastLogon' to Date & Time

    I'm not sure what time zone you are in, but I tweaked FP's formula a bit to match 'w32tm.exe /ntte' response for my local time. The previous result kept coming back a little over 4 hours different than w32tm.

    I verified the below formula with 20 different 'pwdLastSet' times for one of my clients and they all matched up. (I am in the Eastern time zone, by the way). It may be a coincidence, but I'm running with it.

    =((A1/10000000/3600/24)-109205)-0.166673

  7. #7
    Registered User
    Join Date
    01-11-2013
    Location
    Usa
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Converting AD Field 'lastLogon' to Date & Time

    I used the following =(((ROUNDDOWN(raw_date/10000000,0)/3600)+gmt_offset)/24)-109205

    The original post had an epoch correction of 109207, but I found that the one posted by OldD, 109205 agreed with w32tm /ntte raw_date. Since I am in the Eastern US timezone and it is winter, I used -5 for the gmt_offset. Before I added the rounddown, I found that some of the times were off by 1 second compared to w32tm. The 8 dates I tried from the AD (2008-2013) all agree with w32tm to the second now. This calculation would not automatically account for DST, which is left as an exercise for the reader.

    NOTE: It looks like Win32tm is rounding the final seconds down by truncating the fractional seconds.

    H:\>w32tm /ntte 129964929448137000
    150422 08:55:44.8137000 - 11/4/2012 3:55:44 AM

    While Excel's date/time format is rounding normally when it expresses the EXCEL date-time in characters. Truncating the fractional seconds as shown in the formula above is the best one can do unless there's a way to prevent EXCEL from rounding when it applies the format. The method shown could very occasionally still cause a 1 second discrepancy.
    Last edited by Kip4724; 01-11-2013 at 08:56 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1