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
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...