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!

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...