topic Re: 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? in 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 — Tue, 26 Apr 2016 20:55:13 GMT

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).

Re: 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?

marina_rovira — Wed, 27 Apr 2016 09:20:31 GMT

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 )

Re: 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 — Wed, 27 Apr 2016 13:08:37 GMT

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?

Re: 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?

marina_rovira — Wed, 27 Apr 2016 14:33:19 GMT

What do you mean per agent?

Re: 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 — Thu, 28 Apr 2016 13:56:57 GMT

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.

Re: 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?

sundareshr — Thu, 28 Apr 2016 16:22:14 GMT

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

Re: 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?

marina_rovira — Fri, 29 Apr 2016 08:07:33 GMT

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

Re: 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 — Fri, 29 Apr 2016 13:45:07 GMT

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

Re: 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 — Mon, 09 May 2016 19:50:43 GMT

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