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;