Find the delta between first logins and last logout per break in activity in the case where logins and logouts can happen multiple times in a row?

[cdhippen]

I have been trying to evaluate total time logged in but have run into a couple snags due to the fact that our system can allow logins on different browsers, computers, incognito windows, etc. without logging out of the original session. Also, depending on time range, the first event may not be a login, and the last event may not be a logout. I wrote a subsearch to append a login if the first event isn't a login with time retrieved from the time range of the search, and vice versa with the logout at the end, but I'm still having trouble removing middle-duplicates.

Below, I've provided sample results (image) of the search (below image) in the following scenario (above image):

Logout (Login happened before set time range)
Logout (2nd session that was logged in before time range)
Login (1st login since session started)
Logout
Login
Login
Logout
Logout
etc. etc.
Login (last value in search period is a login, logout is manually added with | append statement)

index="<logins/ logouts index>" "Login Message1" OR "Logout Message1" OR "Logout Message2" userGuid=<userID> 
| eval action=case(match(eventMessage,"Logout Message1") OR match(eventMessage,"Logout Message2"),"Logout",match(eventMessage,"Login Message1") OR match(eventMessage,"Login Message2") OR match(eventMessage,"Login Message3"),"Login") 
| table userGuid,action,_time 
| append 
    [| search index="<logins/ logouts index>" "Login Message1" OR "Logout Message1" OR "Logout Message2" userGuid=<userID> 
| eval action=case(match(eventMessage,"Logout Message1") OR match(eventMessage,"Logout Message2"),"Logout",match(eventMessage,"Login Message1") OR match(eventMessage,"Login Message2") OR match(eventMessage,"Login Message3"),"Login") 
    | sort _time 
    | eval _time=mvzip(action, _time) 
    | stats first(_time) as start last(_time) as stop by userGuid 
    | fields userGuid start stop 
    | untable userGuid action _time 
    | eval _time=split(_time, ",") 
    | eval action=mvindex(_time,0) 
    | eval _time=mvindex(_time,1) 
    | streamstats count by userGuid 
    | eval flag=if((count="1" AND action="Login") OR (count="2" AND action="Logout"), "Valid", "Switch") 
    | search flag="Switch" 
    | addinfo 
    | eval info_max_time=strftime(info_max_time, "%Y-%m-%d %H:%M:%S.%3N"), info_min_time=strftime(info_min_time, "%Y-%m-%d %H:%M:%S.%3N")
    | eval action=if(action="Login", "Logout", "Login"), _time=if(count="1", info_min_time, info_max_time)
    | eval _time=strptime(_time, "%Y-%m-%d %H:%M:%S.%3N")
    | table userGuid action _time]
| sort 0 _time
| streamstats count as sessionNo by action userGuid
| streamstats count as logCheck by sessionNo userGuid
| eval flag=if((action="Logout" AND logCheck="1") OR (action="Login" AND logCheck="2"), "Switch", "Fine")
| eval newSessionNo=case((action="Logout" AND flag="Switch"), sessionNo-1, (action="Login" AND flag="Switch"), sessionNo, flag="Fine", sessionNo)

As you can see from the screenshot, I've been fiddling with trying to order things and put the correct items together so that I can remove the one between the first login and last logout of the session. From there I'm sure I can calc the delta, but this is where I'm stuck. I've attached the same picture with the items I want removed highlighted below:

[mthomas_splunk · Accepted solution]

The following 6 lines of SPL produce the raw dataset you used in your stackoverflow question:

| 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 logic below to produces 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)

SPL:

| 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"