Splunk Search

Need to calculate weighted average across columns like addtotals does sum across columns (from chart command)

hmahendrakumar
Path Finder

I have asked almost the same question here. I will try to explain my question better here

My command looks like this

... | stats sum(field1) as sumf1, sum(field2) as countf2 by  field3,field4  | eval avgf5 = (sumf1/countf2 )  | chart sum(avgf5) as avgf5 over field4 by  field3 

The chart command transposes field3 values across columns... This produces a tables with columns(that is equal to no of distinct field3 values) ....

If I want to have a sum across cols I can use addtotals What can I use for average(weighted) across cols? Can I write my own function?

1 Solution

Stephen_Sorkin
Splunk Employee
Splunk Employee

You should be able to make field3 a multivalued field that contains its original value as well as the value ALL so that the stats command calculates the global sum and count for every value of field4, stored as field3=ALL and then aggregated properly later. Let's assume that field3 never has commas in it:

... | eval field3 = field3 . ",ALL"
    | makemv delim="," field3
    | stats sum(field1) as sumf1, count(field2) as countf2 by field3 field4
    | eval avgf5 = (sumf1/countf2)
    | chart first(avgf5) over field4 by field3 

If field3 does have commas in it, you can just pick another delimiter.

View solution in original post

Stephen_Sorkin
Splunk Employee
Splunk Employee

You should be able to make field3 a multivalued field that contains its original value as well as the value ALL so that the stats command calculates the global sum and count for every value of field4, stored as field3=ALL and then aggregated properly later. Let's assume that field3 never has commas in it:

... | eval field3 = field3 . ",ALL"
    | makemv delim="," field3
    | stats sum(field1) as sumf1, count(field2) as countf2 by field3 field4
    | eval avgf5 = (sumf1/countf2)
    | chart first(avgf5) over field4 by field3 

If field3 does have commas in it, you can just pick another delimiter.

hmahendrakumar
Path Finder

This solution is simply awesome....

0 Karma

hmahendrakumar
Path Finder

Or When we use an avg function in chart command, is there a way to get overall average ?

0 Karma
Get Updates on the Splunk Community!

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

What's New in Splunk Observability Cloud and Splunk AppDynamics - May 2025

This month, we’re delivering several new innovations in Splunk Observability Cloud and Splunk AppDynamics ...