Amazon CloudTrail is a service provided by AWS that helps audit and monitor your AWS account by recording user, role, and service level events. These events can be captured from the AWS Management Console, CLI, APIs, or SDKs, and can be used for audits, governance, and compliance purposes.

In this article, we will explore how to use Amazon Athena to query the Amazon CloudTrail logs and extract valuable information from them.

Setting up CloudTrail

To get started, you need to create a trail for your AWS account. This trail will store the CloudTrail events in an S3 bucket. Once the trail is created, you can configure Amazon Athena to query the logs using standard SQL queries.

Here are the steps to create a trail:

1. Sign in to the AWS Management Console
2. Go to the CloudTrail console
3. Click on “Create a trail”
4. Enter a trail name
5. By default, AWS will create an S3 bucket to store the events
6. Click on “Create trail”

Once the trail is created, you can view and filter the events in the CloudTrail console.

Creating an Amazon Athena Table

To query the CloudTrail logs using Amazon Athena, you need to create a table that maps to the S3 bucket where the logs are stored. Here are the steps to create an Athena table:

1. Go to the CloudTrail console
2. Click on the “Event history” tab
3. Click on “Create Athena table”
4. Choose the S3 bucket name from the drop-down
5. By default, the table name will be the same as the S3 bucket name
6. Click on “Create table”

Once the table is created, you can use the Athena service to query the CloudTrail logs using standard SQL queries.

Querying CloudTrail Data with Amazon Athena

Now that we have set up CloudTrail and created an Athena table, we can start querying the CloudTrail logs. Here are some example queries:

Query 1: Get All Records from the CloudTrail

SELECT * FROM cloudtrail_logs;

Query 2: Get Events for S3 Bucket Creation

SELECT eventname, sourceipaddress, requestparameters
FROM cloudtrail_logs
WHERE eventname = 'CreateBucket';

Query 3: Get Events for S3 Bucket Deletion

SELECT eventname, sourceipaddress, requestparameters
FROM cloudtrail_logs
WHERE eventname = 'DeleteBucket';

Query 4: Find All Users Who Signed into the Console

SELECT eventtime, useridentity.arn, awsregion
FROM cloudtrail_logs
WHERE eventname = 'ConsoleLogin';

These are just a few examples of the types of queries you can run on the CloudTrail logs using Amazon Athena. With the power of SQL, you can extract valuable insights and perform audits on your AWS account.

In conclusion, Amazon Athena provides a powerful and easy-to-use solution for querying and analyzing CloudTrail logs. By leveraging the capabilities of Athena, you can gain valuable insights into your AWS account and ensure compliance with governance and security requirements.

Article Last Updated: 2023-11-07