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 Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...