Control: Name servers should answer authoritatively

powerpipe control run net_insights.control.dns_ns_authoritative

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

with domain_list as (
  select distinct domain from net_dns_record where domain in (select jsonb_array_elements_text(to_jsonb($1::text[]))) order by domain
),
domain_ns_records as (
  select domain, target from net_dns_record where domain in (select domain from domain_list order by domain) and type = 'NS' order by domain
),
ns_ips as (
  select domain, ip, target, host(ip) as ip_text from net_dns_record where domain in (select target from domain_ns_records) and type = 'A' order by domain
),
ns_with_authoritative_stats as (
  select
  domain_ns_records.domain,
  domain_ns_records.target,
  case
    when ns_ips.ip is null then false
    else (select count(*) from net_dns_record where domain = domain_ns_records.domain and dns_server = ns_ips.ip_text and type = 'SOA' group by domain) is not null 
  end as is_authoritative
from
  domain_ns_records
  left join ns_ips on domain_ns_records.target = ns_ips.domain and ns_ips.ip is not null
order by domain_ns_records.target
),
ns_non_authoritative as (
  select distinct domain from ns_with_authoritative_stats where not is_authoritative order by domain
)
select
  domain_list.domain as resource,
  case
    when ns_non_authoritative.domain is null then 'ok'
    else 'alarm'
  end as status,
  case
    when ns_non_authoritative.domain is null then domain_list.domain || ' name servers listed at parent server answer authoritatively.'
    else domain_list.domain || ' name servers do not answer authoritatively: [' || (select string_agg(target, ', ') from ns_with_authoritative_stats where domain = domain_list.domain and not is_authoritative) || '].'
  end as reason
from
  domain_list
  left join ns_non_authoritative on domain_list.domain = ns_non_authoritative.domain;