Splunk Search

stats by date_hour and by another field add zero count for hours with no events

Jonkiye
New Member

Hello,

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

... | stats dc(my_field)  by other_field, _time

I want this search to return the count of events grouped by hour and by "other_field" for alerting. And then compare it with data of the last day.

But if the search returns no events for a given hour and other_field, that other_field doesn't appear at this hour in the resulting table.
Is there a way to modify this to add 0's for the hours and other_fields with no events?

I tried the "timechart" but it doesn't really do the job because I need to parse by "_time" AND by "other_field". It will create columns for each "other_field".

Is there even a better way do do this? This is for an alert where I want to compare the min between the 2 last result for each "other_field" and the result of the last day at the same hour and for the same "other_field".

Thanks, Colin

0 Karma
1 Solution

maciep
Champion

Look into the untable command. It can reformat your timechart results into a table format like you want. Its counterpart, xyseries, reformats the other direction.

... | timechart span=1h dc(my_field) as my_field by other_field | untable _time other_field my_field

View solution in original post

0 Karma

maciep
Champion

Look into the untable command. It can reformat your timechart results into a table format like you want. Its counterpart, xyseries, reformats the other direction.

... | timechart span=1h dc(my_field) as my_field by other_field | untable _time other_field my_field
0 Karma

Jonkiye
New Member

This is what I needed, thanks!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...