Use transaction to chain events : end_time to start_time of next event

[BpAdminEtCtrl]

Hy everybody !

This is my first post, so don't hesitate to correct me, explain howto do it, or ask for further informations or configurations ...

Here's my problem :
I have eventlogs (from SPECTOR technologies) that looks like this :

---

dest user Action Starting Time Ending Time
server1 user1 Activity 01/07/2016 11:17 01/07/2016 11:27
server1 user1 Inactivity 01/07/2016 11:27 01/07/2016 16:32
server1 user1 Activity 01/07/2016 16:32 01/07/2016 16:34
server1 user1 Inactivity 01/07/2016 16:34 01/07/2016 16:38
server1 user1 Activity 01/07/2016 16:38 01/07/2016 16:41
server1 user1 Inactivity 01/07/2016 16:41 02/07/2016 17:21
server1 user1 Activity 04/07/2016 08:49 04/07/2016 09:11
server1 user1 Inactivity 04/07/2016 09:11 04/07/2016 09:14
server1 user1 Activity 04/07/2016 09:14 04/07/2016 09:45
server1 user1 Inactivity 04/07/2016 09:45 04/07/2016 09:50
server1 user1 Activity 04/07/2016 09:50 04/07/2016 09:50

server1 user1 Inactivity 04/07/2016 09:50 04/07/2016 11:30

As you can see, the end_time from one event can be linked to the start_time of the next one.
By chaining events, you can see that the user1 first session starts on 01/07/2016 11:17 and ends on 02/07/2016 17:21
which means a 30h04m long session.

I tried to use this : request |transaction dest user
But since there's nothing that looks like a transactionid or sessionid in my logs, it doesn't work ...

• I can't build a id row (because users can have multiple sessions on a same server, and can run sessions on many days)
• It's for a weekly report, so the request can be heavy, it doesn't matter
• I can't chain request indefinitely, unless it's trough a form of loop (users can chain activity/Inactivities hundred of times per day ...)

Is there a way to achieve this with transaction ? If not, can you see another way to achieve this ? (macros, scripts .. ?? I'm open to any solution to do the job )

thanks in advance !

[sundareshr]

Not sure transaction is the best option for this. Try streamstats instead. Something like this work

base search | eval starttime=strptime(starttime, "%m/%d/%Y %H:%M") | eval endtime=strptime(endtime, "%m/%d/%Y %H:%M") | reverse | streamstats window=1 current=f values(starttime) as prev_start by dest user | streamstats count(eval(starttime!=prev_start)) as group by dest user | stats first(startime) as start last(endtime) as end by dest user | eval duration=end-start | eval duration=tostring(duration, "duration") | convert ctime(start) as start ctime(end) as end

http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Streamstats

[DEAD_BEEF]

This is hot! Good work.

[DEAD_BEEF]

I don't think I have the full answer, but I can share my thoughts on how I would approach this to get you started.

Based on what you wrote, it sounds like you need to come up with a report that shows you which user logged on, when they logged on, and how long they were logged on for.

I think first we transaction the logs by destination and user since that's the only commonality between all logs. This will groups all the logs together where the user is the same

index=spector | transaction dest,user | ...

now we need to get the start times and end times into their own field (separate from the date).

• first use rex to extract the start_time and end_time of each log entry
• use an eval statement to find the difference between the inactivity start_time from the activity end_time and call that diff
• If diff != 0, then you know that it is a separate session

rex is just a regex command that lets you extract the data from a field (presuming it is not already parsed for you into its own field). In this case, I think you can use it to just extract the time.

... | rex field=StartingTime "(?<s_time>\d{2}:\d{2})" | rex field=EndingTime "(?<e_time>\d{2}:\d{2})"

The thing I can't figure out is how you structure the query for time_diff such that looks at the inactivity start_time - activity end_time (of previous log). I use an eval command to create two new fields that set the appropriate rex extracted e_time if action is Activity, and s_time if action is Inactivity. Not sure if you can use the end_time and start_time rex extracted fields in an eval field. Then I store the difference between these two times in diff

eval activity_endtime=if(match(_raw, "Activity", end_time, 0)
eval inactivity_starttime=if(match(_raw, "Inactivity", start_time, 0)
eval diff=inactivity_starttime-activity_endtime

Then you would do your search where at the end you would put | where diff=0

Not sure if that helps or that's what you were trying to do. Are you just trying to calculate the total session time?