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()?

0 Karma
1 Solution

Splunk Employee
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

View solution in original post

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

0 Karma

Communicator

Ah, I see. Then it's a good thing to know about 😉

0 Karma

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.

0 Karma

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.

0 Karma

Splunk Employee
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

View solution in original post

Splunk Employee
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.

0 Karma

Communicator

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

0 Karma

Legend

How about

search ... AND NOT MyValue=0

?

0 Karma

Communicator

Is your idea in preliminary filtering of zero values before stats?
In my case, "search ..." is a complicated search about 10 lines long and I can't just put AND NOT at the end. Using ... | where MyValue!=0 | stats ... filters out some necessary fields together with MyValue, so it's not the case also.

0 Karma