Splunk Search

How to chart vehicle layover time?

plucas_splunk
Splunk Employee
Splunk Employee

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?

0 Karma

sundareshr
Legend

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
0 Karma

plucas_splunk
Splunk Employee
Splunk Employee

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?

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...