topic Re: Distinct count by hour by type in Splunk Search

Distinct count by hour by type

plucas_splunk — Wed, 05 Apr 2017 22:25:18 GMT

I currently have a search:

... | eval hour=strftime(_time,"%H") |
streamstats time_window=1h dc(vehicle_id) AS dc_vid |
timechart max(dc_vid) by hour fixedrange=false

This correctly produces the number of distinct vehicles on a particular route by hour.

But now assume that there are two different vehicle types: bus and streetcar. So I want to modify the chart to show the same thing, but each bar should be a stacked bar composed of the number of distinct vehicles by vehicle_type by hour.

I've tried all manner of fiddling with the search and I can't seem to get it.

BTW: the existing search shows each hour as a different colored bar. I don't actually care about that. For the new chart, two colors would be fine (one for each vehicle type in the stacked bar).

Re: Distinct count by hour by type

somesoni2 — Thu, 06 Apr 2017 01:13:06 GMT

How about this?

... | eval hour=strftime(_time,"%H") |
 streamstats time_window=1h dc(vehicle_id) AS dc_vid by vehicle_type|
 timechart max(dc_vid) by vehicle_type fixedrange=false

... | eval hour=vehicle_type.":".strftime(_time,"%H") |
 streamstats time_window=1h dc(vehicle_id) AS dc_vid by vehicle_type|
 timechart max(dc_vid) by hour fixedrange=false

Re: Distinct count by hour by type

woodcock — Thu, 06 Apr 2017 02:23:45 GMT

Like this:

... | eval vehicle_type=case(PUT YOUR STUFF HERE)
| timechart span=1h dc(vehicle_id) AS dc_vid BY vehicle_type

Re: Distinct count by hour by type

plucas_splunk — Thu, 06 Apr 2017 02:42:15 GMT

Neither of those works.

Re: Distinct count by hour by type

plucas_splunk — Thu, 06 Apr 2017 02:46:19 GMT

This pretty much works. 🙂

Re: Distinct count by hour by type

woodcock — Thu, 06 Apr 2017 03:09:44 GMT

There is value in simplicity, even if it is not a perfect fit.