CODEWITHSUNDEEP
active-directorydata-conversionpowerquery
SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2370

Active directory lastLogonTimestamp - convert Integer to Date

Is anyone able to help me convert the lastLogon and lastLogonTimestamp from Active Directory? I am pulling the data with Power Query and for my own user name I and the data is returned like this:

[users.lastLogonTimestamp]=131804496023891686

[users.lastLogon]=131808141012537325

I found this page on Microsoft Docs which states very clearly:

This value is stored as a large integer that represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). A value of zero means that the last logon time is unknown.

However I am struggling to get a logical result. I have tried converting nanosecond to days, and then adding the days integer to the starting date '1/1/1601' result. Since I have been actively logging in, I should be getting a date result around today's date, '09/10/2018'. 

131804496023891686 / 86,400,000,000,000 

= 1525.5150002765241435185185185185

_ 

1525 + '1/1/1601' = Wednesday, March 6, 1605

-- REFERENCES:

1) https://www.calculateme.com/time/nanoseconds/to-days/

2) https://www.timeanddate.com/date/dateadded.html?m1=01&d1=01&y1=1601&type=add&ay=&am=&aw=&ad=1525&rec=

3) https://learn.microsoft.com/en-us/windows/desktop/adschema/a-lastlogon

Upvotes: 0

Views: 10435

Answers (2)

Kwanian
Kwanian

Reputation: 11

So, for PowerShell, I put all together as following


(Get-Date '1601-01-01').AddDays([long]::parse($objItem.lastlogon)*100/86400/1000/1000/1000)

Upvotes: 0

SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2370

Okay so this is a DOH! moment... The answer was staring me in the face. I missed the fact that this was represented in 100 nanoseconds not 1 nanosecond. 

(131804496023891686*100) / 86,400,000,000,000 

152551.50002765241435185185185185

_

152551 + '1/1/1601' = Thursday, September 13, 2018

NOTE: So this result is actually 3 days in the future... not perfect, but what I am really looking for is just "Active accounts in the last 30 days", so I will consider this acceptably accurate.

This article was also helpful-- http://www.selfadsi.org/ads-attributes/user-lastLogonTimestamp.htm.

Upvotes: 1

Related Questions