Amazon Athena
User Guide  | API Reference

Querying AWS CloudTrail Logs

AWS CloudTrail logs include details about any API calls made to your AWS services, including the console.

To collect logs and save them to Amazon S3, enable CloudTrail for the console. For more information, see Creating a Trail in the AWS CloudTrail User Guide.

Note the destination Amazon S3 bucket where you save the logs, as you need it in the next section. For more information about specifying the location of your logs, see CloudTrail.

Creating the Table for CloudTrail Logs#

To create the CloudTrail table#

  1. Copy and paste the following DDL statement into the AWS Management Console for Athena.

  2. Modify the s3://cloudtrail_bucket_name/AWSLogs/bucket_ID/ to point to the S3 bucket that contains your logs data.

    CREATE EXTERNAL TABLE cloudtrail_logs (
     eventversion STRING,
     userIdentity STRUCT<
        type:STRING,
        principalid:STRING,
        arn:STRING,
        accountid:STRING,
        invokedby:STRING,
        accesskeyid:STRING,
        userName:String,
        sessioncontext:STRUCT<
          attributes:STRUCT<
            mfaauthenticated:STRING,
            creationdate:STRING>,
          sessionIssuer:STRUCT<
            type:STRING,
            principalId:STRING,
            arn:STRING,
            accountId:STRING,
            userName:STRING>>>,
       eventTime STRING,
       eventSource STRING,
       eventName STRING,
       awsRegion STRING,
       sourceIpAddress STRING,
       userAgent STRING,
       errorCode STRING,
       errorMessage STRING,
       requestId  STRING,
       eventId  STRING,
       resources ARRAY<STRUCT<
       ARN:STRING,
       accountId:STRING,
       type:STRING>>,
       eventType STRING,
       apiVersion  STRING,
       readOnly BOOLEAN,
       recipientAccountId STRING,
       sharedEventID STRING,
       vpcEndpointId STRING
    )
    ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
    STORED  AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION  's3://cloudtrail_bucket_name/AWSLogs/bucket_ID/'
    
  3. Run the query in AWS Management Console for Athena. After the query completes, Athena registers cloudtrail_logs, making the data in it ready for you to issue queries.

Tips for Querying CloudTrail Logs#

To explore the CloudTrail logs data, use these tips:

  • Before querying the logs, verify that your logs table looks the same as the one in Creating the Table for CloudTrail Logs. If it isn’t the first table, delete the existing table: DROP  TABLE cloudtrail_logs;.

  • Recreate the table. For more information, see Creating the Table for CloudTrail Logs.

  • Start by looking at which IAM users called which API operations and from which source IP addresses.

  • Use the following basic SQL query as your template. Paste the query to the AWS Management Console for Athena and run it.

    SELECT
     useridentity.arn,
     eventname,
     sourceipaddress,
     eventtime
    FROM cloudtrail_logs
    LIMIT 100;
    
  • Modify this query further to explore your data.

  • To improve performance and prevent long-running queries, include the LIMIT clause to return a specified subset of rows.

For more information, see the AWS Big Data blog post Analyze Security, Compliance, and Operational Activity Using AWS CloudTrail and Amazon Athena.