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

[ephenix]

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

[ITWhisperer · Accepted solution]

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]

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

[chaker]

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]

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