Jump to content
Kovacic

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

Recommended Posts

Kovacic

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( ͡° ͜ʖ ͡°)

Share this post


Link to post
Share on other sites
Kovacic

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( ͡° ͜ʖ ͡°)

Share this post


Link to post
Share on other sites
UEZ

Is this accurate enough?

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

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

 

  • Like 1

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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
Kovacic

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( ͡° ͜ʖ ͡°)

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Kovacic

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( ͡° ͜ʖ ͡°)

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Kovacic

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( ͡° ͜ʖ ͡°)

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Kovacic

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( ͡° ͜ʖ ͡°)

Share this post


Link to post
Share on other sites
water
_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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Kovacic
_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( ͡° ͜ʖ ͡°)

Share this post


Link to post
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

  • Similar Content

    • tweakster2010
      By tweakster2010
      Hello All,
      It has been a long time since I posted, role changes etc involving work and I finally am back to modifying a program I write in AutoIt that has AD integration. I am at a point where we are modifying our structure where I am validating users access via what Distribution List they belong to in our AD OU's. What I have run into is the OU is a sub OU of a sub OU now. Meaning: CN=team, OU=DL, OU=Groups, DC=business.com(working), is now CN=Team, OU=SubDL, OU=DL, OU=Groups, DC=business.com (Not working).  Just curious if the AD functionality should be able to read it or maybe I am missing something?
       
      ElseIf _AD_IsMemberOf("CN=Team,OU=Distribution Lists,OU=Groups,DC=business,DC=com", $sFQDN_User) Then #works for primary Distro ElseIf _AD_IsMemberOf("CN=Team,OU=SubDL,OU=Distribution Lists,OU=Groups,DC=business,DC=com", $sFQDN_User) Then #fails for subOU of DL How I am accessing AD to get the information:
      _AD_Open() Global $aUser = _AD_GetObjectsInOU("", "(&(objectCategory=person)(objectClass=user)(samaccountname=" & @UserName & "))", 2, "ADsPath,Displayname,distinguishedName") Global $sDisplayName0 = $aUser[1][1] ; Displayname ;MsgBox(0,"", $sDisplayName0) If StringLeft($sDisplayName0, 2) = "9-" Then $Displaynamestring = StringTrimLeft($sDisplayName0, 2) $Displaynamestring1 = StringTrimRight($Displaynamestring, 6) The error code generated is a 1 with a 0 extended. I assume it is because it cannot find the SubOU.
       
      Thanks for any assistance.
    • antmar904
      By antmar904
      I'm trying to read all cells used in column "C" in excel to an array but not sure how.
       
      Local $NameArray = _Excel_RangeRead($oWorkbook, $oWorkbook.Activesheet, $oWorkbook.Range["C"].End)  
    • Jemboy
      By Jemboy
      Hi,
      Ones(some times twice) a month I get an e-mail with zip file, which has price updates from a supplier.
      I have to upload the file to an FTP to get it processed.
      When uploading the file, it will get "timestamped" with the time and date at which time the file was uploaded.
      Normally this is fine, because I mostly upload the file the same day.
      Sometimes it may take 1 or 2 days before I can upload the file.
      For historical purpose, I would like to have the file timestamped with the original date.
      I have tried using:  _FTP_Command ( $hFTPSession, "MFCT YYYYMMDDHHMMSS path") 
      however this command does not work or change the timestamp as I expected.
      Does anyone now a way how I can change the timestamp of a FTP-file?
    • Blois
      By Blois
      Hey Guys,
      Good?
      I'm ned help to consult in other domain. My three domain contains any domains.
      How do I get this query done?
       
      Tks for the Help!
       
×