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!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...