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;