Deren shows you how to
use Amazon Athena to
search CloudTrail logs

Deren_SEA0818

I want to search through a large collection of AWS CloudTrail logs, but to avoid causing errors, I don't want to manually create Amazon Athena tables.

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 Run advanced queries in Amazon Athena.
  3. In the Create a table in Amazon Athena window, open the Storage location drop-down, and then choose the S3 bucket name that contains the CloudTrail log files.
  4. Choose Create table.
  5. Choose Go to Athena.

Note: If you receive the error "Your account does not have sufficient permissions to create tables in Amazon Athena," attach the AmazonAthenaFullAccess managed policy by following the instructions at Attaching IAM Policies (Console).

You can use one or more of the following sample queries with your logs, replacing your_athena_tablename with the name of your Athena table, and access_key_id with your 20-character access key, which 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';

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-07-05