Omitting zero values when calculating stats avg()?

[greg]

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

[cramasta]

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

[greg]

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

[cramasta]

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.

[greg]

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.

[Stephen_Sorkin · Accepted solution]

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

[Stephen_Sorkin]

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.

[greg]

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

[Ayn]

How about

search ... AND NOT MyValue=0

?

[greg]

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.