Splunk Search

stats count include zero

LearningGuy
Motivator

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

| 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



Data

locationname
location Aname A2
location Bname B1
location Bname B2
location Cname C1
location Cname C2
location Cname C3

 

Expected output:

locationcount(name)
location A0
location B1
location C2
Labels (2)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

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

View solution in original post

LearningGuy
Motivator

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

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

PickleRick
SplunkTrust
SplunkTrust

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.

LearningGuy
Motivator

Hi,

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

Thanks

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

PickleRick
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

  Now On Demand  Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research ...

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...