Getting Data In

How do I write a search to aggregate the difference between timestamps of disparate login and logout events by user ID over a given period of time?

MDSplunkNinja
Explorer

My agents log in and out of our system several times in a given shift and I need to aggregate the total time they spend logged in. There is one event captured for successful log in and two for log out (one for idle timeout and one for log out).

0 Karma
1 Solution

sundareshr
Legend

Maybe this will help give you some ideas... (this is psuedo code, please tweak to your data set)

     index=* login OR logout OR timeout | rex "(?<action>login|logout|timeout)" | eval action=if(action=="logout" OR action=="timeout", "logout", action) | reverse | streamstats count as session by userid action | sort userid session | streamstats window=1 range(_time) as dur by userid session | stats sum(dur) as duration by userid

View solution in original post

0 Karma

sundareshr
Legend

Maybe this will help give you some ideas... (this is psuedo code, please tweak to your data set)

     index=* login OR logout OR timeout | rex "(?<action>login|logout|timeout)" | eval action=if(action=="logout" OR action=="timeout", "logout", action) | reverse | streamstats count as session by userid action | sort userid session | streamstats window=1 range(_time) as dur by userid session | stats sum(dur) as duration by userid
0 Karma

MDSplunkNinja
Explorer

Thank you Sundareshr and Marina for your help. Here is what I was able to have success with:

sourcetype=XXXXXXXXXXXX client=XXXXXXXX (SUCCESSFUL_UI_LOGIN OR LOGOUT_UI_TIMEOUT OR LOGOUT_UI_REGULAR) | rex "(?SUCCESSFUL_UI_LOGIN|LOGOUT_UI_TIMEOUT|LOGOUT_UI_REGULAR)" | eval action=if(action=="LOGOUT_UI_TIMEOUT" OR action=="LOGOUT_UI_REGULAR", "logout", action) | transaction count as session by normalizedUsername action maxpause=1s | sort normalizedUsername session | transaction range(_time) as duration by normalizedUsername session startswith="SUCCESSFUL_UI_LOGIN" endswith="logout" | stats sum(duration) as duration by normalizedUsername
0 Karma

MDSplunkNinja
Explorer

sundareshr...this is looking pretty good. I am getting back data by user but all of the durations are "0". Did I miss a variable in this search that was "psuedo"?

sourcetype=XXXXXXXXXXXXXX client=XXXXXXXXX (SUCCESSFUL_UI_LOGIN OR LOGOUT_UI_TIMEOUT OR LOGOUT_UI_REGULAR) | rex "(?SUCCESSFUL_UI_LOGIN|LOGOUT_UI_TIMEOUT|LOGOUT_UI_REGULAR)" | eval action=if(action=="LOGOUT_UI_TIMEOUT" OR action=="LOGOUT_UI_REGULAR", "logout", action) | reverse | streamstats count as session by normalizedUsername action | sort normalizedUsername session | streamstats window=1 range(_time) as dur by normalizedUsername session | stats sum(dur) as duration by normalizedUsername
0 Karma

marina_rovira
Contributor

Hi,

I'm always using this sentence to find the total time for ticket handling:

eval diference=strptime('ClosureAt', "%d %b %Y %T %Z")-strptime('CreatedAt', "%d %b %Y %T %Z")

IT transforms the timestamp (type: "%d %b %Y %T %Z") in epoch time and calculates the difference. If you want an average, It's better to calculate it with epoch type and then make the change with this:

eval "Time average"=tostring(round(avg,0), "duration")

Also, I know there is a statement called transaction that can help you better for this, but since I don't use it, I can not explain you how to do it. (See documentation here: http://docs.splunk.com/Documentation/Splunk/6.4.0/SearchReference/Transaction )

0 Karma

MDSplunkNinja
Explorer

Agents=Users. I need to get the total time spent logged in to the system per user in a given shift or week or month.

0 Karma

marina_rovira
Contributor

The way sundareshr is doing below, sum by UserID in case you have a field like this.

Go through his response, I think it can help you more than mine. Mine is just for the time, but he is checking when it's a timeout and set it as logout, and streamstats statement will work for time calculation.

Anyway, write me again if anything! I will be glad to help

0 Karma

MDSplunkNinja
Explorer

How are you aggregating the total handling time per agent? Have you been able to do that within your search or do you have to do that in excel?

0 Karma

marina_rovira
Contributor

What do you mean per agent?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...