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]

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

[sundareshr · Accepted solution]

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

[MDSplunkNinja]

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

[MDSplunkNinja]

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

[marina_rovira]

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 )

[MDSplunkNinja]

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.

[marina_rovira]

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

[MDSplunkNinja]

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?

[marina_rovira]

What do you mean per agent?