How to calculate time between events within a session

[jy190]

I have events like

session_id = 1 device_time = 2017-11-26T12:13:32
session_id = 1 device_time = 2017-11-26T12:13:35
session_id = 1 device_time = 2017-11-26T12:13:37

session_id = 2 device_time = 2017-11-26T12:13:41
session_id = 2 device_time = 2017-11-26T12:13:48

session_id = 3 device_time = 2017-11-26T12:13:30

session_id = 4 device_time = 2017-11-26T12:13:21
session_id = 4 device_time = 2017-11-26T12:13:25
session_id = 4 device_time = 2017-11-26T12:13:34
session_id = 4 device_time = 2017-11-26T12:13:38

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

session_id = 1 time_diff = 3
session_id = 1 time_diff = 2
session_id = 2 time_diff = 7
session_id = 4 time_diff = 4
session_id = 4 time_diff = 9
session_id = 4 time_diff = 4

[woodcock]

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

[micahkemp]

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

[jy190]

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

[DalJeanis]

@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 last_device_time. you need a final verb that will deal with that.

[micahkemp]

Great points that need to be considered.