How to: Query Cloudtrail Logs

AWS CloudTrail Console, AWS Athena, AWS Glue and AWS QuickSight can all be used to query CloudTrail logs. In this article, we will discuss how to use each of these services to query CloudTrail logs.

Using AWS Cloudtrail Console

The AWS CloudTrail console is the easiest way to query your CloudTrail logs without having to use other services. This console provides a visual interface that allows you to view and search CloudTrail events. It also provides a comprehensive list of CloudTrail log events and allows you to filter the log results using various parameters such as date range and event type.

Step 1: Log in to the AWS Console.

The first step to using the CloudTrail console is to log in to the AWS Console. You can do this by navigating to the AWS Management Console and entering your AWS credentials.

Step 2: Select your CloudTrail Trails.

Once you’ve logged in to the AWS Console, you’ll need to select the CloudTrail trails that you want to query logs from. You can access the CloudTrail console by clicking the “Services” drop-down menu and selecting CloudTrail.

Step 3: Select the Logs You Want to Query.

Once you are in the CloudTrail console, you can select the logs that you want to query by using the filters on the left-hand side of the screen. You can filter by date, AWS service, resource type, and more.

Step 4: Select the Run Query Button.

Once you have selected the logs you want to query, you can click the “Run Query” button to begin querying your logs.

Step 5: View Your Logs.

Once you’ve run the query, you can view the results of your query in the log viewer. Here, you can view the details of the activity that took place in your AWS account.

Step 6: Export Your Logs.

If you want to save the results of your query for future reference, you can click the “Export” button to export the query results to a CSV or JSON file.

Using AWS Athena

Step 1: Log into the AWS Management Console, and open the Athena console

Step 2: Click the “Create Table” button

Step 3: Select your CloudTrail log S3 bucket

Step 4: Enter a table name

Step 5: Enter the following query to create the table:

CREATE EXTERNAL TABLE `<your_table_name>`(
  `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,
  `requestparameters` string,
  `responseelements` string,
  `additionaleventdata` string,
  `requestid` string,
  `eventid` string,
  `resources` array<struct<
    arn: string,
    accountid: string,
    type: string>>,
  `eventtype` string,
  `apiversion` string,
  `readonly` string,
  `recipientaccountid` 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://<your_bucket_name>/'

Step 6: Click “Create Table”

Step 7: Running queries

Once the table is created, you can run queries against it using Athena. Here are some sample queries to get you started:

List all API calls made to S3:

SELECT *

FROM <your_table_name>

WHERE eventsource = ‘s3.amazonaws.com’

List all API calls made to EC2:

SELECT *

FROM <your_table_name>

WHERE eventsource = ‘ec2.amazonaws.com’

List all API calls made to an specific resource:

SELECT *

FROM <your_table_name>

WHERE resources[0].arn = ‘<arn_of_the_resource>’

List all API calls made by a specific user:

SELECT *

FROM <your_table_name>

WHERE useridentity.userName = ‘<name_of_the_user>’

Using AWS Glue

AWS Glue is a fully managed ETL (extract-transform-load) service that can be used to transform, clean and move CloudTrail logs from one place to another. Glue can also be used to write custom scripts that can be used to query and analyze CloudTrail logs.

Step 1: Create a Glue Crawler

First, you will need to create a Glue Crawler that is able to detect the CloudTrail data in your AWS environment. To do this, open the AWS Glue console and click on “Crawlers”. Next, click on the “Add crawler” button. Give your crawler a name and select the type of data store you want it to crawl (in this case, select “S3”). 

Then, enter the S3 bucket path of the CloudTrail log files you want to query (e.g. s3://cloudtrail-logs). Select the data store type as “JSON” and the output data format as “Parquet”. Finally, click “Next” and then “Finish” to create the crawler.

Step 2: Create a Glue Database

Once the crawler is created, you will need to create a Glue database to store the results of your queries. To do this, open the AWS Glue console and click on “Databases”. Then, click on the “Add database” button and give it a name (e.g. “CloudTrail_DB”).

Step 3: Create a Glue Table

Next, you will need to create a Glue Table in the Glue Database. To do this, navigate to the “Tables” section of the Glue console and click on the “Add table” button. Give the table a name (e.g. “CloudTrail_Table”) and select the Glue Database you created in the previous step.

Then, select the S3 bucket path you used for the crawler in Step 1 and select the file format as “JSON”. Finally, click “Create” to create the Glue Table.

Step 4: Query CloudTrail using AWS Glue

Once the Glue Table is created, you are now ready to query CloudTrail using AWS Glue. To do this, open the AWS Glue console and click on the “Query editor” tab. 

Then, enter the following sample query to get all the events that occurred in the past 24 hours:

SELECT * FROM CloudTrail_Table
WHERE eventTime > (CURRENT_TIMESTAMP - INTERVAL '24 HOURS');
You can also query CloudTrail to get all the events from a specific user by entering the following query:
SELECT * FROM CloudTrail_Table
WHERE userName = '<USERNAME>';

Using AWS Quicksight

AWS QuickSight is an interactive data visualization tool that can be used to visualize and analyze CloudTrail log events. QuickSight provides a simple drag-and-drop interface that can be used to quickly create visualizations of CloudTrail logs. QuickSight also provides an array of powerful data analysis and transformation options that can be used to gain deeper insights into CloudTrail log events.

Step 1: Log in to the AWS Console and go to the QuickSight home page.

Step 2: Create a new data set by clicking the “New Data Set” button.

Step 3: Select “Amazon CloudTrail” as the data source type.

Step 4: Select the CloudTrail log files you want to query.

Step 5: Click “Create Data Set” and wait for the data set to be created.

Step 6: Click “Create Analysis” to create a new analysis.

Step 7: Select the data set you just created and click “Create Analysis”.

Step 8: Select the type of query you want to run. You can choose from a basic query, an advanced query, or a custom query.

Step 9: Enter your query in the query editor. Here are some sample queries you can use:

• To find all API calls for a specific user: SELECT * FROM cloudtrail_log WHERE userIdentity.userName = ‘<userName>’

• To list all API calls from a specific IP address: SELECT * FROM cloudtrail_log WHERE sourceIPAddress = ‘<ipAddress>’

• To list all API calls within a given date range: SELECT * FROM cloudtrail_log WHERE eventTime BETWEEN ‘<startDate>’ AND ‘<endDate>’\

Step 10: Click “Run Query” to execute the query.

Step 11: The results of the query will be displayed in a table.