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

net_dns_record

Params

Args	Name	Default	Description	Variable
$1	domain_names	`["github.com","microsoft.com"]`	DNS domain names.	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;