Getting Data In

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

DPWSplunkPOC
Explorer

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.

0 Karma
1 Solution

somesoni2
Revered Legend

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,"%+")

View solution in original post

somesoni2
Revered Legend

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
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi DPWSplunkPOC
did you tried?

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

Bye.
Giuseppe

0 Karma

DPWSplunkPOC
Explorer

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

did you tried

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

Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...