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
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.
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.
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:
Try again without the reverse command. The transaction command makes assumptions about the order of events and reverse messes with that.
It's works. Thank you.