turbot/net_insights
Loading controls...

Control: Local DNS name server list should match parent name server list

Description

It is recommended that the local NS list should match the parent NS list.

Usage

Run the control in your terminal:

powerpipe control run net_insights.control.dns_ns_local_matches_parent_ns_list

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run net_insights.control.dns_ns_local_matches_parent_ns_list --share

Steampipe Tables

Params

ArgsNameDefaultDescriptionVariable
$1domain_names
["github.com","microsoft.com"]
DNS domain names.

SQL

with domain_list as (
select
distinct domain,
substring(
domain
from
'^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)'
) as tld
from
net_dns_record
where
domain in (
select
jsonb_array_elements_text(to_jsonb($1 :: text [ ]))
)
order by
domain
),
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
),
ns_ips as (
select
domain,
type,
ip,
host(ip) as ip_text
from
net_dns_record
where
domain in (
select
target
from
domain_parent_server_ns_list
)
and type = 'A'
order by
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
)
select
domain_list.domain as resource,
case
when ns_with_different_ns_count.domain is null then 'ok'
else 'alarm'
end as status,
case
when ns_with_different_ns_count.domain is null then domain_list.domain || ' name server records returned by parent server match local list.'
else domain_list.domain || ' parent name server records do not match local records: [' || (
select
string_agg(target, ', ')
from
ns_with_name_server_record
where
parent_server_ns_record_count <> name_server_record_count
) || '].'
end as reason
from
domain_list
left join ns_with_different_ns_count on domain_list.domain = ns_with_different_ns_count.domain;