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

[HannanPervez]

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.

[nickhills]

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))

[althomas]

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

| where _time>relative_time(now(),"-90d@d")