How do I automatically create tables in Amazon Athena to search through AWS 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

1.    Open the CloudTrail console, and then choose Trails from the navigation pane. Note the S3 bucket name.

2.    In the navigation pane, choose Event history, and then choose Create Athena table.

3.    In the Create a table in Amazon Athena window, open the Storage location menu, and then choose the Amazon Simple Storage Service (Amazon S3) bucket with the CloudTrail log files.

Note: You can improve search performance for specific time frames in a single AWS region using partition projection for CloudTrail logs with Athena.

4.    Choose Create table.

5.    Choose Go to Athena.

Note: You might receive the error "Your account does not have sufficient permissions to create tables in Amazon Athena." If you do, then attach the AmazonAthenaFullAccess managed policy by following the instructions at adding AWS Identity and Access Management (IAM) policies (console).

You can use one or more of the following sample queries with your logs. 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.

Run the Athena query

1.    Open the Athena console, choose New query, and then choose the dialog box to clear the sample query.

2.    Enter your query and then choose Run Query.

Display all recorded AWS API activity for a specific access key

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

Identify any security group changes for your EC2 instance

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;

Display any console logins over the last 24 hours

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';

Display any failed console sign-in attempts over the last 24 hours

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';

Identify CloudTrail monthly cost increases

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 eventtime defined values are limited to a single month. To get results for multiple months, run additional queries.

Identify missing IAM permissions

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 failed API call, widen the query range similar to the following:

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

Querying AWS CloudTrail logs

Adding and removing IAM identity permissions

How can I troubleshoot IAM permission access denied or unauthorized errors?