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]

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

[stoutrw]

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

[dayananda7449]

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.

[woodcock · Accepted solution]

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)