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;