Splunk Search

How to convert msDS-UserPasswordExpiryTimeComputed in date?

Stitif
Observer

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 

Labels (1)
Tags (3)
0 Karma

gcusello
Esteemed Legend

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

0 Karma

Stitif
Observer

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.

0 Karma

gcusello
Esteemed Legend

Hi @Stitif,

could you share a sample of your logs?

Ciao.

Giuseppe

0 Karma

Stitif
Observer

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust
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

 

 

0 Karma
Get Updates on the Splunk Community!

Observability Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestSplunk APM's New Tag Filter ExperienceSplunk APM has updated ...

Security Newsletter Updates | March 2023

 March 2023 | Check out the latest and greatestUnify Your Security Operations with Splunk Mission Control The ...

Platform Newsletter Highlights | March 2023

 March 2023 | Check out the latest and greatestIntroducing Splunk Edge Processor, simplified data ...