topic Re: Omitting zero values when calculating stats avg()? in Splunk Search

Omitting zero values when calculating stats avg()?

greg — Tue, 27 Mar 2012 14:20:01 GMT

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

Re: Omitting zero values when calculating stats avg()?

Ayn — Tue, 27 Mar 2012 14:26:12 GMT

How about

search ... AND NOT MyValue=0

Re: Omitting zero values when calculating stats avg()?

greg — Tue, 27 Mar 2012 14:43:14 GMT

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.

Re: Omitting zero values when calculating stats avg()?

Stephen_Sorkin — Tue, 27 Mar 2012 15:41:37 GMT

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

Re: Omitting zero values when calculating stats avg()?

greg — Tue, 27 Mar 2012 15:57:54 GMT

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

Re: Omitting zero values when calculating stats avg()?

Stephen_Sorkin — Tue, 27 Mar 2012 16:16:10 GMT

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.

Re: Omitting zero values when calculating stats avg()?

cramasta — Fri, 02 Aug 2013 19:26:25 GMT

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

Re: Omitting zero values when calculating stats avg()?

greg — Mon, 05 Aug 2013 10:01:18 GMT

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.

Re: Omitting zero values when calculating stats avg()?

cramasta — Mon, 05 Aug 2013 14:05:53 GMT

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.

Re: Omitting zero values when calculating stats avg()?

greg — Mon, 05 Aug 2013 14:09:32 GMT

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