Splunk Search

Find average when using group by

balash1979
Path Finder

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

Tags (2)
0 Karma
1 Solution

cmerriman
Super Champion

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

View solution in original post

0 Karma

cmerriman
Super Champion

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@balash1979,

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

Happy Splunking!

balash1979
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...