turbot/net_insights

GitHub

Query: dns_ns_report

Usage

steampipe query net_insights.query.dns_ns_report

Plugins & Tables

SQL

with domain_list as (
select
distinct domain,
substring(
domain
from
'^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)'
) as tld
from
net_dns_record
where
domain = $1
),
domain_parent_server as (
select
l.domain,
d.domain as tld,
d.target as parent_server
from
net_dns_record as d
inner join domain_list as l on d.domain = l.tld
where
d.type = 'SOA'
order by
l.domain
),
domain_parent_server_ip as (
select
*
from
net_dns_record
where
domain in (
select
parent_server
from
domain_parent_server
)
order by
domain
),
domain_parent_server_with_ip as (
select
domain_parent_server.domain,
host(domain_parent_server_ip.ip) as ip_text
from
domain_parent_server
inner join domain_parent_server_ip on domain_parent_server.parent_server = domain_parent_server_ip.domain
where
domain_parent_server_ip.type = 'A'
order by
domain_parent_server.domain
),
domain_parent_server_ns_list as (
select
net_dns_record.domain,
net_dns_record.target
from
net_dns_record
inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain
and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text
and net_dns_record.type = 'NS'
order by
net_dns_record.domain
),
parent_server_ns_count_by_domain as (
select
net_dns_record.domain,
count(net_dns_record.target)
from
net_dns_record
inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain
and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text
and net_dns_record.type = 'NS'
group by
net_dns_record.domain
order by
net_dns_record.domain
),
domain_ns_count as (
select
count(*)
from
net_dns_record
where
domain = $1
and type = 'NS'
group by
domain
),
domain_ns_records as (
select
*
from
net_dns_record
where
domain in ($1)
and type = 'NS'
),
ns_ips as (
select
domain,
type,
ip,
host(ip) as ip_text
from
net_dns_record
where
domain in (
select
target
from
domain_ns_records
)
and type = 'A'
),
ns_with_ip as (
select
domain_ns_records.domain,
host(ns_ips.ip) as ip_text
from
domain_ns_records
inner join ns_ips on domain_ns_records.target = ns_ips.domain
where
ns_ips.type = 'A'
order by
domain_ns_records.domain
),
ns_with_name_server_record as (
select
domain_parent_server_ns_list.domain,
domain_parent_server_ns_list.target,
(
select
count as parent_server_ns_record_count
from
parent_server_ns_count_by_domain
where
domain = domain_parent_server_ns_list.domain
),
(
select
count(*) as name_server_record_count
from
net_dns_record
where
domain = domain_parent_server_ns_list.domain
and dns_server = ns_ips.ip_text
and type = 'NS'
group by
domain
)
from
domain_parent_server_ns_list
left join ns_ips on domain_parent_server_ns_list.target = ns_ips.domain
where
ns_ips.ip is not null
order by
domain_parent_server_ns_list.domain
),
ns_with_different_ns_count as (
select
distinct domain
from
ns_with_name_server_record
where
parent_server_ns_record_count <> name_server_record_count
),
ns_record_with_ip as (
select
domain_ns_records.domain,
domain_ns_records.target,
ns_ips.ip,
(
ns_ips.ip << '10.0.0.0/8' :: inet
or ns_ips.ip << '100.64.0.0/10' :: inet
or ns_ips.ip << '172.16.0.0/12' :: inet
or ns_ips.ip << '192.0.0.0/24' :: inet
or ns_ips.ip << '192.168.0.0/16' :: inet
or ns_ips.ip << '198.18.0.0/15' :: inet
) as is_private
from
domain_ns_records
inner join ns_ips on domain_ns_records.target = ns_ips.domain
),
ns_record_with_private_ip as (
select
distinct domain
from
ns_record_with_ip
where
is_private
),
invalid_ns_count as (
select
domain,
count(*)
from
net_dns_record
where
domain in ($1)
and type = 'NS'
and not target ~ '^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}\.?$'
group by
domain
),
check_ips as (
select
distinct array_to_string(
array_remove(
string_to_array(text(ns_ips.ip), '.'),
split_part(text(ns_ips.ip), '.', 4)
),
'.'
),
domain_ns_records.domain as domain
from
domain_ns_records
inner join ns_ips on domain_ns_records.target = ns_ips.domain
where
ns_ips.type = 'A'
and domain_ns_records.type = 'NS'
),
dns_record_count as (
select
domain,
count(*)
from
net_dns_record
where
domain in (
select
domain
from
domain_list
)
group by
domain
),
dns_cname_count as (
select
domain,
count(*)
from
net_dns_record
where
domain in (
select
domain
from
domain_list
)
and type = 'CNAME'
group by
domain
),
count_stats as (
select
domain,
(
select
count
from
dns_record_count
where
domain = domain_list.domain
) as all_record_count,
(
select
count
from
dns_cname_count
where
domain = domain_list.domain
) as cname_record_count
from
domain_list
)
select
'Multiple name servers' as "Recommendation",
case
when count < 2 then '❌'
else '✅'
end as "Status",
count || ' NS record(s) found. As per RFC2182 section 5 domain record must have at least 3 name servers, and no more than 7.' as "Result"
from
domain_ns_count
UNION
select
'Name of name servers are valid' as "Recommendation",
case
when r.domain is null
or r.count = 0 then '✅'
else '❌'
end as "Status",
case
when r.domain is null
or r.count = 0 then 'Name servers have valid name format.'
else 'At least one name server with invalid name format.'
end || ' The names can contain only alphabetical characters (A-Z), numeric characters (0-9), the minus sign (-), and the period (.). Period characters are allowed only when they are used to delimit the components of domain style names.' as "Result"
from
domain_list as d
left join invalid_ns_count as r on d.domain = r.domain
UNION
select
'Missing name servers reported by parent' as "Recommendation",
case
when ns_with_different_ns_count.domain is null then '✅'
else '❌'
end as "Status",
case
when ns_with_different_ns_count.domain is null then 'NS records returned by parent server is same as the one reported by your name servers.'
else 'Name server records returned by parent server doesn''t match with your name servers [' || (
select
string_agg(target, ', ')
from
ns_with_name_server_record
where
parent_server_ns_record_count <> name_server_record_count
) || '].'
end || ' Unmatched NS records can cause delays when resolving domain records, as it tries to contact a name server that is either non-existent or non-authoritative.' as "Result"
from
domain_list
left join ns_with_different_ns_count on domain_list.domain = ns_with_different_ns_count.domain
UNION
select
'No CNAME record if an NS (or any other) record is present' as "Recommendation",
case
when all_record_count > 0
and (
cname_record_count is null
or cname_record_count < 1
) then '✅'
when cname_record_count > 0
and all_record_count = cname_record_count then '✅'
else '❌'
end as "Status",
case
when all_record_count > 0
and (
cname_record_count is null
or cname_record_count < 1
) then 'No CNAME record found.'
when cname_record_count > 0
and all_record_count = cname_record_count then 'CNAME record(s) [' || (
select
string_agg(target, ', ')
from
net_dns_record
where
domain = count_stats.domain
) || '].'
else domain || ' has CNAME record along with NS (or any other) record.'
end || ' A CNAME record is not allowed to coexist with any other data. This is often attempted by inexperienced administrators as an obvious way to allow your domain name to also be a host. However, DNS servers like BIND will see the CNAME and refuse to add any other resources for that name. Since no other records are allowed to coexist with a CNAME, the NS entries are ignored.' as "Result"
from
count_stats
UNION
select
'Different subnets' as "Recommendation",
case
when count(*) = 1 then '❌'
else '✅'
end as "Status",
case
when count(*) = 1 then 'Name servers are on the same subnet.'
else 'Name servers appear to be dispersed.'
end || ' As per RFC2182 section 3.1, it is recommended that the secondary servers must be placed at both topologically and geographically dispersed locations on the Internet, to minimize the likelihood of a single failure disabling all of them.' as "Result"
from
check_ips
group by
domain
UNION
select
'IPs of name servers are public' as "Recommendation",
case
when ns_record_with_private_ip.domain is null then '✅'
else '❌'
end as "Status",
case
when ns_record_with_private_ip.domain is null then 'All NS records appear to use public IPs.'
else domain_list.domain || ' has NS records using private IPs [' || (
select
host(ip)
from
ns_record_with_ip
where
domain = domain_list.domain
and is_private
) || '].'
end || ' For a server to be accessible on the public internet, it needs a public DNS record, and its IP address needs to be reachable on the internet.' as "Result"
from
domain_list
left join ns_record_with_private_ip on domain_list.domain = ns_record_with_private_ip.domain
UNION
select
'Different autonomous systems' as "Recommendation",
case
when count(*) = 1 then '❌'
else '✅'
end as "Status",
case
when count(*) = 1 then 'Name servers are in same location.'
else 'Name servers are located in different location.'
end || ' As per RFC2182 section 3.1, it is recommended that the secondary servers must be placed at both topologically and geographically dispersed locations on the Internet, to minimize the likelihood of a single failure disabling all of them.' as "Result"
from
check_ips
group by
domain

Params

ArgsNameDefaultDescriptionVariable
$1domain_name_input

    Dashboards

    The query is used in the dashboards: