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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...