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

Resolution

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, use partition projection for CloudTrail logs in Athena.
5. Choose Create table.
   Note: The table references CloudTrail logs in the specified S3 bucket, including new logs that CloudTrail delivers to the bucket.

If you don't have permissions to create a table, then you might get the "Your account does not have sufficient permissions to create tables in Amazon Athena" error message.

To resolve this error, 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. To clear the sample query, choose Clear.
3. Enter your query and then choose Run query.

Use example queries

If this is your first time using Athena, then set an S3 bucket as a location to store query results before you run your first query.

To set an S3 bucket location, complete the following steps:

1. Open the Athena console.
2. In the navigation pane, choose Query editor.
3. Choose the Query settings tab.
4. Choose Manage.
5. In the Location of query result - optional field, enter an S3 bucket.
6. Choose Save.

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 recorded AWS API operation 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 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 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 operation that failed, then run the following query to 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