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)
0 Karma
1 Solution

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

View solution in original post

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

View solution in original post

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)