Splunk Search

Calculate average of each column at the bottom

sahil237888
Path Finder

Hi ,

Can anyone help me- how to get average of the all the columns at the bottom.

The output should be like - 

ctime totalaccesses: https://www.google.comminrespond: https://www.google.comaverrespond: https://www.google.comtotalaccesses: https://www.listofurls.com
7/31/2020 0:00150066556.2272727318177
7/31/2020 1:0050077947.76659039692
7/31/2020 2:0040077444.44514768912
7/31/2020 3:00964850.61764706722
7/31/2020 4:0070071547.76427256721
7/31/2020 5:00560146950.43421053821
7/31/2020 6:00620117948.08455882741
7/31/2020 7:0074068861.37606838790
7/31/2020 8:00235132245.852941181388
7/31/2020 9:0078955947.125327
Averageshould show average of aboveshould show average of aboveshould show average of aboveshould show average of above



I am using below query but it is not giving correct output (doubt if I am using correct query)-

index=servers host="Datacenter01" websites IN ("https://www.google.com","https://www.listofurls.com","https://www.fishbucks.com")
|bucket _time span=1h
| convert timeformat=" %m/%d/%y %H:%M:%S" ctime(_time) AS ctime
| stats count as totalaccesses min(responsd_time) as minrespond max(responsd_time) as maxrespond avg(responsd_time) as averrespond by ctime over websites

| foreach *minrespond *maxrespond *averrespond [eval sum_<<FIELD>> = 0, count_<<FIELD>> = 0]
| foreach *minrespond [eval sum_A_Field = sum_A_Field + '<<FIELD>>', count_A_Field = count_A_Field + 1]
| foreach *minrespond *maxrespond *averrespond [eval avg_<<FIELD>> = sum_<<FIELD>> / count_<<FIELD>>]

 

Labels (1)
0 Karma

sahil237888
Path Finder

Hi thambisetty,

 

I tried the same but its not giving me correct result i.e. it is not giving average of each column at the bottom but only giving the details like totalaccesses,maxrespond, minrespond of each url day wise.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

I have tried this using _internal logs it is working. May be you need to check base search and base search in sub search.

————————————
If this helps, give a like below.
0 Karma

thambisetty
SplunkTrust
SplunkTrust

@sahil237888 

Hope you found the solution.

————————————
If this helps, give a like below.
0 Karma

thambisetty
SplunkTrust
SplunkTrust
index=servers host="Datacenter01" websites IN ("https://www.google.com","https://www.listofurls.com","https://www.fishbucks.com") 
| bucket _time span=1h 
| stats count as totalaccesses min(responsd_time) as minrespond max(responsd_time) as maxrespond avg(responsd_time) as averrespond by _time websites 
| append 
    [ search index=servers host="Datacenter01" websites IN ("https://www.google.com","https://www.listofurls.com","https://www.fishbucks.com") 
    | bucket _time span=1h 
    | stats count as totalaccesses min(responsd_time) as minrespond max(responsd_time) as maxrespond avg(responsd_time) as averrespond by _time websites 
    | stats avg(totalaccesses) as avg_totalaccesses avg(minrespond) as avg_minrespond avg(maxrespond) as avg_maxrespond avg(averrespond) as avg_averrespond ]
| foreach totalaccesses,minrespond,maxrespond,averrespond [eval <<FIELD>>=coalesce(<<FIELD>>,round(avg_<<FIELD>>,2))]
| fields - avg_*
| fillnull value="Average"
————————————
If this helps, give a like below.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...