Archive
Highlighted

How to calculate time between events within a session

New Member

I have events like

sessionid = 1 devicetime = 2017-11-26T12:13:32
sessionid = 1 devicetime = 2017-11-26T12:13:35
sessionid = 1 devicetime = 2017-11-26T12:13:37

sessionid = 2 devicetime = 2017-11-26T12:13:41
sessionid = 2 devicetime = 2017-11-26T12:13:48

sessionid = 3 devicetime = 2017-11-26T12:13:30

sessionid = 4 devicetime = 2017-11-26T12:13:21
sessionid = 4 devicetime = 2017-11-26T12:13:25
sessionid = 4 devicetime = 2017-11-26T12:13:34
sessionid = 4 devicetime = 2017-11-26T12:13:38

I would like to calculate time difference between consecutive events within a session, and get result like:

sessionid = 1 timediff = 3
sessionid = 1 timediff = 2
sessionid = 2 timediff = 7
sessionid = 4 timediff = 4
sessionid = 4 timediff = 9
sessionid = 4 timediff = 4

Tags (1)
0 Karma
Highlighted

Re: How to calculate time between events within a session

Champion

Look into what you can do with streamstats. As an example:

<your search> | streamstats current=false last(device_time) AS last_device_time BY session_id | eval time_diff=device_time-last_device_time
0 Karma
Highlighted

Re: How to calculate time between events within a session

SplunkTrust
SplunkTrust

@micahkemp - (1) you need to ensure that you know which order the events are in. Put an appropriate |sort 0 statement before the streamstats... unless you are certain the default order will work for the use case. (2) you need to deal with the fact that the first record for each session id will have a null lastdevicetime. you need a final verb that will deal with that.

0 Karma
Highlighted

Re: How to calculate time between events within a session

Champion

Great points that need to be considered.

0 Karma
Highlighted

Re: How to calculate time between events within a session

New Member

Thanks. I'm new to splunk. Could you explain why this logic works?

0 Karma
Highlighted

Re: How to calculate time between events within a session

Esteemed Legend

You can use autoregress for this and it is a bit simpler than biting off streamstats.

0 Karma