Is there a way to optimize my join search?

[monteirolopes]

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.

[somesoni2 · Accepted solution]

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"

[dolivasoh]

Nice one. I didn't think of coalescing until after I posted and I was already heading to bed.

[monteirolopes]

Thank you!

[dolivasoh]

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"