I have a system for which I'd like to be able to report on how much time individual users spend logged in.
However, there are a few constraints:
There could also be a scenario where a user gets disconnected but then is able to reconnect (for example, losing wifi while moving between rooms in the office), or closing their screen to go out for a quick lunch.
_time UserID EventType
10/14/15 08:00 AM bob Session_Start
10/14/15 10:00 AM bob Session_End
10/14/15 08:00 AM alice Session_Start
10/14/15 08:30 AM alice Disconnect
10/14/15 09:00 AM alice Reconnect
10/14/15 10:00 AM alice Session_End
10/14/15 08:00 AM carol Session_Start
10/14/15 10:00 AM carol Disconnect
10/14/15 11:00 AM carol Session_End
Doing a nice and simple transaction
is a starting point:
From there I can easily do a timechart span=1d sum(duration) by UserID
to get the type of report I want.
This works in Bob's case just fine. But for Alice and Carol, they've both been given extra time. Alice disconnected at 8:30, and then reconnected at 9. That gives her an extra 30 minutes on that sum(duration)
. The sum for Carol is off as well, since he simply closed his laptop screen (for example), and called it a day. The system ended his session an hour later after the timeout passed.
I'm struggling to find a good way to approach this. At this point, I'd be happy with just solving the issue demonstrated in Carol's case. Solving Alice's scenario would be a bonus.
Any thoughts?
You can use expressions in startswith= and endswith=. So, you should be able to do something like:
...search... | transaction UserID startswith=(EventType="Session_Start" OR EventType="Reconnect") endswith=(EventType="Session_End" OR EventType="Disconnect")
See also: http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Transaction
Like this:
... | reverse | eval SessionID=0 | streamstats current=f count (eval(EventType="Session_Start")) AS SessionID last(EventType) AS prevEventType last(_time) AS prevTime by UserID | eval deltaOnlineSeconds=if(prevEventType="Session_Start" OR prevEventType="Reconnect", _time - prevTime, 0) | stats sum(deltaOnlineSeconds) AS onlineSeconds BY SessionID, UserID
If you would like to roll up it one more time to get total over all sessions, then add this to the end of the previous search:
... | stats count AS sessions sum(onlineSeconds) AS onlineSeconds BY UserID
So did this work?
You can use expressions in startswith= and endswith=. So, you should be able to do something like:
...search... | transaction UserID startswith=(EventType="Session_Start" OR EventType="Reconnect") endswith=(EventType="Session_End" OR EventType="Disconnect")
See also: http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Transaction