Splunk Search

How to find time difference between the types of log in and log out events with no shared field values?

cdhippen
Path Finder

I have log items that have event messages but no IDs indicating that the log in and log out belong to the same session. However, obviously a log in will happen before a log out so on and so forth.

The logs look something like this:

{TIME} "eventMessage":"Timeout is detected for Standard user" {userID}

{TIME} "eventMessage":"User login successful" {userID}

{TIME} "eventMessage":"Timeout is detected for SAML user" {userID}

{TIME} "eventMessage":"SSO user login successful" {userID}

{TIME} "eventMessage":"User logged out successfully" {userID}

{TIME} "eventMessage":"SSO user login successful" {userID}

I want to calculate all the time that the user was logged in, but have no shared field values I can do it by. As you can see, the user will sometimes log in via SSO and sometimes normally. They can also either log in, or the system can time them out. The logs reflect that.

How would I go about calculating the time between logins and logouts/timeouts?

Bonus question: How would I take 60 minutes off each time there was a timeout? (the users have to be inactive for 60 minutes before they are timed out.)

2nd Bonus question: How would I do this for multiple users whose logs might be mixed up with other users (i.e. the login/logout would not be in a direct line because other users logins/ logouts might be in the mix)?

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

Hi @cdhippen,

Try this and let's know what changes you need.

index="your index" source=""
|eval action=case(match(Message,"Timeout is detected for") OR match(Message,"User logged out"),"Logout",match(Message,"User login successful") OR match(Message,"SSO user login successful"),"Login")
|sort _time
|stats list(action) as action,list(_time) as _time by user
|eval zipped=mvzip(action,_time)|table user,zipped|mvexpand zipped|eval data=split(zipped,",")
|eval action=mvindex(data,0)|eval _time=mvindex(data,1)
|table user,action,_time
|streamstats current=f last(_time) as prev by user
|eval diff=if(action=="Logout",(_time-prev),"NA")|fillnull value="NA" diff
|eval diff=if((action=="Logout" AND diff=="NA"),"Login Not Found",diff)|fields - prev

My sample data looks like this

2018-06-29 11:40:46, "Timeout is detected for Standard user", user1
2018-06-29 11:41:46, "User login successful", user2
2018-06-29 11:42:46, "Timeout is detected for SAML user", user3
2018-06-29 11:43:46, "SSO user login successful", user4
2018-06-29 11:44:46, "User logged out successfully", user2
2018-06-29 11:45:46, "SSO user login successful", user5
2018-06-29 11:46:46, "User login successful", user6
2018-06-29 11:47:46, "User logged out successfully", user6
2018-06-29 11:48:46, "User logged out successfully", user5
2018-06-29 11:49:46, "User login successful", user2
2018-06-29 11:50:46, "User logged out successfully", user4
2018-06-29 11:51:46, "User login successful", user4
2018-06-29 11:52:46, "User logged out successfully", user2
2018-06-29 11:53:46, "User logged out successfully", user4
Happy Splunking!

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Hi @cdhippen,

Try this and let's know what changes you need.

index="your index" source=""
|eval action=case(match(Message,"Timeout is detected for") OR match(Message,"User logged out"),"Logout",match(Message,"User login successful") OR match(Message,"SSO user login successful"),"Login")
|sort _time
|stats list(action) as action,list(_time) as _time by user
|eval zipped=mvzip(action,_time)|table user,zipped|mvexpand zipped|eval data=split(zipped,",")
|eval action=mvindex(data,0)|eval _time=mvindex(data,1)
|table user,action,_time
|streamstats current=f last(_time) as prev by user
|eval diff=if(action=="Logout",(_time-prev),"NA")|fillnull value="NA" diff
|eval diff=if((action=="Logout" AND diff=="NA"),"Login Not Found",diff)|fields - prev

My sample data looks like this

2018-06-29 11:40:46, "Timeout is detected for Standard user", user1
2018-06-29 11:41:46, "User login successful", user2
2018-06-29 11:42:46, "Timeout is detected for SAML user", user3
2018-06-29 11:43:46, "SSO user login successful", user4
2018-06-29 11:44:46, "User logged out successfully", user2
2018-06-29 11:45:46, "SSO user login successful", user5
2018-06-29 11:46:46, "User login successful", user6
2018-06-29 11:47:46, "User logged out successfully", user6
2018-06-29 11:48:46, "User logged out successfully", user5
2018-06-29 11:49:46, "User login successful", user2
2018-06-29 11:50:46, "User logged out successfully", user4
2018-06-29 11:51:46, "User login successful", user4
2018-06-29 11:52:46, "User logged out successfully", user2
2018-06-29 11:53:46, "User logged out successfully", user4
Happy Splunking!
0 Karma

cdhippen
Path Finder

Thank you very much, I replaced the necessary fields and summed by user and it looks great. Much appreciated.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

The issue with this is, as you stated in the second question, you could have intermingled sessions. User A may log in, then before they log out, User B logs in. Without a common value to correlate, there is no way to be sure that you have the correct events.

0 Karma

cdhippen
Path Finder

Well the userID would be a differentiator between those. If it's not possible to do it for multiple users, is there a way to do it for a single user? There's only like 5 users I need to get the report for so running a separate report for each would not be too much of a stretch

0 Karma

cdhippen
Path Finder

The problem I'm having with differentiating is that there are several login/logout logs for a single user, so I don't know how to tie them together, even though they're all right next to each other.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...