topic Re: How to limit number of column in xyseries with TOP or RARE? in Splunk Search

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

ephenix — Fri, 19 Aug 2022 05:52:37 GMT

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

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

chaker — Fri, 19 Aug 2022 06:03:28 GMT

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

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

ITWhisperer — Fri, 19 Aug 2022 07:07:53 GMT

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:

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

ephenix — Fri, 19 Aug 2022 19:08:34 GMT

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

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

ephenix — Fri, 19 Aug 2022 19:01:04 GMT

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