Using the transaction command to determine the length of an "active" session.

[Ricapar]

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:

• When a user opens a new session, it is logged as a Session_Start event. During this time, a user can either log off (ending their session completely [see Bob below]), or a user can disconnect (say by.. closing their laptop screen), which the application registers as a disconnect, but keeps the session until a 1 hour timeout period passes. At this point the session is terminated (see Carol).

• 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:

  | transaction UserID startswith=EventType=Session_Start endswith=EventType=Session_End
  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?

[woodcock]

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

[woodcock]

So did this work?

[masonmorales · Accepted solution]

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