topic Splunk replaces zero with null values. Chart Avg(value) omits all those null values, with this avg result is not correct. How to fix this? in Splunk Search

Splunk replaces zero with null values. Chart Avg(value) omits all those null values, with this avg result is not correct. How to fix this?

dayananda7449 — Wed, 06 Jul 2016 01:59:24 GMT

Hi Everyone, I am a newbie to splunk. We are using splunk to monitor our custom perfmon counters. see the below search query. While performing avg on one of the counters, I am not getting the right result as i expect. I tried with fillnull and so on. Still the same issue. I can fill nulls but i can't those values of zero's in to consideration while calculating average.

index=perfmon collection=ServiceBus counter="Sent/sec" instance="ABC" host ="XYZ" | Timechart avg(Value)

Result from the above query.

2016-06-27 23:46:00      
2016-06-27 23:47:00     10
2016-06-27 23:48:00     10
2016-06-27 23:49:00      
2016-06-27 23:50:00     10
2016-06-27 23:51:00      
2016-06-27 23:52:00      
2016-06-27 23:53:00     10
2016-06-27 23:54:00      
2016-06-27 23:55:00      
2016-06-27 23:56:00     10
2016-06-27 23:57:00      
2016-06-27 23:58:00      
2016-06-27 23:59:00     10
2016-06-28 00:00:00      
2016-06-28 00:01:00     10

Below is the query that i use to calculate average.

index=perfmon collection=ServiceBus counter="Sent/sec" instance="ABC" host ="XYZ" | chart avg(Value)

Result from above query is 10 (since i have 7 event where data is non zero, sum all the values and divided by the total events = 70/7 = 10) . This is not the result what i am looking for.

Expected Result is 70/16 = 4.375 . (I have 16 instances of data being reported in the timechart)

Can someone help me out how to fix this issue?

Thanks,
Dayananda

Re: Splunk replaces zero with null values. Chart Avg(value) omits all those null values, with this avg result is not correct. How to fix this?

woodcock — Thu, 07 Jul 2016 19:24:05 GMT

Like this:

index=perfmon collection=ServiceBus counter="Sent/sec" instance="ABC" host ="XYZ" | timechart avg(Value) AS Value | fillnull value="0" Value | chart avg(Value)

Re: Splunk replaces zero with null values. Chart Avg(value) omits all those null values, with this avg result is not correct. How to fix this?

dayananda7449 — Wed, 02 Nov 2016 17:51:10 GMT

We resolved this issue by adding showZeroValue = 1 in the inputs.config file for each and every category where we need splunk to captured zero value data while forwarding the data to splunk.

Re: Splunk replaces zero with null values. Chart Avg(value) omits all those null values, with this avg result is not correct. How to fix this?

stoutrw — Wed, 22 May 2019 21:32:51 GMT

This is also relevant if you don't want to use the fillnull command. There's an option in the visualization tab.

https://answers.splunk.com/answers/474799/how-to-delete-data-points-with-null-values-by-host.html