topic Re: stats count include zero in Splunk Search

stats count include zero

LearningGuy — Tue, 07 Jan 2025 18:46:14 GMT

Hello,
First, I am aware that there are multiple posts regarding my question, but I can't seem to use them in my scenario.
Please see an example below. There are two fields, location and name. I need to filter out name that contain "2" and stats count name based on location.
I came up with this search, but the problem is it did not include location A (because the count is zero)
Please suggest. I appreciate your help. Thanks

Data

location	name
location A	name A2
location B	name B1
location B	name B2
location C	name C1
location C	name C2
location C	name C3

Expected output:

location	count(name)
location A	0
location B	1
location C	2

Re: stats count include zero

PickleRick — Tue, 07 Jan 2025 20:02:30 GMT

This is the typical "proving the negative" case. https://www.duanewaddle.com/proving-a-negative/

Append (from a lookup or a here-document), sum and you're set.

Re: stats count include zero

LearningGuy — Tue, 07 Jan 2025 21:28:22 GMT

Hello,
Is there any other way to do this?
The data is dynamic. If I am doing this way, I have to have another process to dump a CSV file.
Thanks

Re: stats count include zero

PickleRick — Tue, 07 Jan 2025 21:48:21 GMT

And how is Splunk supposed to know that there should be something there if there isn't? You have to tell it somehow.

If you know that there is alway something with the 2 at the end and it is to be the base of your count, that's a completely different use case and can be done by some groupping and filtering.

Re: stats count include zero

LearningGuy — Tue, 07 Jan 2025 22:58:26 GMT

Hi,

My understanding is the data is there somewhere, Splunk decided to not display 0 when using stats count.

Thanks

Re: stats count include zero

PickleRick — Wed, 08 Jan 2025 05:06:40 GMT

No. Your understanding is wrong. At each step of the search pipeline Splunk only has the data from the previous step in the pipe. So when you filter your data with | search, it onky retains the results matched by your command.

The data from before that command is no longer there so stats cannot use it to group by. So how should it know what are the possible values? Splunk cannot find what is not there so it cannot generate a zero result for a potential non-existing by-field value because it doesn't know what value that would be.

The only case when stats do return a zero value is when you do an overall stats count and you have no results at all.

Re: stats count include zero

isoutamo — Wed, 08 Jan 2025 18:08:18 GMT

One way to do it is this

| makeresults format=csv data="location, name location A, name A2 location B, name B1 location B, name B2 location C, name C1 location C, name C2 location C, name C3" | search name != "*2*" | stats count by location | append [| makeresults format=csv data="location, name location A, name A2 location B, name B1 location B, name B2 location C, name C1 location C, name C2 location C, name C3" | eval count=0 | fields location count | dedup location] | stats sum by location

but as @PickleRick said, Splunk is not good with non existent values.

r. Ismo