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 SessionStart
10/14/15 10:00 AM bob SessionEnd
10/14/15 08:00 AM alice SessionStart
10/14/15 08:30 AM alice Disconnect
10/14/15 09:00 AM alice Reconnect
10/14/15 10:00 AM alice SessionEnd
10/14/15 08:00 AM carol SessionStart
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.
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")
... | 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