How to convert Windows LDAP 18 digit lastLogonTimestamp field to human readable format?

[DPWSplunkPOC]

I've seen lots of different solutions for converting time from epoch but I have not come across a solution that works to convert the Windows LDAP 18-digit lastLogonTimestamp field. How do I convert this field to a human readable field?

Thank you.

[somesoni2 · Accepted solution]

You can use following formula to convert LDAP/FILETIME timestamps to human readable date in Splunk. See this runanywhere sample

| gentimes start=-1 | eval time=131315659450000000 | eval time_s=(time/10000000)-11644473600 | eval time_human=strftime(time_s,"%+")

[DPWSplunkPOC]

Thank you for your answer

This worked and gave me an easy to read output from my AD data. I need to take it a step further. I need to look for users that have not logged in for 6 months.

My search looks like this:

index=myADdata
| eval lastLogon = strftime(lastLogonTimestamp/10000000-11644473600,"%m/%d/%Y")
| where last_logon < (now() - (86400 * 180))
| table cn lastLogon

[gcusello]

Hi DPWSplunkPOC
did you tried?

eval TimeStamp=strftime(_time,"%d/%m/%Y %H.%M.%S")

Bye.
Giuseppe

[DPWSplunkPOC]

Yes I have. This does not work for Windows LDAP time stamps because Active Directory stores date/time values as the number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 until the date/time that is being stored according to MS technet.

If Windows used epoch in LDAP, that eval would work.

[gcusello]

did you tried

eval TimeStamp=strftime(_time/100,"%d/%m/%Y %H.%M.%S")

Bye.
Giuseppe