Splunk Search

stats by date_hour add zero count for hours with no events

ickymettle
Explorer

Hi folks,

I'm working on a search to return the number of events by hour over any specified time period. At the moment i've got this on the tail of my search:

... | stats count by date_hour | sort date_hour

I want this search to return the count of events grouped by hour for graphing.

This for the most part works. However if the search returns no events for a given hour, that hour doesn't appear in the resulting table.

Is there a way to modify this to essentially add 0's for the hours with no events? Given stats is only aggregating on fields that exist in the result data and it isn't really a "time" aware function I can't see a solution.

Is there even a better way do do this? This is for a dashboard where I want to graph the busiest time of day across a given time range and want the query flexible enough to just be able to change the time range (7d, last month, last year).

Thanks, Marcus

1 Solution

ziegfried
Influencer

I had a similar problem in the past and solved it by implementing a custom search commands that added the missing elements (days in my case). But I think your query can be solved using a combination of timechart, eval and stats:

... | timechart span=1h count | eval hour=strftime(_time,"%H:00") | stats sum(count) as count by hour

View solution in original post

ziegfried
Influencer

I had a similar problem in the past and solved it by implementing a custom search commands that added the missing elements (days in my case). But I think your query can be solved using a combination of timechart, eval and stats:

... | timechart span=1h count | eval hour=strftime(_time,"%H:00") | stats sum(count) as count by hour

ickymettle
Explorer

this did the trick nicely, thanks!

0 Karma

ftk
Motivator

If you are just doing this for graphing, I recommend using timechart instead of stats. You can tell timechart to use spans of 1 hour, and for every hour with 0 events you can configure whether you want to treat this as 0 (null) or simply connect with the other data data points.

Try using it like so:

... | timechart span=1h count

ickymettle
Explorer

this works fine for a 24 hour time range, however in my particular use case I needed to sum the events by hour over multiple days to graph the busiest hour of the day over more than 1 day

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...