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