Last active
October 15, 2023 04:42
-
-
Save matwerber1/bb89d3cf2290d2bf8452b694dbceb119 to your computer and use it in GitHub Desktop.
Example SQL to search CloudTrail Logs stored in S3 using Amazon Athena for any changes to Route 53 DNS records#
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment