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
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)
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
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.
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)