3

I have exported a list of AD Users out of AD and need to validate their login times.

The output from the powershell script give lastlogin as LDAP/FILE time

EXAMPLE 130305048577611542

I am having trouble converting this to readable time in pandas

Im using the following code:

df['date of login'] = pd.to_datetime(df['FileTime'], unit='ns')

The column FileTime contains time formatted like the EXAMPLE above.

Im getting the following output in my new column date of login

EXAMPLE 1974-02-17 03:50:48.577611542

I know this is being parsed incorrectly as when i input this date time on a online converter i get this output

EXAMPLE:

Epoch/Unix time: 1386031258 GMT: Tuesday, December 3, 2013 12:40:58 AM Your time zone: Monday, December 2, 2013 4:40:58 PM GMT-08:00

Anyone have an idea of what occuring here why are all my dates in the 1970'

InBox
  • 41
  • 1
  • 2
  • 4
  • I would consider it a duplicate of https://stackoverflow.com/questions/4869769/convert-64-bit-windows-date-time-in-python – schlenk Jul 26 '18 at 20:41
  • Possible duplicate of [convert 64 bit windows date time in python](https://stackoverflow.com/questions/4869769/convert-64-bit-windows-date-time-in-python) – schlenk Jul 26 '18 at 20:42

3 Answers3

9

The 18-digit Active Directory timestamps (LDAP), also named 'Windows NT time format', 'Win32 FILETIME or SYSTEMTIME', or NTFS file time.

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.

Therefore, 130305048577611542 does indeed relate to December 3, 2013.

When putting this value through the date time function in Python, it is truncating the value to nine digits. Therefore the timestamp becomes 130305048 and goes from 1.1.1970 which does result in a 1974 date!

In order to get the correct Unix timestamp you need to do:

(130305048577611542 / 10000000) - 11644473600

Saikat
  • 14,222
  • 20
  • 104
  • 125
Jim Grant
  • 1,128
  • 2
  • 13
  • 31
2

Here's a solution I did in Python that worked well for me:

import datetime

def ad_timestamp(timestamp):
    if timestamp != 0:
        return datetime.datetime(1601, 1, 1) + datetime.timedelta(seconds=timestamp/10000000)
    return np.nan

So then if you need to convert a Pandas column:

df.lastLogonTimestamp = df.lastLogonTimestamp.fillna(0).apply(ad_timestamp)

Note: I needed to use fillna before using apply. Also, since I filled with 0's, I checked for that in the conversion function about, if timestamp != 0. Hope that makes sense. It's extra stuff but you may need it to convert the column in question.

R.F.
  • 73
  • 7
1

I've been stuck on this for couple of days. But now i am ready to share really working solution in more easy to use form:

import datetime
timestamp = 132375402928051110
value = datetime.datetime (1601, 1, 1) +   
datetime.timedelta(seconds=timestamp/10000000) ### combine str 3 and 4  
print(value.strftime('%Y-%m-%d %H:%M:%S'))
Vasiliy.Koshkovsky
  • 11
  • 2