Part 2 Using Athena to query S3 buckets

This is in continuation to my previous post on how can use the Athena to query the s3 buckets storing the cloudtrail logs in order to better organize your security and compliance which is hard thing to achieve in a legacy/large accounts with number of users.

Question:- Identifying the last 100 most used IAM Keys. Usually IAM roles is better approach to be used than using the IAM keys for the authentication as IAM roles can rotate the keys after every 15minutes thus making hard to intercept the keys and increasing the security of the Account.

Answer

 SELECT  
  useridentity.accesskeyid,

  useridentity.arn,

  eventname,

  COUNT(eventname) as frequency

 FROM account_cloudtrail

 WHERE sourceipaddress NOT LIKE '%.com'

   AND year = '2019'

   AND month = '01'

   AND day = '01'

   AND useridentity.accesskeyid LIKE 'AKIA%'

 GROUP BY useridentity.accesskeyid, useridentity.arn, eventname

 ORDER BY frequency DESC

 LIMIT 100