How do I automatically create tables in Athena to search through CloudTrail logs?

Resolution

Use the CloudTrail console to automatically create the Athena tables. For more information, see AWS CloudTrail log search using Amazon Athena.

Create the Athena table from your CloudTrail logs

Complete the following steps:

1. Open the CloudTrail console.
2. In the navigation pane, Choose Trails, and then note the name of the Amazon Simple Storage Service (Amazon S3) bucket that contains the CloudTrail logs.
3. In the navigation pane, choose Event history, and then choose Create Athena table.
4. In the Create a table in Amazon Athena window, select the S3 bucket from the Storage location menu.
   Note: To improve your search within a specific time range, you can use partition projection for CloudTrail logs in Athena.
5. Choose Create table.

Note: The table references all CloudTrail logs in the specified S3 bucket, including new logs that CloudTrail delivers to the bucket.

You might receive the "Your account does not have sufficient permissions to create tables in Amazon Athena" error. If you receive this error, then attach the AmazonAthenaFullAccess managed policy to your AWS Identity and Access Management (IAM) role.

Run Athena queries on your CloudTrail logs table

Complete the following steps:

1. Open the Athena console.
2. Choose Clear to clear the sample query or choose the plus sign to open a new tab.
3. Enter your query, and then choose Run query.

Use example queries

The following are examples of queries that you can run.

Note: In the following queries, replace your_athena_tablename with the name of your Athena table and access_key_id with your 20-character access key. Your access key usually begins with the characters AKIA or ASIA.

To display all recorded AWS API activity for a specific access key, run the following query:

SELECT eventTime, eventName, userIdentity.principalId  
  FROM your_athena_tablename   
 WHERE userIdentity.accessKeyId like 'access_key_id'

To identify security group changes to your Amazon Elastic Compute Cloud (Amazon EC2) instance, run the following query:

SELECT eventname, useridentity.username, sourceIPAddress, eventtime, requestparameters  
  FROM your_athena_tablename  
 WHERE (requestparameters LIKE '%sg-5887f224%' OR requestparameters LIKE '%sg-e214609e%' OR requestparameters LIKE '%eni-6c5ca5a8%')  
       AND eventtime > '2017-02-15T00:00:00Z'  
 ORDER BY eventtime ASC;

To display all console logins over the last 24 hours, run the following query:

SELECT useridentity.username, sourceipaddress, eventtime, additionaleventdata  
  FROM your_athena_tablename   
 WHERE eventname = 'ConsoleLogin'  
       AND eventtime >= '2017-02-17T00:00:00Z'  
       AND eventtime < '2017-02-18T00:00:00Z';

To display all failed console sign-in attempts over the last 24 hours, run the following query:

SELECT useridentity.username, sourceipaddress, eventtime, additionaleventdata  
  FROM your_athena_tablename  
 WHERE eventname = 'ConsoleLogin'  
       AND useridentity.username = 'HIDDEN_DUE_TO_SECURITY_REASONS'  
       AND eventtime >= '2017-02-17T00:00:00Z'  
       AND eventtime < '2017-02-18T00:00:00Z';

To identify CloudTrail monthly cost increases, run the following query:

SELECT eventName,count(eventName) AS NumberOfChanges,eventSource  
  FROM your_athena_tablename   
 WHERE eventtime >= '2019-01-01T00:00:00Z' AND eventtime < '2019-01-31T00:00:00Z'  
 GROUP BY eventName, eventSource  
 ORDER BY NumberOfChanges DESC

Note: The defined eventtime values are for a single month. To get results for multiple months, run additional queries.

To identify missing IAM permissions, run the following query:

SELECT COUNT(*) AS TotalEvents, useridentity.arn, eventsource, eventname, errorCode, errorMessage  
  FROM your_athena_tablename  
 WHERE (errorcode LIKE '%Denied%' OR errorcode LIKE '%Unauthorized%')  
   AND eventtime >= '2019-10-28T00:00:00Z'  
   AND eventtime < '2019-10-29T00:00:00Z'  
 GROUP BY eventsource, eventname, errorCode, errorMessage, useridentity.arn  
 ORDER BY eventsource, eventname

If the results don't display the API call that failed, then increase the query range:

SELECT COUNT(*) as TotalEvents, useridentity.arn, eventsource, eventname, errorCode, errorMessage  
  FROM your_athena_tablename  
 WHERE errorcode <> ''  
       AND eventtime >= '2019-10-28T00:00:00Z'  
       AND eventtime < '2019-10-29T00:00:00Z'  
 GROUP BY eventsource, eventname, errorCode, errorMessage, useridentity.arn  
 ORDER BY eventsource, eventname

Related information

Query AWS CloudTrail logs

Adding and removing IAM identity permissions

How can I get data to help troubleshoot IAM permission access denied or unauthorized errors?