Finding Route53 logs with the help of CloudTrail and Athena

If your company uses AWS Route53 to manage DNS records of its domains, there might a situation where you want to find which IAM user created / modified which DNS record.

The reason for this could be anything: asset management, to find root cause of a security incident (like subdomain takeover) or simply to cleanup unused DNS records.

This could be done with ease if you have CloudTrail logging enabled (atleast for us-east-1 region) and stored on an S3 bucket. If you don’t have it enabled during the time of DNS management, this blog post will not be of much help to you.

Before we move on, let’s know what is AWS Athena.

What is AWS Athena ?

Athena is a query service by AWS, which allows you to query data stored in S3 bucket using standard SQL. You can read more about AWS Athena here.

Cloudtrail logs all the API calls made to AWS services and stores them on S3 buckets. We can utilize Athena service to query Cloudtrail logs to get required Route53 logs.

Creating Athena table from CloudTrail logs

Go to CloudTrail dashboard, click on “Event History” and then click “Run advanced queries in Amazon Athena”

Select the S3 bucket which is used to store the CloudTrail logs. Click on “Create Table”.

By default, the name of the S3 bucket which stores the Cloudtrail logs is appended to the Athena table name. For example, if my S3 bucket name is badshah, then my default Athena table name is cloudtrail_logs_badshah. You can change the name of the table before it’s creation (it can’t be changed after creation).

Before executing the queries, please remember:

• I have used cloudtrail_logs_badshah as my table name for all the following queries. Replace it with your table name.
• The query output includes only username, account ID, creation date, domain name, record type and the resource records. If you want to get all columns, you can use SELECT * FROM cloudtrail_logs_badshah.

Athena query to get Route53 logs for DNS record creation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT useridentity.username,
       useridentity.accountid,
       useridentity.sessioncontext.attributes.creationdate,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.name') AS domainName,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.type') AS recordType,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.resourceRecords') AS resourceRecords
FROM cloudtrail_logs_badshah
WHERE (eventsource = 'route53.amazonaws.com'
      AND eventname = 'ChangeResourceRecordSets'
      AND json_extract_scalar(requestparameters,'$.changeBatch.changes[0].action') = 'CREATE')

A successful execution will give output like the below:

Athena query to get all Route53 logs

If you want to get all the actions of DNS records, then use the following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT useridentity.username,
       useridentity.accountid,
       useridentity.sessioncontext.attributes.creationdate,
       json_extract(requestparameters,'$.changeBatch.changes[0].action') AS action,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.name') AS domainName,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.type') AS recordType,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.resourceRecords') AS resourceRecords
FROM cloudtrail_logs_badshah
WHERE (eventsource = 'route53.amazonaws.com'
       AND eventname = 'ChangeResourceRecordSets')

A successful execution will give output like the below:

Athena query to get all Route53 logs for a particular DNS record

Make sure you change the Athena table name cloudtrail_logs_badshah and the DNS record sub.your-domain.com.. Don’t forget the . at the end of the DNS record.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT useridentity.username,
       useridentity.accountid,
       useridentity.sessioncontext.attributes.creationdate,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.name') AS domainName,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.type') AS recordType,
       json_extract(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.resourceRecords') AS resourceRecords
FROM cloudtrail_logs_badshah
WHERE (eventsource = 'route53.amazonaws.com'
      AND eventname = 'ChangeResourceRecordSets'
      AND json_extract_scalar(requestparameters,'$.changeBatch.changes[0].resourceRecordSet.name') = 'sub.your-domain.com.')

A successful execution will give output like the below: