Splunk Search

How to include counts with 0 events?

eliassplunk
Explorer

Stats count is not showing me the number of counts if there are no events for the particular search.

index="myIndex" AND (sourctype="source1" OR sourcetype="source2") | stats count by sourcetype

Result is showing me:

sourcetype: source1
count: 34

But it is not showing anything for source2 since there are no events for that source.
Below is how I want the result to show:

 sourcetype:  source1                                       count: 34
 sourcetype:  source2                                       count: 0
1 Solution

danielransell
Path Finder

Another way to do this I just learned from my own Splunk Answers question is the method of |stats count(eval(condition)) as countName. Try this search out and see if it works for you:

index="myIndex" sourcetype=source1 OR sourcetype=source2 
| stats count(eval(sourcetype=source1)) AS "Number of Source 1 Events", count(eval(sourcetype=source2)) AS "Source2 Events"

I used different "AS" formats to help breakdown the search - that way you can see what has changed and modify on your own. Anyway, this will add to your total only if the eval condition is true. The inner eval condition could just as easily be EventCode=4624 or anything else you'd like to count - the as is just formatting.

View solution in original post

woodcock
Esteemed Legend

I just answered this here:
https://answers.splunk.com/answers/781448/how-to-group-by-forcing-line-value.html#answer-780753
So like this:

index="myIndex" AND (sourctype="source1" OR sourcetype="source2")
| append [|makeresults
| rename COMMENT AS "This would better be done using a 'lookup' file with ' |inputlookup append=t' instead of '|makeresults ...'"
| eval sourcetype="source1 source2 list all possible values here"
| makemv sourcetype ]
| stats count(host) AS count BY sourcetype
0 Karma

danielransell
Path Finder

Another way to do this I just learned from my own Splunk Answers question is the method of |stats count(eval(condition)) as countName. Try this search out and see if it works for you:

index="myIndex" sourcetype=source1 OR sourcetype=source2 
| stats count(eval(sourcetype=source1)) AS "Number of Source 1 Events", count(eval(sourcetype=source2)) AS "Source2 Events"

I used different "AS" formats to help breakdown the search - that way you can see what has changed and modify on your own. Anyway, this will add to your total only if the eval condition is true. The inner eval condition could just as easily be EventCode=4624 or anything else you'd like to count - the as is just formatting.

eliassplunk
Explorer

Thank you for your help! This worked great!

0 Karma

mayurr98
Super Champion

Try this :

index="myIndex" AND (sourctype="source1" OR sourcetype="source2") 
| stats count by sourcetype 
| append 
    [| stats count 
    | eval sourcetype=split("source1,source2",",") 
    | mvexpand sourcetype] 
| stats sum(count) as count by sourcetype
0 Karma

arjunpkishore5
Motivator

If there are no events for the source, they won't be in the results. If you must show a count always, you can do this.

index="myIndex" AND (sourctype="source1" OR sourcetype="source2") 
| stats count as total by sourcetype
|append 
  [|makeresults 
  | eval sourcetype=mvappend("source1", "source2")
  | mvexpand sourcetype
  | eval total=0]
|stats max(total) as total by sourcetype

Hope this helps.

Cheers

0 Karma

eliassplunk
Explorer

Thank you for your help. I was stuck all day trying to do this.

0 Karma

eliassplunk
Explorer

I'm seeing an issue with the answer. It's pretty 0 no matter what even where there are events for source 2 instead of showing the number of events.

0 Karma

arjunpkishore5
Motivator

That's strange. Can you check if there are no typos on the field names?

As a test, I tried this on the internal index and it works

index=_internal sourcetype=splunkd OR sourcetype=splunkd2 
| stats count as total by sourcetype 
| append 
    [| makeresults 
    | eval sourcetype=mvappend("splunkd", "splunkd2") 
    | mvexpand sourcetype 
    | eval total=0] 
| stats max(total) as total by sourcetype

Can you paste this exact query and check if it works ?

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...