Control: Name servers should not contain CNAME records if an NS (or any other) record is present

powerpipe control run net_insights.control.dns_ns_no_cname_with_other_record

powerpipe login
powerpipe control run net_insights.control.dns_ns_no_cname_with_other_record --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, type, target from net_dns_record where domain in (select domain from domain_list) 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_ns_records) and type = 'A' order by domain
),
ns_record_with_ip as (
  select
    domain_ns_records.domain,
    domain_ns_records.target as name_server,
    host(ns_ips.ip) as ip_text
  from
    domain_ns_records
    left join ns_ips on domain_ns_records.target = ns_ips.domain
  where
    domain_ns_records.type = 'NS'
    and ns_ips.ip is not null
  order by domain_ns_records.target
),
ns_record_with_record_count_stats as (
  select
    domain,
    name_server,
    (select count(*) from net_dns_record where domain = ns_record_with_ip.domain and dns_server = ns_record_with_ip.ip_text and type = 'CNAME') as cname_record_count,
    (select count(*) from net_dns_record where domain = ns_record_with_ip.domain and dns_server = ns_record_with_ip.ip_text and type not in ('CNAME')) as non_cname_record_count
  from
    ns_record_with_ip
),
ns_record_with_cname_other as (
  select distinct domain from ns_record_with_record_count_stats where cname_record_count > 0 and non_cname_record_count > 0 order by domain
)
select
  domain_list.domain as resource,
  case
    when ns_record_with_cname_other is null then 'ok'
    else 'alarm'
  end as status,
  case
    when ns_record_with_cname_other is null then domain_list.domain || ' name servers have no CNAME record.'
    else domain_list.domain || ' name servers have CNAME record along with NS (or any other) records.'
  end as reason
from
  domain_list
  left join ns_record_with_cname_other on domain_list.domain = ns_record_with_cname_other.domain;