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'