turbot/net_insights

GitHub
Loading controls...

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:

steampipe check net_insights.control.dns_soa_ns_same_serial

Snapshot and share results via Steampipe Cloud:

steampipe login
steampipe check --share net_insights.control.dns_soa_ns_same_serial

Plugins & 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;