Splunk Search

Sum to have a value as zero in case not found

kdulhan
Explorer

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
Motivator

Add this to the end of your search

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

Hope this helps

Cheers

0 Karma

entpnerd
Explorer

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.

0 Karma

HiroshiSatoh
Champion

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

0 Karma

kdulhan
Explorer

The above is not displaying a column "FailedOccureances".

0 Karma

sbbadri
Motivator

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

0 Karma

cmerriman
Super Champion

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

0 Karma

kdulhan
Explorer

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

0 Karma

cmerriman
Super Champion

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.

0 Karma
Get Updates on the Splunk Community!

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

Customer Experience | Join the Customer Advisory Board!

Are you ready to take your Splunk journey to the next level? 🚀 We invite you to join our elite squad ...