topic Re: Calculate average of each column at the bottom in Splunk Search

Calculate average of each column at the bottom

sahil237888 — Mon, 07 Sep 2020 11:15:59 GMT

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.com	minrespond: https://www.google.com	averrespond: https://www.google.com	totalaccesses: https://www.listofurls.com
7/31/2020 0:00	1500	665	56.22727273	18177
7/31/2020 1:00	500	779	47.76659039	692
7/31/2020 2:00	400	774	44.44514768	912
7/31/2020 3:00	9	648	50.61764706	722
7/31/2020 4:00	700	715	47.76427256	721
7/31/2020 5:00	560	1469	50.43421053	821
7/31/2020 6:00	620	1179	48.08455882	741
7/31/2020 7:00	740	688	61.37606838	790
7/31/2020 8:00	235	1322	45.85294118	1388
7/31/2020 9:00	789	559	47.125	327
Average	should show average of above	should show average of above	should show average of above	should 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>>]

Re: Calculate average of each column at the bottom

thambisetty — Mon, 07 Sep 2020 12:12:34 GMT

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"

Re: Calculate average of each column at the bottom

sahil237888 — Tue, 08 Sep 2020 03:18:07 GMT

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.

Re: Calculate average of each column at the bottom

thambisetty — Tue, 08 Sep 2020 03:39:55 GMT

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

Re: Calculate average of each column at the bottom

thambisetty — Tue, 08 Sep 2020 18:10:34 GMT

@sahil237888

Hope you found the solution.