topic Re: Calculate the average in the total row in Splunk Search

How to calculate the average in the total row?

boxmetal — Tue, 20 Dec 2022 14:07:43 GMT

Hi Splunk community,

I need to display data shown as table below

Component	Total units	Violated units	Matched [%]
Type A	1	1	99
Type B	10	10	75
Type C	100	85	85
Total	111	96	86

In the total row, the matched value is the average of the column, while others are the sum value.

Is it possible to insert the average value to the total row as shown?

Here's my SPL:

index="my_index"source="*sourcename*" | stats count as total_units count(eval(isnull(approval_message))) as violated_units values(matched_percentage) as matched by component | addcoltotals total_units + violated_units labelfield=component | rename total_units as "Total Units", violated_units as "Violated Units", matched as "Matched [%]"

Re: Calculate the average in the total row

Manasa_401 — Tue, 20 Dec 2022 07:40:32 GMT

Hello @boxmetal

Try the below query:

index="my_index"source="*sourcename*" | stats count as total_units count(eval(isnull(approval_message))) as violated_units values(matched_percentage) as matched by component | addcoltotals total_units + violated_units labelfield=component | streamstats avg(matched) as average | eval matched=coalesce(matched,average) | fields - average | rename total_units as "Total Units", violated_units as "Violated Units", matched as "Matched [%]"

If this helps an upvote would be appreciated.

Thanks,

Manasa

Re: Calculate the average in the total row

boxmetal — Tue, 20 Dec 2022 08:07:58 GMT

Hi @Manasa_401 ,

The table showed average calculation. Thank you a lot!