Jump to content

Having trouble converting AD date/time to readable Date/time


Recommended Posts

Hello all.. I am trying to convert Active Directory computer account last used date to a readable format. I have some functions that will do this, but they use the following command to perform the command:

w32tm /ntte <NT time epoch>
  Convert a NT system time, in (10^-7)s intervals from 0h 1-Jan 1601,
  into a readable format.

 

This is super slow when dealing with thousands of records. Here is what I have so far...

 

#include <array.au3>
#include <DateTimeConvert.au3>
#include <Date.au3>
$exampleADTimeStamp = "130793861794571914"
Msgbox(0,"MyResult", "Timestamp: " & $exampleADTimeStamp & @crlf & "Days since last Login: " & DateStringToDaysSinceLastLogin($exampleADTimeStamp))

Func DateStringToDaysSinceLastLogin($Timestamp)
    $ParseOutput = ParseLL($Timestamp)
    $ParseOutput = StringSplit($ParseOutput, " ", 2)
    $newFinal = _DateStandardToCalcDate($ParseOutput[0])
    Return Abs(CompareD($newFinal))
EndFunc   ;==>DateStringToDaysSinceLastLogin
Func ParseLL($oLastLogon)
    $sOutput = ""
    $iPID = Run(@ComSpec & " /c " & "w32tm.exe /ntte " & $oLastLogon, @TempDir, @SW_HIDE, 2)
    While 1
        $sOutput &= StdoutRead($iPID)
        If @error Then ExitLoop
    WEnd
    $sOutput = StringSplit($sOutput, " - ", 2 + 1)
    Return $sOutput[1]
EndFunc   ;==>ParseLL
Func CompareD($Datez)
    Return _DateDiff("D", @YEAR & "/" & @MON & "/" & @MDAY, $Datez)
EndFunc   ;==>CompareD

 

I'm sure there has got to be a better way to calculate this date format without having to use a command line tool, but after searching the forums, I haven't found anything..

Does anyone have any thoughts?

 

Thanks in advance!!!

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

Found my own answer.. I just need to write up this calculation...

 

 

Convert 18-digit LDAP timestamps to human readable date & epoch

The 18-digit Active Directory timestamps, also named 'Windows NT time format' and 'Win32 FILETIME or SYSTEMTIME'. These are used in Microsoft Active Directory for pwdLastSet, accountExpires, LastLogon, LastLogonTimestamp and LastPwdSet. The timestamp is the number of 100-nanoseconds intervals (1 nanosecond = one billionth of a second) since Jan 1, 1601 UTC.

 

 

Thanks anyways! 

 

(I notice i can never find an answer unless I post the Q here first, then I find the answer in minutes.)

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

Is this accurate enough?

#include <Date.au3>
$exampleADTimeStamp = "130793861794571914"

ConsoleWrite(_DateAdd("s", StringTrimRight($exampleADTimeStamp, 7), "1601/01/01 00:00:00")  & @CRLF)

 

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
Share on other sites

Its embarrassing how long I have been working to do this and how many methods I tried, and you knocked it out in one line.

I would give you a total man hug if you were here... Many thanks!!!

 

 

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

If you are talking about property "lastlogon" search function _AD_GetObjectProperties from my AD UDF for "lastlogon" and you will see how the date is translated into readable format.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

If you are talking about property "lastlogon" search function _AD_GetObjectProperties from my AD UDF for "lastlogon" and you will see how the date is translated into readable format.

I tried this, but for some reason, in our environment, it always returned a blank value. Even a raw LDAP attribute query for a computer object returned a blank value which makes no sense.. Its not a UDF issue.. because I can pull any other attribute with no problem, but lastLogonTimeStamp is always blank.. Oddly enough, this works:

dsquery.exe * -limit 0 -filter "&(objectClass=Computer) (sAMAccountName=*)" -attr SamAccountName lastlogontimestamp

I think we just have a wonky AD environment. This is not the first time I saw something not work that very well should work.

 

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

lastlogon and lastlogontimestamp are two different properties. Did you try "lastlogon"?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

lastlogon and lastlogontimestamp are two different properties. Did you try "lastlogon"?

I did, but from what I understand from Microsoft, lastlogon is specific to the domain controller, while LastLogonTimeStamp replicates. They suggest we pull from LastLogonTimeStamp when running reports. 

 

- LastLogon:
                  When a user logs on, this attribute is updated on the Domain Controller that provided the authentication ONLY.  Because it is only updated on one DC, that means this attribute is not replicated.  While that might seem inconvenient at first, this is actually a pretty useful function.  In those cases when you may want to figure out what sites a user has logged on (or not logged on), you would use this attribute.
                  
                  - LastLogonTimeStamp

                  To summarize this attribute, this is the replicated version of the LastLogon attribute.

 

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

Correct. Then function _AD_GetLastLoginDate is what you need. The function queries all DCs and returns the correct last login date/time.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Correct. Then function _AD_GetLastLoginDate is what you need. The function queries all DCs and returns the correct last login date/time.

It looks like that ended up working, but for some reason, that query takes much longer than a simple dsquery... 

 

This takes about 18 seconds to complete for me:

#include <ad.au3>
_AD_OPEN()
Msgbox(0,"_AD_GetLastLoginDate",_AD_GetLastLoginDate(@computername & "$"))
_AD_CLOSE()

While this takes less than 1 second

dsquery.exe * -limit 0 -filter "&(objectClass=Computer) (sAMAccountName=%computername%$)" -attr SamAccountName lastlogontimestamp

 

While I normally don't mind it taking a few more seconds, I have to query a little over 5000 computer accounts.

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

_AD_GetLastLoginDate queries ALL DCs to get the correct last logon because this property is - as you have noted - not replicated to other DCs. You can limit the DCs to query to a site or a list of DCs.
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

_AD_GetLastLoginDate queries ALL DCs to get the correct last logon because this property is - as you have noted - not replicated to other DCs.
You can limit the DCs to query to a site or a list of DCs.

Perfect, ill try that!

C0d3 is P0etry( ͡° ͜ʖ ͡°)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...