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

Usage

Run the control in your terminal:

steampipe check net_insights.control.dns_ns_no_cname_with_other_record

Snapshot and share results via Steampipe Cloud:

steampipe login
steampipe check --share net_insights.control.dns_ns_no_cname_with_other_record

net_dns_record

Params

["github.com","microsoft.com"]

SQL

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;