Splunk Search

Include zero in the average

CarmineCalo
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?

Tks in advance!

0 Karma
1 Solution

mayurr98
Super Champion

can you try

 <<search>>

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

View solution in original post

0 Karma

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

0 Karma

CarmineCalo
Path Finder

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

Tks!
Carmine

0 Karma

mayurr98
Super Champion

can you try

 <<search>>

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

CarmineCalo
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

0 Karma

mayurr98
Super Champion

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!

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...