Query: dns_soa_report

with domain_list as (
  select
    distinct domain,
    substring(
      domain
      from
        '^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)'
    ) as tld
  from
    net_dns_record
  where
    domain = $1
  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'
),
domain_parent_server_ip as (
  select
    *
  from
    net_dns_record
  where
    domain in (
      select
        parent_server
      from
        domain_parent_server
    )
),
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 as name_server
  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'
),
domain_primary_ns as (
  select
    target
  from
    net_dns_record
  where
    domain = $1
    and type = 'SOA'
),
domain_ns_records as (
  select
    *
  from
    net_dns_record
  where
    domain in (
      select
        domain
      from
        domain_list
    )
    and type = 'NS'
  order by
    domain
),
ns_ips as (
  select
    *
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_ns_records
    )
  order by
    domain
),
ns_with_ip as (
  select
    domain_ns_records.domain,
    host(ns_ips.ip) as 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_with_ip as i on r.domain = i.domain
    and r.dns_server = i.ip_text
  where
    r.type = 'SOA'
)
select
  'Name servers have same SOA serial' as "Recommendation",
  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 '❌'
    else '✅'
  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 'At least one name server has different SOA serial.'
    else 'All name servers have same SOA serial.'
  end || ' 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 record there might be a problem with the transfer.' as "Result"
from
  domain_list as d
UNION
select
  'Primary name server listed at parent' as "Recommendation",
  case
    when (
      select
        count(*)
      from
        domain_parent_server_ns_list
      where
        name_server in (
          select
            *
          from
            domain_primary_ns
        )
    ) is null then '❌'
    else '✅'
  end as "Status",
  case
    when (
      select
        count(*)
      from
        domain_parent_server_ns_list
      where
        name_server in (
          select
            *
          from
            domain_primary_ns
        )
    ) is null then 'Primary name server not listed in parent.'
    else domain || ' primary name server ' || (
      select
        target
      from
        domain_primary_ns
    ) || ' listed at parent.'
  end || ' Primary name server is the name server declared in your SOA file and generally reads your records from zone files. It is responsible for distributing the data to secondary name servers. Unmatched NS records can cause delays when resolving domain records, as it tries to contact a name server that is either non-existent or non-authoritative.' as "Result"
from
  domain_list
UNION
select
  'SOA serial number should be between 1 and 4294967295' as "Recommendation",
  case
    when serial < 1
    or serial > 4294967295 then '❌'
    when not (
      select
        serial :: text ~ '^\d{4}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}\d{2}$'
    ) then 'ℹ️'
    else '✅'
  end as "Status",
  case
    when not (
      select
        serial :: text ~ '^\d{4}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}\d{2}$'
    ) then domain || ' SOA serial number ' || serial || ' doesn''t match recommended format (per RFC1912 2.2) YYYYMMDDnn.'
    else domain || ' SOA serial number is ' || serial || '.'
  end as "Result"
from
  net_dns_record
where
  domain = $1
  and type = 'SOA'
UNION
select
  'SOA refresh value should be between 20 minutes and 12 hours' as "Recommendation",
  case
    when refresh < 1200
    or refresh > 43200 then '❌'
    else '✅'
  end as "Status",
  'SOA Refresh interval is: ' || refresh || '. This value indicates how often a secondary will poll the primary server to see
      if the serial number for the zone has increased (so it knows
      to request a new copy of the data for the zone). As per RFC1912 section 2.2 value should be in between 20 mins to 2 hrs.' as "Result"
from
  net_dns_record
where
  domain = $1
  and type = 'SOA'
UNION
select
  'SOA retry value should be between 2 minutes and 2 hours' as "Recommendation",
  case
    when retry < 120
    or retry > 7200 then '❌'
    else '✅'
  end as "Status",
  'SOA Retry value is: ' || retry || '. If a secondary was unable to contact the primary at the
      last refresh, wait the retry value before trying again. Recommended value is 2 minutes to 2 hours.' as "Result"
from
  net_dns_record
where
  domain = $1
  and type = 'SOA'
UNION
select
  'SOA expire value should be between 2 weeks and 4 weeks' as "Recommendation",
  case
    when expire < 1209600
    or expire > 2419200 then '❌'
    else '✅'
  end as "Status",
  'SOA Expire value is: ' || expire || '. This value indicates how long a secondary will still treat its copy of the zone
      data as valid if it can''t contact the primary. As per RFC1912 section 2.2 value should be in between 2-4 weeks.' as "Result"
from
  net_dns_record
where
  domain = $1
  and type = 'SOA'
UNION
select
  'SOA minimum TTL value should be between 10 minutes to 24 hours' as "Recommendation",
  case
    when minimum < 600
    or minimum > 86400 then '❌'
    else '✅'
  end as "Status",
  'SOA Minimum TTL is: ' || minimum || '. This value was used to serve as a default TTL for records without a given TTL value and now is
    used for negative caching (indicates how long a resolver may cache the negative answer). RFC2308 recommends a value of 1-3 hours.' as "Result"
from
  net_dns_record
where
  domain = $1
  and type = 'SOA'