Query: dns_ns_record

with domain_records as (
  select
    *
  from
    net_dns_record
  where
    domain = $1
    and type = 'NS'
  order by
    domain
),
ns_ips as (
  select
    domain,
    ip,
    type,
    target,
    host(ip) as ip_text
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_records
    )
    and type = 'A'
  order by
    domain
)
select
  domain_records.target as "Domain",
  ns_ips.ip as "IP Address",
  domain_records.ttl as "TTL",
  case
    when ns_ips.ip is null then 'Not Responding'
    when (
      select
        count(*)
      from
        net_dns_record
      where
        domain = $1
        and dns_server = ns_ips.ip_text
      group by
        domain
    ) is not null then 'Responding'
    else 'Not Responding'
  end as "Status",
  case
    when ns_ips.ip is null then false
    else (
      select
        count(*)
      from
        net_dns_record
      where
        domain = $1
        and dns_server = ns_ips.ip_text
        and type = 'SOA'
      group by
        domain
    ) is not null
  end as "Authoritative"
from
  domain_records
  left join ns_ips on domain_records.target = ns_ips.domain
  and ns_ips.type = 'A'
where
  domain_records.type = 'NS'
order by
  domain_records.target;