Search code examples
splunktimedeltasplunk-query

Splunk - Remove events between 1st login and last logout while user has any session open where logins and logouts can happen multiple times in a row?

The questions sounds a bit confusing, so to break it down, I'm trying to find the time difference between logins and logouts. The catch is two-fold. It's possible that the time range doesn't catch the first login that's logged out during the time range, and vice versa it's possible that the time range doesn't catch the last logout that's logged in during the time range. This can end up looking like below in a resulting table.

| Action | Action Number |
|--------|---------------|
| Login  | 1             |- 1am
| Login  | 2             |- 1:01 am
| Logout | 1             |- 1:02 am
| Logout | 2             |- 1:03 am
| Logout | 3             |- 1:04 am
| Logout | 4             |- 1:05 am
| Login  | 3             |- 1:10 am
| Logout | 5             |- 1:11 am
| Login  | 4             |- 1:15 am
| Login  | 5             |- 1:16 am
| Logout | 6             |- 1:17 am
| Login  | 6             |- 1:18 am
| Logout | 7             |- 1:20 am
| Logout | 8             |- 1:22 am

Where action number is what number that login/logout is during the time frame. For example, the first login will have an action number of 1, as will the first logout, and so on.

I've written the logic to get that in place, but what I need help with is "removing" the events in between the first login and last logout for each break in activity (periods when the user had no sessions logged in).

This would mean that the first login (for the time range) for this user would be Login - 1, and they had a logged in session until Logout 4. This means I would want to remove Login 2 and Logout 1 and 2. Then I can calculate the time difference between the two remaining events to find the total time they were logged in to any session in that period.

To summarize, the following is the result that I'm wanting to generate from the above table, but I can't find a good way to do this.

| Action | Action Number | Flag for Deletion |
|--------|---------------|-------------------|
| Login  | 1             | False             |
| Login  | 2             | True              |
| Logout | 1             | True              |
| Logout | 2             | True              |
| Logout | 3             | True              |
| Logout | 4             | False             |
| Login  | 3             | False             |
| Logout | 5             | False             |
| Login  | 4             | False             |
| Login  | 5             | True              |
| Logout | 6             | True              |
| Login  | 6             | True              |
| Logout | 7             | True              |
| Logout | 8             | False             |
Solution

  • The following 6 lines of SPL produce your raw dataset:

    | makeresults count=1 
| eval data="Login,1|Login,2|Logout,1|Logout,2|Logout,3|Logout,4|Login,3|Logout,5|Login,4|Login,5|Logout,6|Login,6|Logout,7|Logout,8" 
| makemv delim="|" data 
| mvexpand data 
| rex field=data "(?<action>[^\,]+),(?<action_number>\d+)" 
| fields - _time, data

    The following SPL builds upon this, to produce the required result. Loosely speaking, it:

    1. Keeps a count of the active sessions (streamstats)
    2. Ensures this does not go below zero (streamstats, eval)
    3. Works out the next action (reverse, streamstats)
    4. Uses logic based on session count, current action and next action to decide whether you need to use this event in your calculations (eval)
    | eval actiontype=if(action=="Login",1,-1) 
| streamstats reset_after="("session_count<\"0\"")" sum(actiontype) AS session_count 
| eval session_count=if(session_count==-1,0,session_count) 
| reverse 
| streamstats current=f global=f window=1 max(actiontype) AS next_actiontype 
| eval "Flag for Deletion"=if(session_count>1 OR (session_count==0 AND next_actiontype==-1) OR (session_count==1 AND actiontype==-1),"True","False") 
| reverse 
| fields action, action_number,"Flag for Deletion"