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.
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
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?