turbot/net_insights

Control: All name server records should have same SOA serial

Description

Sometimes serial numbers become out of sync when any record within a zone got updated and the changes are transferred from primary name server to other name servers. If the SOA serial number is not same for all NS records there might be a problem with the transfer.

Usage

Run the control in your terminal:

powerpipe control run net_insights.control.dns_soa_ns_same_serial

Snapshot and share results via Turbot Pipes:

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

Steampipe Tables

Params

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

SQL

with domain_list as (
select distinct domain from net_dns_record where domain in (select jsonb_array_elements_text(to_jsonb($1::text[])))
),
domain_ns_records as (
select domain, target from net_dns_record where domain in (select domain from domain_list) and type = 'NS' order by domain
),
ns_ips as (
select domain, type, target, host(ip) as ip_text from net_dns_record where domain in (select target from domain_ns_records) and type = 'A' order by domain
),
ns_records_with_ips as (
select
domain_ns_records.domain,
ns_ips.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
),
unique_serial as (
select
distinct r.serial,
r.domain
from
net_dns_record as r
inner join ns_records_with_ips as i on r.domain = i.domain and r.dns_server = i.ip_text
where
r.type = 'SOA'
)
select
d.domain as resource,
case
when (select count(*) from unique_serial where domain = d.domain) is null or (select count(*) from unique_serial where domain = d.domain) > 1 then 'alarm'
else 'ok'
end as status,
case
when (select count(*) from unique_serial where domain = d.domain) is null or (select count(*) from unique_serial where domain = d.domain) > 1
then d.domain || ' has at least 1 name server with different SOA serial.'
else d.domain || ' name servers have same SOA serial.'
end as reason
from
domain_list as d;