topic Re: When using "tstats count", how to display zero results if there are no counts to display? in Splunk Search

When using "tstats count", how to display zero results if there are no counts to display?

jsh315 — Mon, 27 Feb 2017 19:14:35 GMT

I need to use tstats vs stats for performance reasons. I would like tstats count to show 0 if there are no counts to display.

| tstats count where index="abc" by _time span=1h

This would return a table with _time (every hour) and count, like this:

_time                   count
2017-01-01 12:00        50
2017-01-01 13:00        62
2017-01-01 14:00        14

But if the count is zero, it doesn't return that row. How do I get this to display 0 count for that hour?

Thank you.

Re: When using "tstats count", how to display zero results if there are no counts to display?

rjthibod — Mon, 27 Feb 2017 19:29:56 GMT

How about this

 | tstats count where index="abc" by _time span=1h | makecontinuous span=1h _time | fillnull value=0

Re: When using "tstats count", how to display zero results if there are no counts to display?

jsh315 — Mon, 27 Feb 2017 20:39:35 GMT

Did not work. Still getting empty rows for where count is zero.

Re: When using "tstats count", how to display zero results if there are no counts to display?

rjthibod — Mon, 27 Feb 2017 21:01:16 GMT

Try this instead

  | tstats count where index="abc" by _time span=1h | timechart span=1h max(count) as count | fillnull value=0 count

Re: When using "tstats count", how to display zero results if there are no counts to display?

jsh315 — Mon, 27 Feb 2017 21:10:02 GMT

That worked. If you have time I would greatly appreciate the explanation of your query. Thank you so much.

Re: When using "tstats count", how to display zero results if there are no counts to display?

rjthibod — Mon, 27 Feb 2017 21:59:26 GMT

timechart by default (unless you specify fixedrange=f) creates a row for each time bucket from the beginning of the search period until the end of the search period. So, the timechart creates all the necessary rows, and then fillnull puts a 0 in all empty row.

Re: When using "tstats count", how to display zero results if there are no counts to display?

jsh315 — Tue, 28 Feb 2017 20:50:48 GMT

Thanks for the response. Your solution works if there is at least one row that returned some count but if all the rows had zero count then I get "No results found.". Is there a solution to handle this case as well? Thanks a lot.

Re: When using "tstats count", how to display zero results if there are no counts to display?

rjthibod — Tue, 28 Feb 2017 22:56:30 GMT

Yes, you can use append to include a dummy value that gets filtered out if there is real data.

| tstats count where index="abc" by _time span=1h 
| addtotals fieldname=Total
| append [|gentimes start=-1 | addinfo | table info_min_time | bin info_min_time as _time span=1h | eval count = 0 | eval Total = 0 | table _time count Total]
| where (Total = 0 OR count > 0)
| timechart span=1h max(count) as count
| fillnull value=0

Re: When using "tstats count", how to display zero results if there are no counts to display?

jsh315 — Fri, 03 Mar 2017 14:08:49 GMT

That worked. Thanks a lot!