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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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