Splunk Enterprise Security
Highlighted

How to convert Active DIrectory AccountExpires field into a date code

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
Highlighted

Re: How to convert Active DIrectory AccountExpires field into a date code

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.