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!

Earn a $35 Gift Card for Answering our Splunk Admins & App Developer Survey

Survey for Splunk Admins and App Developers is open now! | Earn a $35 gift card!      Hello there,  Splunk ...

Continuing Innovation & New Integrations Unlock Full Stack Observability For Your ...

You’ve probably heard the latest about AppDynamics joining the Splunk Observability portfolio, deepening our ...

Monitoring Amazon Elastic Kubernetes Service (EKS)

As we’ve seen, integrating Kubernetes environments with Splunk Observability Cloud is a quick and easy way to ...