Splunk Search

## Include zero in the average

Path Finder

Splunkers!

I'm not able to solve a strange issue...

Basically, the stats avg() is omitting values in the calculation.

Code is the following:

``````<<search>>

| fillnull Incident_Duration,Unavailabilty_Month value=0
| stats avg(Incident_Duration) as Incident_Avg, avg(Unavailabilty_Month) as Unavailabilty_Avg by ReqMonthAva, AppID
``````

So, null value are transformed into 0 in the table resulting from the previous search, but avg function is not considering them while processing Incident_Avg and Unavailabilty_Avg

Seems to me very strange... how can i solve?

Tags (3)
1 Solution
SplunkTrust

can you try

`````` <<search>>

| fillnull
| stats avg(Incident_Duration) as Incident_Avg, avg(Unavailabilty_Month) as Unavailabilty_Avg by ReqMonthAva, AppID
``````
Super Champion

can you try:

`````` ..|eval Incident_Duration=tonumber(Incident_Duration), Unavailabilty_Month=tonumber(Unavailabilty_Month)
| fillnull Incident_Duration,Unavailabilty_Month value=0
| stats avg(Incident_Duration) as Incident_Avg, avg(Unavailabilty_Month) as Unavailabilty_Avg by ReqMonthAva, AppID
``````

i think it might be that your values are strings and not numbers. using this runanywhere command: `|makeresults|eval data=" ,2,1,4,6"|makemv delim="," data|mvexpand data|eval with_zero=tonumber(data)|fillnull with_zero value=0|stats avg(data) avg(with_zero)`, the values are different.

Path Finder

See above, the problem was related to <> values in ReqMonthAva.

Tks!
Carmine

SplunkTrust

can you try

`````` <<search>>

| fillnull
| stats avg(Incident_Duration) as Incident_Avg, avg(Unavailabilty_Month) as Unavailabilty_Avg by ReqMonthAva, AppID
``````
Path Finder

It works!

I found the root problem... basically RegMonthAva had some <> value (corresponding with <> values for Unavailability_month and Incident_Duration).

With | fillnull i solve the problem.

Tks!

Carmine

SplunkTrust

I am glad that my solution helped you to get the desired results. I am converting my comment to an answer. Accept/upvote it if this is an answer to your question!

Register for .conf21 Now! Go Vegas or Go Virtual!

### How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20. Learn More or Register Now >

Get Updates on the Splunk Community!