topic Re: How to get first logout after login? in Splunk Search

How to get first logout after login?

alissan — Tue, 13 Dec 2022 15:17:19 GMT

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

Re: How to get first logout after login?

richgalloway — Tue, 13 Dec 2022 16:30:58 GMT

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.

Re: How to get first logout after login?

alissan — Wed, 14 Dec 2022 11:52:15 GMT

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:

Re: How to get first logout after login?

richgalloway — Wed, 14 Dec 2022 13:40:08 GMT

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

Re: How to get first logout after login?

alissan — Wed, 14 Dec 2022 14:35:43 GMT

It's works. Thank you.