topic Re: Duration between events in Splunk Search

Duration between events

plucas_splunk — Tue, 29 Sep 2020 23:46:36 GMT

Given transit data like:

2019-03-19 19:00:32 GMT vehicle_id="58" stop_direction=Inbound
2019-03-19 19:05:45 GMT vehicle_id="57" stop_direction=Outbound
2019-03-19 19:10:45 GMT vehicle_id="59" stop_direction=Outbound
2019-03-19 19:21:32 GMT vehicle_id="58" stop_direction=Inbound
2019-03-19 19:25:06 GMT vehicle_id="57" stop_direction=Outbound
2019-03-19 19:37:43 GMT vehicle_id="58" stop_direction=Outbound
2019-03-19 19:41:02 GMT vehicle_id="59" stop_direction=Inbound

A "trip" is starting at one end of the line and going to the other end. I want to calculate the duration of every trip. You know when a trip ends because the a particular vehicle's stop_direction changes either from "Inbound" to "Outbound" or vice versa.

For example, when vehicle 58 changes from "Inbound" (line 4) to "Outbound" (line 6), then it had a "trip" starting from the oldest contiguous "Inbound" (line 1) to the last "Inbound" (line 4) before changing to "Outbound" (line 6). Hence, the duration would conceptually be time_of_event(4) - time_of_event(1).

Explained another way: for a given vehicle, if you were to plot its stop direction (where I is "Inbound" and O is "Outbound"), then:

IIII OOOOO III OOO IIIIIIII
^^^^ ^^^^^ ^^^ ^^^ ^^^^^^^^
T1   T2    T3  T4  T5

I.e., a run of the same stop direction constitutes a "trip." When the direction changes, it's the next trip.

I want to calculate the duration of every trip and chart it such that the X-axis is the trip and the Y-axis is the duration of that trip.

Re: Duration between events

nickhills — Thu, 21 Mar 2019 12:06:24 GMT

You could look at the transaction command to help you here:
https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/Transaction

..your search..|transaction vehicle_id startswith=Outbound endswith=Inbound|transaction vehicle_id startswith=Inbound endswith=Outbound|eval stops=eventcount, duration=round(duration/1000,0)|table vehicle_id stops duration

Duration will be in seconds

Re: Duration between events

plucas_splunk — Thu, 21 Mar 2019 17:56:22 GMT

Shouldn't that be: duration=duration/60?

I think my search should be: my_search | streamstats window=2 global=f earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id | transaction vehicle_id endswith=eval(dir1!=dir2) | eval duration=duration/60.

But I want to chart the "trip" (or the "transaction ID") by the duration. For each transaction, I'd need a monotonically increasing integer. How can I get that?