Splunk Search

Distinct count by hour by type

plucas_splunk
Splunk Employee
Splunk Employee

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

0 Karma

woodcock
Esteemed Legend

Like this:

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

plucas_splunk
Splunk Employee
Splunk Employee

This pretty much works. 🙂

0 Karma

woodcock
Esteemed Legend

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

0 Karma

somesoni2
Revered Legend

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

OR

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

plucas_splunk
Splunk Employee
Splunk Employee

Neither of those works.

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Updates (ESCU) - New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 3 releases of new content via the Enterprise ...

Thought Leaders are Validating Your Hard Work and Training Rigor

As a Splunk enthusiast and member of the Splunk Community, you are one of thousands who recognize the value of ...

.conf23 Registration is Now Open!

Time to toss the .conf-etti 🎉 —  .conf23 registration is open!   Join us in Las Vegas July 17-20 for ...