topic Re: Find average when using group by in Splunk Search

Find average when using group by

balash1979 — Wed, 30 Sep 2020 02:22:50 GMT

Here is my query

index="search_index"   search processing_service | eval time_in_mins=('metric_value')/60 | stats avg(time_in_mins) by channel

By running this query, i get the following results
channel1 5.25
channel2 6.25
channel3 10.25

Basically, i get output of all the channels and their averages. how can I get only 1 value with the average of all the channel averages ?
I would like an output of my query as

Avg_of_all_channels 7.25

Re: Find average when using group by

renjith_nair — Thu, 03 Oct 2019 14:33:42 GMT

@balash1979,

What do you get after removing the by channel from the search ?

Re: Find average when using group by

balash1979 — Thu, 03 Oct 2019 20:10:28 GMT

Thanks. I just removed by channel and it worked. I was just overthinking.

Re: Find average when using group by

cmerriman — Wed, 30 Sep 2020 02:28:36 GMT

as @renjith.nair stated in the comments, I believe what you're after is simply

index="search_index"   search processing_service | eval time_in_mins=('metric_value')/60 | stats avg(time_in_mins) as all_channel_avg

which would just output one column named all_channel_avg and one row with the avg.

if you'd like both the individual channel avg AND the total avg, possibly something like:

index="search_index"   search processing_service | eval time_in_mins=('metric_value')/60 |eventstats avg(time_in_mins) as total_avg| stats values(total_avg) as all_channel_avg avg(time_in_mins) as channel_avg by channel

however, you might want to do a count and sum in the stats command and then the eventstats and some eval in order to not run eventstats before stats.

index="search_index"   search processing_service | eval time_in_mins=('metric_value')/60| stats  avg(time_in_mins) as channel_avg sum(time_in_mins) as total_mins count as total_count by channel|eventstats sum(total_mins) as total_mins sum(total_count) as total_count|eval all_channel_avg=total_mins/total_count

again, that might actually need some work, as i'm currently really thinking that the math might not be right....