Splunk Enterprise Security

How to convert Active DIrectory AccountExpires field into a date code

sheamus69
Communicator

The AccountExpires field in an AD log is described as:

The date when the account expires. This value represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). A value of 0 or 0x7FFFFFFFFFFFFFFF (9223372036854775807) indicates that the account never expires.
https://docs.microsoft.com/en-us/windows/win32/adschema/a-accountexpires

The long string doesn't follow the standard unix epoch time, so the strftime function doesn't seem to apply.

Does anyone know the formula for resolving this?

Sample data Set:

accountExpires, accountExpires_strftime, ActualExpiry
132066576000000000, 11:59.59 pm, Fri 12/31/9999, 03/07/2019 21:00
0, 01:00.00 am, Thu 01/01/1970, Never
131775408000000000, 11:59.59 pm, Fri 12/31/9999, 31/07/2018 21:00
131748624000000000, 11:59.59 pm, Fri 12/31/9999, 30/06/2018 21:00
131693328000000000, 11:59.59 pm, Fri 12/31/9999, 27/04/2018 21:00

Thanks in advance,
Sheamus

0 Karma

jwiedow
Communicator

@sheamus69, which events are you seeing this field in? Are you using the Splunk Supporting Add-on for Active Directory to pull your account information in for analysis with ES?

As you noted and per KB Article 555936; The Active Directory stores date/time values as the number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 till the date/time that is being stored. The time is always stored in Greenwich Mean Time (GMT) in the Active Directory.

Using that information and the reference here, the formula would be

 =(((ROUNDDOWN(raw_date/10000000,0)/3600)+gmt_offset)/24)-109205

You should be able to put formula into an eval statement for Splunk.

I asked about the Add-On for Active Directory because there is an easier method to retrieve this data by changing your ldapsearch to return the computed password expiry time in standard format. Here is a sample of what that would look like.

| ldapsearch domain=contoso.com search="(&(objectClass=user)(!(objectClass=computer)))" attr="*;msDS-UserPasswordExpiryTimeComputed"
| collect index=main sourcetype=activedirectory:json source=activedirectory_user host=ad1-contoso
0 Karma

hughkelley
Path Finder

I had to use a different calculation.  The TZ is still off, but as I'm only looking for a date,  it's close enough for my purposes.

index=wineventlog   source="WinEventLog:Security"   EventCode=5136 LDAP_Display_Name=accountExpires   Value>0  
| eval expDate=floor((Value-116444736000000000)/10000000)
| dedup _time Account_Name LDAP_Display_Name  expDate DN
| table _time Account_Name LDAP_Display_Name  expDate DN
| convert timeformat="%Y-%m-%d"  ctime(expDate)

 

I still don't understand why my TZ is wrong.  I assume the Value field is UTC and I'm subtracting the Splunk epoch (result of one-liner below) from that.

([datetime] "1970-01-01 00:00:00Z").ToFileTimeUtc()

  

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...