Combining data and finding latest match with logins

[batsonpm]

I have two queries. One that pulls the login info and one that pulls the logout info. I’ve been banging my head on getting this to work the last couple of days. Maybe a fresh perspective will help me out.
The regex is removing citrix servers because I don't care about them.

:: Logouts

index=sysmon sourcetype="WinEventLog:Security" EventCode=4647 
| regex host!="(?m)^.*(m|M)(s|S)(t|T)(s|S).*$" 
| regex host!="(?m)^.*(t|T)(s|S).*$"
| table _time Account_Name Logon_Type Security_ID host user
| sort +host

::Logins

index=sysmon sourcetype="WinEventLog:Security" EventCode=4624 Logon_Type=10 
| sort -_time
| regex host!="(?m)^.*(m|M)(s|S)(t|T)(s|S).*$" 
| regex host!="(?m)^.*(t|T)(s|S).*$"
| table _time Account_Name Logon_Type Security_ID host src_host
| sort src_host

I need to match the user & host from Logouts to the most recent login time on the matching src_host (user) & host on the Logins search.

I tried to combine the searches. But I’m unclear how I do the query to match the logins/hosts:

index=sysmon sourcetype="WinEventLog:Security" 
| search (EventCode=4647  OR (EventCode=4624 Logon_Type=10 ))
| sort -_time
| regex host!="(?m)^.*(m|M)(s|S)(t|T)(s|S).*$" 
| regex host!="(?m)^.*(t|T)(s|S).*$"
| eval userlogin = upper(user)
| eval userlogout= upper(src_host)
| **magical query goes here**
| table _time Account_Name EventCode Logon_Type Security_ID src_host host user userlogin userlogout

I sorted by -_time to that the most recent event would be the first match it would find if I could use dedup.
For the logout query, I thought about using dedup on host, but then I would miss if multiple users were on the same server. If I dedup by user, then I would miss if the user logged out of multiple servers.
So I think I need to do some kind of search to eval matching host and user or a join. Neither of which I have been able to get to work.

Any suggestions would be helpful!! Thank you!!

[cmerriman · Accepted solution]

what about something like this:

index=sysmon sourcetype="WinEventLog:Security" EventCode=4647 OR (EventCode=4624 Logon_Type=10)
 | regex host!="(?m)^.*(m|M)(s|S)(t|T)(s|S).*$" 
 | regex host!="(?m)^.*(t|T)(s|S).*$"
 | eval join_id=if(EventCode=4647,user,src_host)
 | eval logout_time=if(EventCode=4647,_time,null())
 | eval login_time=if(EventCode=4624,_time,null())
 | stats max(login_time) as most_recent_login max(logout_time) as most_recent_logout values(Account_Name) as Account_Name values(Logon_Type) as Logon_Type values(Security_ID) as Security_ID by join_id host
 | eval most_recent_login=strftime(most_recent_login,"%F %T")

you could use |eventstats max(_time) as max_login_time by user instead of the eval login_time section and then edit that part of the stats command and you could add in _time as well as add another strftime for most_recent_logout or remove the one for most_recent_login