Splunk Search

## Calculate Average for multiple fields

Explorer

My logs currently capture transaction summaries. The transaction summaries can have 0 to n number of integration.

For Example:

``````2014-10-13T14:05:27 FLAT_TXN_SUMMARY mdUUID=a intId1="ledgerDataSource" intResulstSetCall1="0" intEl1="105" intCnt1="12" intCat1="JDBC"
2014-10-13T14:05:32 FLAT_TXN_SUMMARY mdUUID=b intId1="dupeCheckCtsDataSource" intResulstSetCall1="0" intEl1="20" intCnt1="1" intCat1="JDBC" intId2="provinqUncommittedDataSource" intResulstSetCall2="17" intEl2="478" intCnt2="33" intCat2="JDBC" intId3="contractDataSource" intResulstSetCall3="2" intEl3="12" intCnt3="1" intCat3="JDBC"
``````

How do I calculate the average of all intEl* when total number of intEl fields is unknown as it will vary per transaction?

When I search
....| stats avg(intEl*)
I get the average for each intEl field so if I were to use the two log statements above it would give me the average for intEl1, intEl2, and intEl3. I would like one overall average for all intEl*

...| stats avg(intEl*)

results: avg(intEl1)= 62.5, avg(intEl2)=478, avg(intEl3)=12

Would like results to be: AverageintEl=153.75

Keep in mind there can be an unknown amount of intEl fields.

Tags (3)
1 Solution
SplunkTrust

I believe Somesh's answer would actually produce the sum of averages (or an average of sums?) rather than the overall average. Give this a shot:

``````... | eval field_count = 0 | eval field_sum = 0
| foreach intEl* [eval field_count = field_count + 1 | eval field_sum = field_sum + '<<FIELD>>']
| stats sum(field_sum) as field_sum sum(field_count) as field_count
| eval AverageintEl = field_sum / field_count | fields - field_*
``````

This will build a sum and a counter in lock step, giving you the input needed to calculate the average over any number of fields.

SplunkTrust

I believe Somesh's answer would actually produce the sum of averages (or an average of sums?) rather than the overall average. Give this a shot:

``````... | eval field_count = 0 | eval field_sum = 0
| foreach intEl* [eval field_count = field_count + 1 | eval field_sum = field_sum + '<<FIELD>>']
| stats sum(field_sum) as field_sum sum(field_count) as field_count
| eval AverageintEl = field_sum / field_count | fields - field_*
``````

This will build a sum and a counter in lock step, giving you the input needed to calculate the average over any number of fields.

Explorer

This answer was really close to answering my question. The foreach really helped me out. Here is what ended up working for me

...| stats count(intEl*) sum(intEl*)
| eval intEl_count = 0
| foreach count* [eval intEl_count = intEl_count + '<>']
| eval intEl_sum = 0
| foreach sum* [eval intEl_sum = intEl_sum + '<>']
| eval AverageIntEl = intEl_sum / intEl_count
| fields - field_*

Revered Legend

Give this a try

``````your base search  | addtotals fieldname=intEl intEl* | stats avg(intEl)
``````
Get Updates on the Splunk Community!

#### New This Month - More Capabilities to Simplify GDI Plus Enhancements in Observability ...

The latest enhancements to the Observability Portfolio deliver more assisted onboarding and expanded content ...

#### Ingest Actions Output Announces Support for File System Destinations

Introduction As part of Splunk Enterprise 9.3, we are proud to announce the general availability of File ...

#### Autoscaling Kubernetes Workloads with Splunk

About What if you had the ability to scale your Kubernetes pods as a result of information output from Splunk? ...