Splunk Search

Omitting zero values when calculating stats avg()?

Communicator

What I want is:

``````... | stats avg(eval(MyValue!=0)) as Avg
``````

It doesn't work that way (Avg is always 1.0).

Of course, the workaround is:

``````... | stats sum(MyValue) as Sum, count(eval(MyValue!=0)) as Count | eval Avg = Sum/Count
``````

But is there a simpler way of using a conditional expression with stats avg()?

Tags (3)
1 Solution
Splunk Employee

Since avg excludes nulls, you can use eval to turn 0 into null, like:

``````... | stats avg(eval(if(MyValue==0, null(), MyValue))) as Avg
``````

Here it's working in a toy example:

``````| stats count | append [stats count | stats count] | stats avg(eval(if(count==0, null(), count))) as avg
``````
Builder

what about before doing the stats command using table/fields to list out all the fields you want to perform stats and then doing this command

| replace 0 with "" in MyValue

so it would be

.....| fields col1 col2 col3 MyValue | replace "0" with "" in MyValue | stats avg(MyValue)

You can also use
| replace "0" with ""
to replace "0" in all fields

Communicator

Ah, I see. Then it's a good thing to know about ๐

Builder

Nothing wrong with Steven's answer, its probably the best way to do most of the time. The option I provided comes in handy if you have 20 fields in a single event that you wanted to get an average for. You would have to create 20 eval's for each field using Steven's method. Using one replace command takes care of all the fields all at once. Also it can come in handy if the 20 fields are constantly changing and you cant predict ahead of time what all the field names are going to be.

Communicator

Well, what's the advantage of your method in comparison with Stephen's answer? I mean, between replacing 0 with an empty string ("") before stats and doing eval(if(...)) which turns 0 to null inside stats?
If stats treats empty strings as nulls, then it works pretty the same way.

Splunk Employee

Since avg excludes nulls, you can use eval to turn 0 into null, like:

``````... | stats avg(eval(if(MyValue==0, null(), MyValue))) as Avg
``````

Here it's working in a toy example:

``````| stats count | append [stats count | stats count] | stats avg(eval(if(count==0, null(), count))) as avg
``````
Splunk Employee

The docs for stats aggregators is at http://docs.splunk.com/Documentation/Splunk/4.3.1/SearchReference/CommonStatsFunctions. However, it doesn't explicitly mention this, since it's just a consequence of the definition of avg as sum divided by count, and count is the number of occurrences.

Communicator

Thanks Stephen, this trick works great!
Is it mentioned somewhere in docs (about avg() and nulls), could you point out?

Legend

``````search ... AND NOT MyValue=0