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!

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...

The Visibility Gap: Hybrid Networks and IT Services

The most forward thinking enterprises among us see their network as much more than infrastructure – it's their ...

Get Operational Insights Quickly with Natural Language on the Splunk Platform

In today’s fast-paced digital world, turning data into actionable insights is essential for success. With ...