Average and maximum time between events by location

[plucas_splunk]

Given public transit log data of the form:

2016-08-01 13:34:03 GMT vehicle_id="1234" stop_id="5678"

I would like to calculate (and plot) the following:

• The average and maximum times between any vehicles stopping for each stop (i.e., the average and maximum times someone waits for a vehicle to arrive for each stop). The average time is also know as the "headway."
• It would also be nice to calculate and plot this over time, e.g., between 10am-7pm, show how the average and maximum times change per stop.

FYI: I also have a transforms.conf and a props.conf that, based on the stop_id, looks up the stop's stop_name, stop_lat (latitude), and stop_lon (longitude).

[Jeremiah · Accepted solution]

Try this run anywhere command (for your data, just use the portion starting with | streamstats).

| gentimes start=-1 increment=5m | eval vehicle_id=random()%10 |eval stop_id=random()%10 | eval _time=starttime |streamstats  global=f window=2 range(_time) AS wait by stop_id | eval wait=wait/60 | chart avg(wait) max(wait) by stop_id

And for a timechart:

| gentimes start=-1 increment=5m | eval vehicle_id=random()%10 |eval stop_id=random()%10 | eval _time=starttime |streamstats  global=f window=2 range(_time) AS wait by stop_id | eval wait=wait/60 | timechart avg(wait) max(wait) by stop_id