topic Re: Active Directory Password Expiry in Getting Data In

Active Directory Password Expiry

rhysjones — Mon, 28 Sep 2020 10:16:28 GMT

Hello,

I have a quandry. I am attempting to create n query that returns all the users whose passwords are due to expire in the next few days. I am attempting to get this from Active Directory monitoring logs.

So, as an example, in an environment where the password change frequency is 30 days, give me a list of all the users who have NOT changed their passwords in the last 25 days. Exclude disabled accounts and computer accounts.

The query below is kind of working (thanks to other questions previously asked by other people about formatting the AD date's !!) but I am missing something obvious. I have an account with a password that was changed on 8 Dec 2011 and again on 3 Jan 2012. I limit the query date range to the last 30 days (or the last 7 days, it still does it). I am successfully filtering out the most recent ones but the change on the 8th is still appearing. I was hoping the MAX statement would rid me of those.

index="activedir" (objectCategory="CN=Person*" AND userAccountControl!=514) | eval target_date=(now() - (86400 * 25)) | eval human_time_pwd_change=strftime(pwdLastSet/10000000-11644473600,"%Y-%m-%d %H:%M:%S") | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(human_time_pwd_change) as TimeA | where TimeA<target_date | stats max(human_time_pwd_change) AS DateLastChanged by displayName

I think I have been looking at it for too long. Can anyone give me any hints ?

Thanks in advance

Rhys

Re: Active Directory Password Expiry

BobM — Thu, 05 Jan 2012 16:25:05 GMT

This seems overly complex to me and you are comparing times after they have been converted to strings which is unlikely to work. I would use the dedup command that only returns the first (latest) event by a field.

index="activedir" (objectCategory="CN=Person*" AND userAccountControl!=514) 
| dedup displayName 
| eval DateLastChanged=pwdLastSet/10000000-11644473600 
| where DateLastChanged < relative_time(now(),"-25d@d")
| table DateLastChanged displayName
| convert timeformat="%Y-%m-%d %H:%M:%S" DateLastChanged

Re: Active Directory Password Expiry

rhysjones — Thu, 05 Jan 2012 21:31:31 GMT

Fantastic, thankyou ! I made a minor alteration but it worked as I needed.

index="activedir" (objectCategory="CN=Person*" AND userAccountControl!=514)
| dedup displayName
| eval DateLastChanged=pwdLastSet/10000000-11644473600
| where DateLastChanged < relative_time(now(),"-25d@d")
| table DateLastChanged displayName | Sort -DateLastChanged
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(DateLastChanged)

Thanks again !

Rhys

Re: Active Directory Password Expiry

siraj198204 — Wed, 24 Sep 2014 18:11:40 GMT

Hi ,

Similarly ,

source="dbmo-tail://idware/id_account" application=TFD [|inputlookup execSSO.csv |rename sso as owner] |fields source,Lastpasswordchangedate,

The above query is working good.

I have the last passwordchangedate field , it is showing the date . from that i want to determine new field for password expire date . expire date time 90 days .

any one can help me on this .

i have one more question ,

10000000-11644473600 what is this ? any seconds format ?

Re: Active Directory Password Expiry

dale_lakes8769 — Fri, 26 Dec 2014 17:35:23 GMT

Not sure what is different in 6.1.5, but I had to change
... | eval DateLastChanged=pwdLastSet/10000000-11644473600 | ...
to
... | eval DateLastChanged = strptime(pwdLastSet, "%H:%M.%S %p, %a %m/%d/%Y") | ...

else DateLastChanged came back null.