Export route53 records to CSV

dump_route53_records.md

Retrieve hosted zones with aws route53 list-hosted-zones then enter the zone Id below:

aws route53 list-resource-record-sets --hosted-zone-id "/hostedzone/xxxxxxxxxxx" | \
   jq -r '.ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords[]? | .Value), .AliasTarget.DNSName?]  | @tsv'

for csv
aws route53 list-resource-record-sets --hosted-zone-id "/hostedzone/xxxxxxxxxxx" | jq -r '.ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords[]? | .Value), .AliasTarget.DNSName?] | @csv'

Thanks, work as expected 👍

Really helpful thank you!

thanks!

aws route53 list-resource-record-sets --hosted-zone-id "/hostedzone/Z071111111111111" | jq -r '.ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords[]? | .Value), .AliasTarget.DNSName?] | @csv' > abc.csv

aws route53 list-resource-record-sets --hosted-zone-id "/hostedzone/Z071111111111111" --output json | jq -r '.ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords[]? | .Value), .AliasTarget.DNSName?]

For the copy and pasters out there :-)

Thanks for the command.

I was trying to get the output into a CSV file that was uniform and able to be uploaded to Notion. After tweaking, I came up with the following, which works well for me. It combines the values and any alias into one column and provides a header row so the output is readily importable as a CSV.

aws route53 list-resource-record-sets --hosted-zone-id "/hostedzone/Z071111111111111" --output json \
  | jq -r '["Name", "Type", "Value"], (.ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords | select(. != null) | map(.Value) | join(" ")), (.AliasTarget.DNSName? | select(. != null))]) | @csv'

I needed to get all domains and their A Alias record, using ChatGPT, here is how it went, and it worked nicely ~:)

write a ruby code to fetch AWS Route53 records and generate a CSV file that has two columns, domain name and A record value

alter the code so that there is a outer loop to get all hosted zone ids first

alter the code to get all records as that code will get only first 100 record

alter the code to include only record name and value incase of record type is A and Alias

I also managed to get the AWS CLI version after some prompt tweaking, basically it started as:

write the same code using aws cli tool

for hzid in $(aws route53 list-hosted-zones --profile mfa --output json | jq -r '.HostedZones[].Id'); do aws route53 list-resource-record-sets --profile mfa --hosted-zone-id "$hzid" --output json | jq -r '.ResourceRecordSets[] | select((.Type == "A" or .Type == "ALIAS") and (.AliasTarget == null or (.AliasTarget.DNSName | test("^((?!ecs).)*$")))) | [.Name, (.AliasTarget | .DNSName) // (.ResourceRecords[0] | .Value)] | @csv' ; done > route53_records.csv

now that we have the route53 details into a csv, and someone deleted the current one, I would like to write a shell script that will take the csv as input and create hosted zone and all of the resource record sets also

To print the CSV data with headers:

aws route53 list-resource-record-sets --hosted-zone-id "/hostedzone/ZRxxxxxxxxxxx" | jq -r '
    ["Name", "Type", "Value", "AliasTargetDNSName"],
    (.ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords[]? | .Value), .AliasTarget.DNSName?])
    | @csv
' | (echo "Name,Type,Value,AliasTargetDNSName" && cat)

this solution handles ALIAS records which are exported as A records but invalid to import anywhere but AWS

• also replaces extraordinarily high TTL values with 86400 which is the highest possible, and null TTL values with 300
• any other null values are exported as "unknown" for troubleshooting later
• exports as tab separated for BIND format that can be imported elsewhere

profile=example
hosted_zone="HOSTEDZONEID"

aws route53 list-resource-record-sets --profile $profile --hosted-zone-id $hosted_zone --output json | \
jq -r '.ResourceRecordSets[] | "\(.Name // "unknown")\t\((if .TTL > 86400 then 86400 else .TTL // 300 end))\tIN\t\((if .AliasTarget.DNSName then "CNAME" else .Type // "unknown" end))\t\(.ResourceRecords[]?.Value // .AliasTarget.DNSName // "unknown")"'

Thank you!

Note: I ran a few of these aws/jq commands in a Microsoft Windows shell and got errors like

'[.Name' is not recognized as an internal or external command, operable program or batch file.

Using double quotes makes it work.

list-resource-record-sets --hosted-zone-id "/hostedzone/ZXXXXXXXXXXXXX" | jq -r ".ResourceRecordSets[] | [.Name, .Type, (.ResourceRecords[]? | .Value), .AliasTarget.DNSName?] | @tsv" > abc.tsv