Example SQL to search CloudTrail Logs stored in S3 using Amazon Athena for any changes to Route 53 DNS records#

athena-query-for-cloudtrail-route53-dns-changes.sql

-- Query assumes your Athena table is partitioned with a "day" column as described here: 
-- https://www.linkedin.com/pulse/using-athena-partition-projection-query-cloudtrail-other-kinsman/

with detail AS (
SELECT
  day,
  eventtime as timestamp,
  account,
  awsregion as region,
  eventid,
  eventtype,
  eventsource,
  eventname,
  readonly,
  errorcode,
  errormessage,
  useridentity.type as userType,
  
  -- Depending on the userType, this value may be blank and you may need 
  -- to use a different field within useridentity to get the proper principal
  -- (or better yet, coalesce() the field(s) that might contain the identity)
  useridentity.arn as userArn,
  json_extract(requestparameters, '$.changeBatch.changes') AS dnsRecordChanges,
  requestparameters
FROM cloudtrail_logs
WHERE 
  -- If you've partitioned your table as shown in the link above, then filtering by
  -- 'day', 'account', and 'region' will dramatically improve query speed and reduce data scanned. 
  day >= DATE_FORMAT(NOW() - INTERVAL '45' day, '%Y/%m/%d')
  AND account = '999999999999'
  AND region = 'us-east-1'
  AND eventsource = 'route53.amazonaws.com'
  AND eventname = 'ChangeResourceRecordSets'
  AND readonly = 'false'
) 
SELECT 
  timestamp,
  account,
  region,
  eventsource,
  eventname,
  errorcode,
  regexp_replace(userArn, '[^\/]*\/', '') as user,
  eventid as dnsEvent,
  dnsEventLineItem,
  json_extract_scalar(dnsRecordChange, '$.action') as dnsRecordAction,
  json_extract_scalar(dnsRecordChange, '$.resourceRecordSet.name') as dnsRecordName,
  json_extract_scalar(dnsRecordChange, '$.resourceRecordSet.type') as dnsRecordType,
  json_extract_scalar(dnsRecordChange, '$.resourceRecordSet.tTL') as dnsRecordTTL,
  json_extract(dnsRecordChange, '$.resourceRecordSet.resourceRecords') as dnsRecordValue,
  json_extract_scalar(dnsRecordChange, '$.resourceRecordSet.aliasTarget.dNSName') as dnsRecordAlias
  --dnsRecordChange
FROM detail
CROSS JOIN UNNEST(CAST(dnsRecordChanges AS ARRAY(json))) WITH ORDINALITY AS t(dnsRecordChange, dnsEventLineItem)

-- You can optionally filter to look for changes to specific record(s), as shown below:
WHERE json_extract_scalar(dnsRecordChange, '$.resourceRecordSet.name') LIKE '%SOME_DNS_RECORD.COM%'
ORDER BY timestamp DESC, dnsEventLineItem DESC