Splunk Enterprise Security

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

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