Getting Data In

Active Directory Password Expiry

rhysjones
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

BobM
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

siraj198204
Explorer

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

BobM
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

rhysjones
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

dale_lakes8769
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...