Getting Data In

How to look for users who haven't logged into AD in the last 90 days?

HannanPervez
Explorer

I am working on a use case which looks for Users who haven't logged into Active Directory in the last 90 days. I have stitched together a few searches I've came across but have had no luck with it.

index=wineventlog EventCode=528 OR EventCode=4624 
| eval Account_Name=mvindex(Account_Name,1) 
| eval UserAccount=coalesce(Account_Name,User_Name) 
| search NOT UserAccount="*$" NOT UserAccount=anonymous NOT UserAccount=system 
| dedup UserAccount 
| eval testDate=strptime(lastOccurence,"%Y-%m-%d") 
| eval lastMo = relative_time(now(),"-90d@d") 
| where testDate < lastMo 
| table testDate lastMo lastOccurence

I have tried using an LDAP lookup for this however, unfortunately due to the way AD has been set up in our virtualized environment, the time stamps of last logins are way off. Therefore, we have to use Windows event logs.

Thanks in advanced.

Tags (1)
0 Karma

nickhills
Ultra Champion

If you have multiple Domain Controllers, you need to query the last login time from all of them.
Last login is not synced between DCs, so this could very well be why you are seeing results which do not make sense.

If you want to do it by events, you could add something like this to the end of your search (assumes you have the TA's and CIM compliant data)

index=wineventlog EventCode=528 OR EventCode=4624 |stats latest by user,_time|where _time<(now()-(90*86400))
If my comment helps, please give it a thumbs up!

althomas
Communicator

Alternatively, you can use the slightly more user-friendly "relative_time" function:

| where _time>relative_time(now(),"-90d@d")
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...