topic Re: How to include counts with 0 events? in Splunk Search

How to include counts with 0 events?

eliassplunk — Fri, 01 Nov 2019 21:11:12 GMT

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

Re: How to include counts with 0 events?

arjunpkishore5 — Fri, 01 Nov 2019 21:19:47 GMT

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

Re: How to include counts with 0 events?

eliassplunk — Fri, 01 Nov 2019 21:27:00 GMT

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

Re: How to include counts with 0 events?

mayurr98 — Fri, 01 Nov 2019 21:27:47 GMT

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

Re: How to include counts with 0 events?

eliassplunk — Fri, 01 Nov 2019 21:47:31 GMT

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.

Re: How to include counts with 0 events?

arjunpkishore5 — Fri, 01 Nov 2019 23:01:50 GMT

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 ?

Re: How to include counts with 0 events?

danielransell — Mon, 04 Nov 2019 21:57:51 GMT

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.

Re: How to include counts with 0 events?

woodcock — Tue, 05 Nov 2019 05:39:37 GMT

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

Re: How to include counts with 0 events?

eliassplunk — Wed, 20 Nov 2019 21:25:03 GMT

Thank you for your help! This worked great!