steampipe plugin install aws

Table: aws_route53_record - Query AWS Route 53 Record using SQL

The AWS Route 53 Record is a component of Amazon's scalable and highly available Domain Name System (DNS) web service. It is designed to provide reliable and cost-effective domain registration, DNS routing, and health checking of resources within your environment. It translates domain names into the numeric IP addresses that computers use to connect to each other, thus facilitating the routing of internet traffic to your applications and services.

Table Usage Guide

The aws_route53_record table in Steampipe provides you with information about DNS records within AWS Route 53. This table allows you, as a DevOps engineer, to query record-specific details, including type, name, TTL (Time to Live), and associated metadata. You can utilize this table to gather insights on DNS records, such as record types, verification of TTL values, and more. The schema outlines the various attributes of the DNS record for you, including the record name, type, set identifier, TTL, and associated resource records.

Important Notes

  • We recommend specifying the name and type columns when querying zones with a large number of records to reduce the query time.

Examples

Basic info

Explore which type of records are associated with your Route 53 DNS entries to gain insights into your AWS environment's DNS configuration. This can help in identifying potential misconfigurations or understanding the distribution of different record types.

select
name,
type,
records,
alias_target
from
aws_route53_record;
select
name,
type,
records,
alias_target
from
aws_route53_record;

List all test.com records in a zone

Determine the areas in which specific 'test.com' records exist within a zone, enabling better management and organization of your domain records.

select
r.name,
r.type,
record
from
aws_route53_record as r,
jsonb_array_elements_text(records) as record
where
name = 'test.com.';
select
r.name,
r.type,
json_extract(record.value, '$') as record
from
aws_route53_record as r,
json_each(records) as record
where
name = 'test.com.';

List all NS records in a zone

Identify instances where you need to analyze all the NS records within a specific zone. This can be particularly useful when managing DNS configurations and ensuring accurate routing of internet traffic.

select
r.name,
r.type,
record
from
aws_route53_record as r,
jsonb_array_elements_text(records) as record
where
r.type = 'NS';
select
r.name,
r.type,
json_extract(record.value, '$') as record
from
aws_route53_record as r,
json_each(r.records) as record
where
r.type = 'NS';

Get test.com NS record in a zone

Determine the specific Name Server (NS) record associated with 'test.com' in a DNS zone. This is useful for verifying correct DNS configuration or troubleshooting DNS issues.

select
r.name,
r.type,
record
from
aws_route53_record as r,
jsonb_array_elements_text(records) as record
where
r.name = 'test.com.'
and r.type = 'NS';
select
r.name,
r.type,
json_extract(record.value, '$') as record
from
aws_route53_record as r,
json_each(r.records) as record
where
r.name = 'test.com.'
and r.type = 'NS';

Count records by type

Analyze the distribution of different record types in your AWS Route53 configuration to understand which types are most commonly used. This information can be useful for optimizing DNS setup and identifying potential areas for improvement.

select
type,
count(*)
from
aws_route53_record
group by
type
order by
count desc;
select
type,
count(*)
from
aws_route53_record
group by
type
order by
count(*) desc;

List geo-location routing information

Explore geo-location routing information to gain insights into the distribution of your web traffic. This can help optimize your network strategy by identifying which continents and countries are accessing your resources the most.

select
name,
type,
records,
alias_target,
geo_location ->> 'ContinentCode' as continent,
geo_location ->> 'CountryCode' as country,
geo_location ->> 'SubdivisionCode' as subdivision
from
aws_route53_record
where
geo_location is not null
order by
name;
select
name,
type,
records,
alias_target,
json_extract(geo_location, '$.ContinentCode') as continent,
json_extract(geo_location, '$.CountryCode') as country,
json_extract(geo_location, '$.SubdivisionCode') as subdivision
from
aws_route53_record
where
geo_location is not null
order by
name;

Count of records by name and type

Determine the frequency of different record types within your AWS Route53 service. This can help in understanding the distribution and usage patterns of various record types, aiding in effective DNS management.

select
name,
type,
count(*)
from
aws_route53_record
left join jsonb_array_elements_text(records) as record on true
group by
name,
type;
select
name,
type,
count(*)
from
aws_route53_record
left join (
select
value as record
from
aws_route53_record,
json_each(records)
)
group by
name,
type;

Schema for aws_route53_record

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
alias_targetjsonbAlias resource record sets only: Information about the AWS resource, such as a CloudFront distribution or an Amazon S3 bucket, that you want to route traffic to.
cidr_routing_configjsonbThe object that is specified in resource record set object when you are linking a resource record set to a CIDR location.
failovertextFailover resource record sets only: To configure failover, you add the Failover element to two resource record sets. For one resource record set, you specify PRIMARY as the value for Failover; for the other resource record set, you specify SECONDARY. In addition, you include the HealthCheckId element and specify the health check that you want Amazon Route 53 to perform for each resource record set.
geo_locationjsonbGeolocation resource record sets only: A complex type that lets you control how Amazon Route 53 responds to DNS queries based on the geographic origin of the query. For example, if you want all queries from Africa to be routed to a web server with an IP address of 192.0.2.111, create a resource record set with a Type of A and a ContinentCode of AF.
geo_proximity_locationjsonbGeoproximityLocation resource record sets only: A complex type that lets you control how Route 53 responds to DNS queries based on the geographic origin of the query and your resources.
health_check_idtextThe health check the record should be associated with.
latency_regiontextAn AWS region from which to measure latency
multi_value_answerbooleanMultivalue answer resource record sets only: To route traffic approximately randomly to multiple resources, such as web servers, create one multivalue answer record for each resource and specify true for MultiValueAnswer.
nametext=The name of the record.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
recordsjsonbIf the health check or hosted zone was created by another service, an optional description that can be provided by the other service.
regiontextThe AWS Region in which the resource is located.
resource_recordsjsonbInformation about the resource records to act upon. If you're creating an alias resource record set, omit ResourceRecords.
set_identifiertext=Unique identifier to differentiate records with routing policies from one another.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
titletextTitle of the resource.
traffic_policy_instance_idtextThe ID of the traffic policy instance that Route 53 created this resource record set for.
ttltextThe resource record cache time to live (TTL), in seconds.
typetext=The record type. Valid values are A, AAAA, CAA, CNAME, MX, NAPTR, NS, PTR, SOA, SPF, SRV and TXT.
weightbigintWeighted resource record sets only: Among resource record sets that have the same combination of DNS name and type, a value that determines the proportion of DNS queries that Amazon Route 53 responds to using the current resource record set. Route 53 calculates the sum of the weights for the resource record sets that have the same combination of DNS name and type. Route 53 then responds to queries based on the ratio of a resource's weight to the total.
zone_idtext=The ID of the hosted zone to contain this record.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_route53_record