I have a lookup table of AD accounts
lookup table fields
CN, DisplayName, passwordlastset, pwdlastset, userAccountControl
john john Doe 8/7/2017 11:07 131465920645898409 512
Jane Jane Doe 10/31/2017 12:04 131539394829466419 514
Task: find the number of days remaining before the account passwords expire
Table results: CN, DisplayName, Passwordlastset, DaysRemaining
Time to account password expiry = 90 days
spl query:
| inputlookup xoc.csv
| dedup DisplayName
| eval DateLastChanged=pwdLastSet/10000000-11644473600
| eval start = strptime(_time, "%Y-%m-%d %H:%M:%S")
| eval end = strptime(passwordlastset, "%Y-%m-%d %H:%M:%S")
| eval duration = round((end-start)/86400)
| where DateLastChanged < relative_time(now(),"-90d@d")
| table DateLastChanged DisplayName duration
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(DateLastChanged)
... View more