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

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@balash1979,

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

balash1979
Path Finder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!