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!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...