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!

#### Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

#### Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

#### Take the 2021 Splunk Career Survey for \$50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...