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.
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))
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.
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.
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.