Control: Local DNS name server list should match parent name server list

powerpipe control run net_insights.control.dns_ns_local_matches_parent_ns_list

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

with domain_list as (
  select distinct domain, substring( domain from '^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)' ) as tld from net_dns_record where domain in (select jsonb_array_elements_text(to_jsonb($1::text[]))) order by domain
),
domain_parent_server as (
  select l.domain, d.domain as tld, d.target as parent_server from net_dns_record as d inner join domain_list as l on d.domain = l.tld where d.type = 'SOA' order by l.domain
),
domain_parent_server_ip as (
  select * from net_dns_record where domain in (select parent_server from domain_parent_server) order by domain
),
domain_parent_server_with_ip as (
  select domain_parent_server.domain, host(domain_parent_server_ip.ip) as ip_text from domain_parent_server inner join domain_parent_server_ip on domain_parent_server.parent_server = domain_parent_server_ip.domain where domain_parent_server_ip.type = 'A' order by domain_parent_server.domain
),
domain_parent_server_ns_list as (
  select net_dns_record.domain, net_dns_record.target from net_dns_record inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text and net_dns_record.type = 'NS' order by net_dns_record.domain
),
parent_server_ns_count_by_domain as (
  select net_dns_record.domain, count(net_dns_record.target) from net_dns_record inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text and net_dns_record.type = 'NS' group by net_dns_record.domain order by net_dns_record.domain
),
ns_ips as (
  select domain, type, ip, host(ip) as ip_text from net_dns_record where domain in (select target from domain_parent_server_ns_list) and type = 'A' order by domain
),
ns_with_name_server_record as (
  select
    domain_parent_server_ns_list.domain,
    domain_parent_server_ns_list.target,
    (select count as parent_server_ns_record_count from parent_server_ns_count_by_domain where domain = domain_parent_server_ns_list.domain),
    (select count(*) as name_server_record_count from net_dns_record where domain = domain_parent_server_ns_list.domain and dns_server = ns_ips.ip_text and type = 'NS' group by domain)
  from
    domain_parent_server_ns_list
    left join ns_ips on domain_parent_server_ns_list.target = ns_ips.domain
  where
    ns_ips.ip is not null
  order by domain_parent_server_ns_list.domain
),
ns_with_different_ns_count as (
  select distinct domain from ns_with_name_server_record where parent_server_ns_record_count <> name_server_record_count
)
select
  domain_list.domain as resource,
  case
    when ns_with_different_ns_count.domain is null then 'ok'
    else 'alarm'
  end as status,
  case
    when ns_with_different_ns_count.domain is null then domain_list.domain || ' name server records returned by parent server match local list.'
    else domain_list.domain || ' parent name server records do not match local records: [' || (select string_agg(target, ', ') from ns_with_name_server_record where parent_server_ns_record_count <> name_server_record_count) || '].'
  end as reason
from
  domain_list
  left join ns_with_different_ns_count on domain_list.domain = ns_with_different_ns_count.domain;