topic Re: Sum to have a value as zero in case not found in Splunk Search

Sum to have a value as zero in case not found

kdulhan — Fri, 22 Sep 2017 15:59:16 GMT

Hi All,

I have some search criteria followed by stats as:

Search ns=app1 Error | stats sum(eval(AcctNo="'1000394'")) as "FailedOccurences".

In case if that AcctNo is not found in the Search, it does not display FailedOccurences.

Kindly let me how can I get a value of Zero for FailedOccurences if AcctNo is not found in Search result.

Thank you.

Re: Sum to have a value as zero in case not found

cmerriman — Fri, 22 Sep 2017 16:09:37 GMT

add |appendpipe [stats count|where count==0|eval FailedOccurences=0|table FailedOccurences] to the end of your search

Re: Sum to have a value as zero in case not found

HiroshiSatoh — Fri, 22 Sep 2017 16:18:05 GMT

Try this!

| stats sum(eval(if(AcctNo="'1000394'",1,0))) as "FailedOccurences"

※In the case of SUM, please set the items you want to total.
1->your field name

Re: Sum to have a value as zero in case not found

kdulhan — Fri, 22 Sep 2017 19:19:52 GMT

The above is not displaying a column "FailedOccureances".

Re: Sum to have a value as zero in case not found

kdulhan — Fri, 22 Sep 2017 19:23:42 GMT

I have the above as a part of biq search query so can't add it. My complete query has the below as a two columns in appendcols.

Search ns=app1 Error | stats sum(eval(AcctNo="'1000394'")) as "FailedOccurences" sum(eval(AcctNo!="'1000394'")) as "SuccessOccurences"

Please let me know how to get the value of 0 for Failed and Success Occureances if not found.

Thanks

Re: Sum to have a value as zero in case not found

cmerriman — Fri, 22 Sep 2017 19:45:36 GMT

since you have a column for FailedOccurences and SuccessOccurences, try this:

...|appendpipe [stats count(FailedOccurences) as count|where count==0|eval FailedOccurences=0|table FailedOccurences]|stats values(*) as *

if your final output is just those two queries, adding this appendpipe at the end should work.

Re: Sum to have a value as zero in case not found

sbbadri — Fri, 22 Sep 2017 20:19:52 GMT

| stats sum(eval(if(user=="1000394",1,0))) as FailedOccurences by AcctNo

Re: Sum to have a value as zero in case not found

entpnerd — Thu, 31 Oct 2019 21:33:55 GMT

For me, none of the previous answers worked due to the nature of my summary query that I'm getting as input. Nonetheless, the problem still boiled down to the fact that my " | stats sum(count) as myVariable " resulted in null instead of 0. The fix that worked for me was to replace that naive sum with the following:

| stats sum(count) as myVariableSum, count as myVariableCount | eval myVariable=(if(myVariableCount>0,myVariableSum,0))

Now, myVariable is guaranteed to be the sum, defaulted to 0 if otherwise null.

How it works: This new sum relies on the fact that count returns 0 in the event of a null input, unlike sum(count) which returns null. So, we can check against the new variable that is guaranteed to be 0 instead of null. Only if the count is greater than 0 can the sum ever be, so we check the count, then use the sum conditionally.

Re: Sum to have a value as zero in case not found

arjunpkishore5 — Fri, 01 Nov 2019 01:16:48 GMT

Add this to the end of your search

your search
| append 
    [ | makeresults 
    | eval FailedOccurences=0]
| stats max(FailedOccurences) as FailedOccurences

Hope this helps

Cheers