How to get first logout after login?

[alissan]

I have daily user login/logout data like this:

date,user,action
2020-04-14 01:00:00,user1,login
2020-04-14 01:05:00,user2,login
2020-04-14 01:10:00,user3,login
2020-04-14 02:40:00,user2,logout
2020-04-14 02:50:00,user3,logout
2020-04-14 03:10:00,user2,login
2020-04-14 03:10:00,user1,logout
2020-04-14 03:30:00,user3,login
2020-04-14 04:20:00,user2,logout

Users can login/logout multiple times in a day. A session closes and then new session opens. (like user2) I need to get the duration for every session and there is no session id.

How can i merge this two events in one row: Login and first logout after login. Like this:

login_date,logout_date,user
2020-04-14 01:00:00,2020-04-14 03:10:00,user1
2020-04-14 01:05:00,2020-04-14 02:40:00,user2
2020-04-14 01:10:00,2020-04-14 02:50:00,user3
2020-04-14 03:10:00,2020-04-14 04:20:00,user2
2020-04-14 03:30:00,-,user3 

[richgalloway · Accepted solution]

Here's a solution that uses the transaction command.

<<your search for events>>
| transaction user startswith="login" endswith="logout" keeporphans=1
| eval login_date=mvindex(date,0), logout_date=mvindex(date, 1)
| table login_date, logout_date, user

Perhaps someone else can suggest a solution that does not use the hated transaction command.

[alissan]

Thanks @richgalloway , i tried your suggest.

But the result is wrong:

And i tried this search (this query is too complex, there may be a shorter and safer solution):

sourcetype="vpn_duration"
|reverse
|eval c1=1
|streamstats sum(c1) as sum by user,action
|eval session_id=user+"@@"+sum
|table date,session_id,action
|stats list(date) as date_list,list(action) by session_id
|eval login_date=mvindex(date_list,0), logout_date=mvindex(date_list,-1)
|eval session_id_temp=split(session_id,"@@")
|eval username=mvindex(session_id_temp,0)
|table username,login_date,logout_date

Result:

[richgalloway]

Try again without the reverse command.  The transaction command makes assumptions about the order of events and reverse messes with that.

[alissan]

It's works. Thank you.