Sum to have a value as zero in case not found

[kdulhan]

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.

[arjunpkishore5]

Add this to the end of your search

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

Hope this helps

Cheers

[entpnerd]

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.

[HiroshiSatoh]

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

[kdulhan]

The above is not displaying a column "FailedOccureances".

[sbbadri]

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

[cmerriman]

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

[kdulhan]

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

[cmerriman]

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.