Control: All name servers listed at the parent server should respond

powerpipe control run net_insights.control.dns_ns_responded

powerpipe login
powerpipe control run net_insights.control.dns_ns_responded --share

with domain_ns_records as (
  select domain, target from net_dns_record where domain in (select jsonb_array_elements_text(to_jsonb($1::text[]))) and type = 'NS'
),
ns_ips as (
  select domain, ip from net_dns_record where domain in (select target from domain_ns_records) and type = 'A'
),
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 order by domain_ns_records.domain
),
ns_individual_count as (
  select
    d.domain,
    count(*)
  from
    net_dns_record as d
    inner join ns_with_ip as i on d.domain = i.domain and d.dns_server = i.ip_text
  where
    d.type = 'NS'
  group by d.domain
),
ns_count as (
  select domain, count(*) from domain_ns_records group by domain
)
select
  nc.domain as resource,
  case
    when nic.count = pow(nc.count, 2) then 'ok'
    else 'alarm'
  end as status,
  case
    when nic.count = pow(nc.count, 2) then nc.domain || ' name servers are responding.'
    else nc.domain || ' has at least one name server that failed to respond in a timely manner.'
  end as reason
from
  ns_count as nc,
  ns_individual_count as nic
where
  nc.domain = nic.domain
group by nc.domain, nic.count, nc.count;