Query Amazon CloudTrail Logs using Amazon Athena for AWS Account Auditing

By:   |   Updated: 2023-11-07   |   Comments   |   Related: > Amazon AWS


Previous tips have explored how Amazon Athena can help interact with S3 bucket data using standard SQL queries. Various Amazon services generate the logs in text or JSON format. Similarly, you need to go through the text-based records to audit the AWS account. This tip covers using Amazon Athena to query the Amazon CloudTrail logs for auditing your AWS account.


Amazon CloudTrail service helps to audit the AWS account by recording the user, role, or service level events. It captures the events from the AWS management console, command line interface (CLI), APIs, or AWS SDKs. These events can help perform audits, governance, and compliance. AWS automatically enables the CloudTrain once the AWS account is created.

The AWS CloudTrail records events in the following ways:

  1. Event History: Users can view the past 90 days of recorded management events in the AWS region using the Event History. To view the event history, navigate to the CloudTrail Console and Event History page. You can search for the specific event as well. AWS does not charge for viewing events from the Event history.
  2. CloudTrail Lake: CloudTrail Lake is useful for audit and security purposes. It is a managed data lake that captures and converts the events in a columnar format for faster retrieval and analysis. This columnar storage format is Apache ORC standard. You can view data for up to seven years using the CloudTrail lake. This service has a fee, go to AWS CloudTrail pricing for more details.
  3. Trails: S3 storage can be used to store AWS events. Optionally, you can also deliver these events to Amazon CloudWatch and EventBridge. The benefit of trails is that you can configure Amazon Athena and query the logs using the standard SQLs. Delivery of one ongoing event to the AWS S3 bucket is free. You need to pay for the S3 bucket standard storage charges.

This article will configure the trails to store events into the S3 bucket and query using Amazon Athena.

Create a Trail for the AWS Account

Once you sign in to the AWS Management Console, go to Services and launch the CloudTrail console.


In the AWS CloudTrail console, click Create a trail.

Create a trail

In the trails configuration page, enter the trail name. By default, AWS creates the S3 bucket that will store the events. As shown below, it creates the S3 bucket and folder aws-cloudtrail-logs-147081669821-91ab9629 for this demo instance.

Trail configuration

We can use advanced event selectors for creating a trail with custom events and an S3 bucket. Click on Create trail to use the advanced event selectors.

Create a trail with an auto-created S3 bucket and default logging management events. As shown below, there is a trail "management-events." Trails created from the management console use the multi-region trails.

Advanced trail event selectors

Click on the S3 bucket for a CloudTrail folder that records all audit events.

CloudTrail folder

It will create subfolders inside the CloudTrail for the region and log date. For example, for this demo, you can see folders Amazon S3 > Buckets > aws-cloudtrail-logs-147081669821-91ab9629 > AWSLogs/ > 147081669821/ > CloudTrail/ > us-east-1/ > 2023/ > 09/ > 26/

The logs are uploaded in gz compression format, as shown below.

Uploaded logs

Create an Amazon Athena Table for CloudTrail

Click on the Event history tab in the CloudTrail console to view and filter all the events.

CloudTrail Event History

On the Event history page, click the Create Athena table option. It gives you a query to create the Athena table.

Create a table in Amazon Athena

You can choose the S3 bucket name from the drop-down. By default, the table name is the same as your S3 bucket name. For example, my S3 bucket and Athena table name is cloudtrail_logs_aws_cloudtrail_logs_147081669821_91ab9629.

Storage location

Click Create table, and it creates the table in Amazon Athena.


You can view this table by navigating to the Athena service or clicking the table name, redirecting you to launch the query editor.

Launch query editor

Athena allows you to query your data using Trino SQL or PySpark and Spark SQL.   For this demo, choose Query your data with Trino SQL and click Launch query editor.

As shown below, it creates the table in the default database.

table location

Query CloudTrail Data Using Amazon Athena Query

Before we run queries in Amazon Athena, let's perform the following task to generate some events.

Task 1

Create a S3 bucket named mssqltipsdemoathena-cloudtrail.

Bucket configuration

Task 2

Create an IAM User - mssqltips and provide user access to the AWS management console.

User details

Task 3

Once the user is created, sign in using IAM credentials.

Sign in

Task 4

After signing in from IAM credentials, delete the S3 bucket named mymssqlbucket.

Delete bucket

Let's query the Amazon Athena table that retrieves data from the Amazon CloudTrail.

Query 1: Get All Records from the CloudTrail

You can use the query below to get all records from the CloudTrail:

Select * from cloudtrail_logs_aws_cloudtrail_logs_147081669821_91ab9629

However, be careful while retrieving all records from CloudTrail, as you might need to pay a higher price for running an Amazon Athena query depending upon the data scanned.

Query 2: Get Events for S3 Bucket Creation

In this query, we want to see all events where an S3 bucket was created. You can get the source IP address requested parameters for the S3 bucket, such as the bucket name region.

Select eventname, sourceipaddress, requestparameters
from cloudtrail_logs_aws_cloudtrail_logs_147081669821_91ab9629 
where eventName = 'CreateBucket';

As you can see, we have five records of the CreateBucket event in the S3 bucket. Before running the query, we created a bucket -mssqltipsdemoathena-cloudtrail. You can see the entry for that bucket as row number 2.

All events query results

Query 3: Get Events for S3 Bucket Deletion

Suppose someone deleted the S3 bucket in your AWS account. You can use CloudTrail and Athena to query the deletebucket events and get all relevant records.

Select eventname, sourceipaddress, requestparameters
from cloudtrail_logs_aws_cloudtrail_logs_147081669821_91ab9629 
where eventName = 'DeleteBucket';

You can see two delete bucket events in my CloudTrail for mymssqlbucket and mssqldemoathena-cloudtrail.

Events for S3 bucket deletion query results

Query 4: Find All Users Who Signed into the Console

Suppose you want a list of users who have signed into the AWS Web Console. For this purpose, you can filter events for the event named ConsoleLogin.

SELECT eventTime, useridentity.arn, awsRegion 
FROM cloudtrail_logs_aws_cloudtrail_logs_147081669821_91ab9629 
where eventName = 'ConsoleLogin';
All users who signed in to the console query results
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-11-07

Comments For This Article

get free sql tips
agree to terms