Athena

Querying AWS CloudTrail Logs with Athena

Using Athena with CloudTrail logs is a powerful way to enhance your analysis of AWS service activity. For example, you can use queries to identify trends and further isolate activity by attributes, such as source IP address or user.

  1. Go to the S3 Console
    • Click Create bucket button
    • Find your accountid at top right corner of the screen AccountID
    • Enter a Bucket name such as aws-athena-query-results-accountid-us-east-1 (replace accountid with yours)
    • Leave everything else as it is and click Create bucket
  2. Go to the Athena Console
    • Click on Get Started
    • Click on Settings at top right corner of the screen
    • Enter Query result location with your S3 bucket such as s3://aws-athena-query-results-accountid-us-east-1/ (replace accountid with yours)
    • Leave everything else as it is and click Save
  3. Go to the CloudTrail Event History Console
    • Click on Create Athena Table
    • Choose Storage location from the dropdown: aws-cloudtrail-logs-accountid-hash
    • Click on Create table
  4. Go back to the Athena Console
    • On the left, choose default Database and you will see the Athena Table Name.
    • Copy the table and substitute with the TABLE_NAME in the following queries and click on Run Query. Athena Table Name
  5. Console Sign-in activity: Whether it’s to help meet a compliance standard such as PCI, adhering to a best practice security framework such as NIST, or just wanting to better understand who is accessing your assets, auditing your login activity is vital. The following query returns details such as user name, IP address, time of day, whether the login was from a mobile console version, and whether multi-factor authentication was used. 
    SELECT useridentity.username,
         errormessage,
         sourceipaddress,
         eventtime,
         additionaleventdata
FROM default.TABLE_NAME
WHERE eventname = 'ConsoleLogin'

    This is how it looks like on Athena Console. Athena Table Name

  6. AWS event errors: Recurring error messages can be a sign of an incorrectly configured policy, the wrong permissions applied to an application, or an unknown change in your workloads. The following query shows the top 10 errors that have occurred from the start of the year. 
    SELECT count (*) AS TotalEvents,
         eventname,
         errorcode,
         errormessage
FROM default.TABLE_NAME
WHERE errorcode is NOT null
        AND eventtime >= '2021-05-01T00:00:00Z'
GROUP BY  eventname, errorcode, errormessage
ORDER BY  TotalEvents DESC limit 10

    This is how it looks like on Athena Console.

Athena Table Name