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
Super Champion

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
Super Champion

@sahil237888 

Hope you found the solution.

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

thambisetty
Super Champion
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
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!