Hi,
I did a report that lists users who have not logged on for more than 60 days and the status of users aren't disable/deleted on Active Directory. For this, I used the command join
to merge the distinct searches.
Here is my search:
sourcetype=ActiveDirectory | dedup sAMAccountName | eval x = strptime(lastLogonTimestamp, "%H:%M.%S %p, %a %m/%d/%Y") | where x < (now() - (86400 * 60)) | JOIN sAMAccountName [ search sourcetype="WinEventLog:Security" EventCode != 4725 OR EventCode != 4726 | dedup user | eval sAMAccountName = user ] | table sAMAccountName, lastLogonTimestamp | rename sAMAccountName AS "User" lastLogonTimestamp AS "Last Login"
There is a way to optimize this search?
Best regards,
Lopes.
Try like this
(sourcetype=ActiveDirectory) OR (sourcetype="WinEventLog:Security" EventCode != 4725 OR EventCode != 4726)
| eval User=coalesce(sAMAccountName, user) | stats latest(lastLogonTimestamp) as lastLogonTimestamp values(sourcetype) as sourcetype by User | where strptime(lastLogonTimestamp, "%H:%M.%S %p, %a %m/%d/%Y") < relative_time(now(),"-60d")
| table User lastLogonTimestamp | rename lastLogonTimestamp AS "Last Login"
Try like this
(sourcetype=ActiveDirectory) OR (sourcetype="WinEventLog:Security" EventCode != 4725 OR EventCode != 4726)
| eval User=coalesce(sAMAccountName, user) | stats latest(lastLogonTimestamp) as lastLogonTimestamp values(sourcetype) as sourcetype by User | where strptime(lastLogonTimestamp, "%H:%M.%S %p, %a %m/%d/%Y") < relative_time(now(),"-60d")
| table User lastLogonTimestamp | rename lastLogonTimestamp AS "Last Login"
Nice one. I didn't think of coalescing until after I posted and I was already heading to bed.
Thank you!
Try this
(sourcetype=ActiveDirectory) OR (sourcetype="WinEventLog:Security" EventCode != 4725 OR EventCode != 4726) | eval x = strptime(lastLogonTimestamp, "%H:%M.%S %p, %a %m/%d/%Y") | where x < (now() - (86400 * 60)) | rename user as sAMAccountName | stats latest(lastLogonTimestamp) as lastLogonTimestamp by sAMAccountName | rename sAMAccountName AS "User" lastLogonTimestamp AS "Last Login"