https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296798#M89536 <P>Hello,</P> <P>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:</P> <PRE><CODE>... | stats dc(my_field) by other_field, _time </CODE></PRE> <P>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.</P> <P>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.<BR /> Is there a way to modify this to add 0's for the hours and other_fields with no events?</P> <P>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".</P> <P>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".</P> <P>Thanks, Colin</P> Tue, 29 Sep 2020 16:53:39 GMT Jonkiye 2020-09-29T16:53:39Z https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296798#M89536 <P>Hello,</P> <P>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:</P> <PRE><CODE>... | stats dc(my_field) by other_field, _time </CODE></PRE> <P>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.</P> <P>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.<BR /> Is there a way to modify this to add 0's for the hours and other_fields with no events?</P> <P>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".</P> <P>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".</P> <P>Thanks, Colin</P> Tue, 29 Sep 2020 16:53:39 GMT https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296798#M89536 Jonkiye 2020-09-29T16:53:39Z https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296799#M89537 <P>Look into the <A href="http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Untable">untable</A> command. It can reformat your timechart results into a table format like you want. Its counterpart, <A href="http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Xyseries">xyseries</A>, reformats the other direction.</P> <PRE><CODE>... | timechart span=1h dc(my_field) as my_field by other_field | untable _time other_field my_field </CODE></PRE> Sun, 19 Nov 2017 15:53:42 GMT https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296799#M89537 maciep 2017-11-19T15:53:42Z https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296800#M89538 <P>This is what I needed, thanks!</P> Mon, 20 Nov 2017 13:23:55 GMT https://community.splunk.com/t5/Splunk-Search/stats-by-date-hour-and-by-another-field-add-zero-count-for-hours/m-p/296800#M89538 Jonkiye 2017-11-20T13:23:55Z