Getting Data In

Active Directory Password Expiry

Path Finder

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

Tags (1)
1 Solution

Builder

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

View solution in original post

New Member

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 ?

0 Karma

Builder

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

View solution in original post

Path Finder

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

0 Karma

Explorer

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.

0 Karma