Splunk Search

How to add totals to xyseries table?

swengroeneveld
Explorer

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.

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...