Splunk Search
Highlighted

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

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 otherfield, that otherfield 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 "otherfield". 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 "otherfield" and the result of the last day at the same hour and for the same "otherfield".

Thanks, Colin

0 Karma
Highlighted

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

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
Highlighted

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

New Member

This is what I needed, thanks!

0 Karma