How to join two consecutive events and their _time fields?

[PrisonMike]

I have a log file with events that indicate activities in a server. I am interested in the Login and Logout activities - I need to create a report of active sessions.

I managed to order the events so that I can get Login-Logout events consecutively for each user.

[ITWhisperer · Accepted solution]

Assuming your events are already sorted in chronological order (earliest first), try something like this

| streamstats global=f window=1 current=f latest(event) as previous_event latest(time) as previous_time by user
| reverse
| streamstats global=f count(eval(previous_event="Login")) as occurrence by user
| where event="Logout" and previous_event="Login"

[PrisonMike]

I don't really understand what we need the occurrence for?

[ITWhisperer]

Strictly speaking you don't if you just want the times - I have used occurrence in some of my solutions to "tag" the correlated events

[PrisonMike]

Got it

[ITWhisperer]

_time is a bunch of numbers too - it is just auto-formatted when you display it! If you want to display latest(_time) as a datetime string, use either fieldformat or eval with the strftime() function

[PrisonMike]

Okay the solution works perfectly except for the part where i have to include even "half-sessions" where i only have the login or logout event within my time frame. In such case it would be something like 

      Login Time       |   User  |     Logout Time    

             null               | user1 |   yy-mm-dd h:m:s

yy-mm-dd h:m:s   | user2 |          null

[ITWhisperer]

| eval start=if(event="Login",time,null())
| eval end=if(event="Logout",time,null())
| streamstats global=f count(eval(event="Login")) as session by user
| stats values(start) as start values(end) as end by user session