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!

#### Observability Highlights | January 2023 Newsletter

January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

#### Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

#### Platform Highlights | January 2023 Newsletter

January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...