Splunk Search

How to get first logout after login?

alissan
Explorer

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 
Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

alissan
Explorer

Thanks @richgalloway , i tried your suggest.

But the result is wrong:

Screenshot 2022-12-14 at 14.34.34.png

 

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:

Screenshot 2022-12-14 at 14.39.09.png

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.

alissan
Explorer

It's works. Thank you.

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...