Number of days to AD account password expiry

[leagawa]

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)

[lmaclean]

Hi,

Believe this is what you are looking for:

| inputlookup users.csv
| fields identity, pwdLastSet
| eval dateLastSet=strptime(pwdLastSet, "%Y-%m-%dT%H:%M:%S.%6NZ")
| eval epochNextSet=(dateLastSet+7776000)
| eval diff=epochNextSet-now()
| eval days=diff/86400
| eval dateNextSet=strftime(epochNextSet, "%Y-%m-%d %H:%M:%S")

The last line is optional as it will just provide the exact date on which they will need to change their password, also here is a screenshot of what this shows. Just round the days value to whatever you want... And then add in the fields you want on the table as well.