How to chart vehicle layover time?

[plucas_splunk]

Suppose I have vehicle data of the form:

2016-10-18 17:37:05 GMT vehicle_id="1011" vehicle_distance=185 stop_tag="5239"
2016-10-18 17:39:25 GMT vehicle_id="1009" vehicle_distance=51 stop_tag="4532"
2016-10-18 17:41:37 GMT vehicle_id="1010" vehicle_distance=107 stop_tag="4529"
2016-10-18 17:43:35 GMT vehicle_id="1009" vehicle_distance=104 stop_tag="4532"
2016-10-18 17:45:59 GMT vehicle_id="1011" vehicle_distance=98 stop_tag="5240"

I also have a look-up table that give additional information about stops keyed by tag:

stop_tag,stop_title,stop_id,stop_direction,stop_lat,stop_lon
...
"5239","King St & 4th St","15239","Outbound",37.776270,-122.394170
"5240","King St & 4th St","15240","Inbound",37.776270,-122.394080
...

Each physical stop actually has two stop tags: one for "Inbound" and another for "Outbound," for example, the "King St & 4th St" stop shown. The direction is given by stop_direction.

Hence, given a log entry such as the first, at 17:37:05, vehicle 1011 is at stop 5239 --- which is King St & 4th St outbound. Then, nearly 9 minutes later, the same vehicle is at stop 5240 --- the same stop, but inbound. Hence, the vehicle had a "layover" of approximately 9 minutes.

For each vehicle, when it changes direction from outbound to inbound (or vice versa) as given by the direction of the stop tag it's at, I want to chart both the average and maximum layover as the Y-axis and either:

1. All vehicles taken together --- where the X-axis would be time.
2. By individual vehicle -- where the X-axis would be the vehicle_id.

How can I do this?

[sundareshr]

Try this

base search vehicle data | lookup lookupfile.csv stop_tag | reverse | streamstats count by vehicle_id stop_direction | stats earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id count | where NOT (dir1=dir2) | eval duration=end-stop

[plucas_splunk]

You have a typo at the end: should be end-start

Anyway, I took that as a starting point and did:

base search | reverse | streamstats count by vehicle_id stop_direction | stats earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id count | where dir1="Outbound" AND dir2="Inbound" | eval layover=(end-start)/60 | chart min(layover) as Min avg(layover) AS Avg max(layover) as Max by vehicle_id

What about part 1 of my request? All vehicles taken together?