How to convert Active DIrectory AccountExpires field into a date code

[sheamus69]

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

[jwiedow]

@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

[hughkelley]

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()