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!

Updated Team Landing Page in Splunk Observability

We’re making some changes to the team landing page in Splunk Observability, based on your feedback. The ...

New! Splunk Observability Search Enhancements for Splunk APM Services/Traces and ...

Regardless of where you are in Splunk Observability, you can search for relevant APM targets including service ...

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...