Splunk Search
Highlighted

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

Communicator

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 SessionEnd
    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 SessionEnd
    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=SessionStart endswith=EventType=SessionEnd
    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?

0 Karma
Highlighted

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

Influencer

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

View solution in original post

Highlighted

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

Esteemed Legend

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
Highlighted

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

Esteemed Legend

So did this work?

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.