Subsearch relative to base search time

[hoytn]

Hello,

I'm looking into a way to discover following scenario in my ingested logs: some user logged out and didn't log back in the next 1h
I assume in this case I should look in my search for a logout event, and use a subsearch to look for a subsequent login event related to same user within 1h

Eventually I would use the search in Enterprise Security (correlation search) triggering when the second event didn't happen within an hour.

Please let me know if you have any tip on how I should approach this.

Thank you

[woodcock]

I don't understand the value of your use case, but this will show you:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo AND (<Login match details here> OR <Logout match details here>)
| streamstats count(eval(searchmatch(<Login match details here>))) AS sessionID BY user
| stats range(_time) AS duration count max(_time) AS _time BY sessionID user
| where count==1 OR (duration > (60*60))

[chrisyounger · Accepted solution]

Hi @hoytn

Its best to not use subsearches where possible. I would solve the problem like this:

Do a search that returns all the events, both logins and logouts. E.g. sourcetype=Whatever action=Login OR action=Logout

Then you can do something like this (obviously dependant on your data): |eval login_time = if(action=="Login",_time, null())
and |eval logout_time = if(action=="Logout",_time, null())

Now you can join everything together by username: |stats latest(login_time) as login_time latest(logout_time) as logout_time by username

Then do a calculation like so |eval is_logged_in = if(login_time > logout_time) and | eval minutes_since_last_logout = if(isnotnull(is_logged_in), ((now() - logout_time)/60), null())

Finally filter the list to show users who havn't logged back in:

|search minutes_since_last_logout > 60

Hope this answers your question. If you provide more specific details we can help you better.