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 Enterprise 9.4: Features to Power Your Digital Resilience

Hey Splunky People! We are excited to share the latest updates in Splunk Enterprise 9.4. In this release we ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...