How to add totals to xyseries table?

[swengroeneveld]

We are working to enhance our potential bot-traffic blocking and would like to see every IP that has hit AWS cloudfront > 3000 hits per day with a total + percentage of the total traffic that day.

Eventually I got as for with my searches to include appendpipe, this is also the point where I get stuck and will require some guidance. 

The result I would like to get is as follows:

weekday	1.1.1.1	2.2.2.2	3.3.3.3	total traffic	perc. of all traffic
Monday	3000			400000	0.75
Tuesday		3000	3000	400000	1.5
Wednesday	3000			400000	0.75
Thursday	3000	4000	5000	400000	3
Friday		3000		400000	0.75
Saturday	3000			400000	0.75
Sunday		3000		400000	0.75

This is where I got stuck with my query (and yes the percentage is not even included in the query below)

index=awscloudfront
| fields date_wday, c_ip
| convert auto(*)
| stats count by date_wday c_ip | appendpipe [stats count as cnt by date_wday]
| where count > 3000 
| xyseries date_wday,c_ip,cnt

 Any insights / thoughts are very welcome.

[ITWhisperer · Accepted solution]

Try something like this

index=awscloudfront
| fields date_wday, c_ip
| convert auto(*)
| stats count by date_wday c_ip 
| eval large=if(count>=3000,count,null())
| eventstats sum(large) as daily by date_wday
| eventstats sum(count) as grand
| fields - large
| where count >= 3000
| eval c_ip=mvappend(c_ip, "daily", "grand")
| mvexpand c_ip 
| eval count=case(c_ip="daily",daily,c_ip="grand",grand,1==1,count)
| xyseries date_wday c_ip count
| eval percent=round(100*daily/grand,2)
| fields - daily
| table date_wday * grand percent

[swengroeneveld]

Brilliant! With some minor adjustments (excluding white listed IPs), this is exactly what I was looking for.