topic How to convert msDS-UserPasswordExpiryTimeComputed in date? in Splunk Search

How to convert msDS-UserPasswordExpiryTimeComputed in date?

Stitif — Sun, 27 Nov 2022 16:16:30 GMT

Hi,

From splunk search how to convert "msDS-UserPasswordExpiryTimeComputed" value recover from AD in date ? I wish to convert the value with splunk command in date.

Thank you

Re: Convert msDS-UserPasswordExpiryTimeComputed in date

gcusello — Sat, 26 Nov 2022 10:44:20 GMT

Hi @Stitif.

did you tried using eval strftime?

<your_search> | eval "msDS-UserPasswordExpiryTimeComputed"=strftime("msDS-UserPasswordExpiryTimeComputed","%Y-%m-%d %H:%M:%S")

One additional info: don't use "-" in fied names, otherwise you need to use quotes because this char ir considered by Splunk the as minus, instead use "_".

If you have from the source, rename it.

Ciao.

Giuseppe

Re: Convert msDS-UserPasswordExpiryTimeComputed in date

Stitif — Sat, 26 Nov 2022 11:14:11 GMT

Unfortunately not. The value of msDS-UserPasswordExpiryTimeComputed is a "LargeInteger Date". Through the link an exemple is shared but I don't interprate the value as exemple.

Re: Convert msDS-UserPasswordExpiryTimeComputed in date

yuanliu — Sun, 27 Nov 2022 06:24:59 GMT

LargeInteger Date is a 64-bit value that represents the number of 100-nanosecond intervals that have elapsed since 12:00 A.M. January 1, 1601 Coordinated Universal Time (UTC)

In other words, it is 11644473600 * 10000 larger than the same representation in SPL. (Convert Windows Filetime to second in Unix/Linux.)

| eval spl_time_100ns = tonumber('msDS-UserPasswordExpiryTimeComputed') - 116444736000000

The result is still in 100 ns. You can convert to second, too

| eval spl_time_s = tonumber('msDS-UserPasswordExpiryTimeComputed')/10000 - 11644473600

Re: Convert msDS-UserPasswordExpiryTimeComputed in date

gcusello — Sun, 27 Nov 2022 11:24:32 GMT

Hi @Stitif,

could you share a sample of your logs?

Ciao.

Giuseppe

Re: Convert msDS-UserPasswordExpiryTimeComputed in date

Stitif — Sun, 27 Nov 2022 19:00:53 GMT

Hi @gcusello

By powershell I check Active Directory attribute of target user thanks to the following command :
get-aduser <targetUser> -Properties msDS-UserPasswordExpiryTimeComputed | select-object "msDS-UserPasswordExpiryTimeComputed"

msDS-UserPasswordExpiryTimeComputed
-----------------------------------
133008713865298786

So I use [datetime]::FromFileTime() to transform into date :

get-aduser <targetUser> -Properties msDS-UserPasswordExpiryTimeComputed | select-object @{Name="ExpiryDate";Expression={[datetime]::FromFileTime($_."msDS-UserPasswordExpiryTimeComputed")}}

I got this output :
ExpiryDate
--------------
28.06.2022 08:29:46

Side splunk I recover the input as well, I select target user with command :
index=<apps_index> <targetUser> | eval spl_time_s = tonumber('data.msDS-UserPasswordExpiryTimeComputed')/10000 - 11644473600
| table data.samAccountName,data.msDS-UserPasswordExpiryTimeComputed,spl_time_s

I got this input :
data.samAccountName | data.msDS-UserPasswordExpiryTimeComputed | spl_time_s
<targetUser> | 133008713865298786 | 13289226912929.879

I try to get recover side splunk date with day month year.

I tried the same exercise with another AD attribute. LastLogonDate where the date is already in the good format
get-aduser <targetUser> -Properties lastlogondate | select-object lastlogondate

lastlogondate
-------------
29.04.2022 08:30:00

Side splunk I display by a table data:
index=<apps_index> <targetUser> | table data.samAccountName,data.LastLogonDate

data.samAccountName | data.LastLogonDate
<targetUser> | /Date(1651213800071)/

I don't find the expression to display the date correctly for both attributes

Best Regards,

Stitif