topic Re: Calculate Average for multiple fields in Splunk Search

Calculate Average for multiple fields

Brittany_Carr — Mon, 28 Sep 2020 17:55:49 GMT

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.

Re: Calculate Average for multiple fields

somesoni2 — Fri, 17 Oct 2014 21:38:40 GMT

Give this a try

your base search  | addtotals fieldname=intEl intEl* | stats avg(intEl)

Re: Calculate Average for multiple fields

martin_mueller — Fri, 17 Oct 2014 22:52:03 GMT

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.

Re: Calculate Average for multiple fields

Brittany_Carr — Mon, 28 Sep 2020 17:56:58 GMT

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