Splunk Search

How to limit number of column in xyseries with TOP or RARE?

ephenix
Explorer

Hi,

I'm building a report to count the numbers of events per AWS accounts vs Regions with stats and xyseries. It works well but I would like to filter to have only the 5 rare regions (fewer events). When I'm adding the rare, it just doesn’t work.  

index=aws sourcetype="aws:cloudtrail"
| rare limit=5 awsRegion 
| stats count by awsRegion, account
| xyseries account awsRegion count

Any ideas?

Thanks

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

The reason it doesn't work is that rare will only leave the regions without the corresponding accounts

Assuming you want the counts for all the accounts in the 5 lowest total regions, you could do something like this:

index=aws sourcetype="aws:cloudtrail"
| stats count by awsRegion account
| eventstats sum(count) as total by awsRegion
| sort 0 total awsRegion
| streamstats dc(awsRegion) as rank
| where rank < 6
| xyseries account awsRegion count

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

The reason it doesn't work is that rare will only leave the regions without the corresponding accounts

Assuming you want the counts for all the accounts in the 5 lowest total regions, you could do something like this:

index=aws sourcetype="aws:cloudtrail"
| stats count by awsRegion account
| eventstats sum(count) as total by awsRegion
| sort 0 total awsRegion
| streamstats dc(awsRegion) as rank
| where rank < 6
| xyseries account awsRegion count

ephenix
Explorer

Thanks @ITWhisperer , Great look like it is working! Interesting new approach that I will explore more. 

I also add a total to sort it not sure it is the most efficient way but it did work. 

index=aws sourcetype="aws:cloudtrail"
| stats count by awsRegion account
| eventstats sum(count) as total by awsRegion
| sort 0 total awsRegion
| streamstats dc(awsRegion) as rank
| where rank < 5
| xyseries account awsRegion count
| addtotals fieldname=TotalEvents
| eval TotalEvents=TotalEvents-account
| sort TotalEvents

0 Karma

chaker
Contributor

Something like this should work, I don't have any cloudtrail data on hand at the moment.


index=aws sourcetype="aws:cloudtrail"

| chart count by account awsRegion  limit=0
| addtotals
| sort limit=5 Total

ephenix
Explorer

Thanks @chaker Unfortunately, your suggestion that still provide another way to express a table but only take the top 5 in alphabetic not in term of less or most use

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 ...