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>>]
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.
I have tried this using _internal logs it is working. May be you need to check base search and base search in sub 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
| 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"