Control: Name servers should be in different locations

powerpipe control run net_insights.control.dns_ns_different_autonomous_systems

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

with domain_records as (
  select
    domain,
    type,
    target
  from
    net_dns_record
  where
    domain in (select jsonb_array_elements_text(to_jsonb($1::text[])))
),
ns_ips as (
  select
    domain,
    type,
    host(ip) as ip_text
  from
    net_dns_record
  where
    domain in ( select target from domain_records where type = 'NS' )
),
check_ips as (
  select
    distinct array_to_string(array_remove(string_to_array(ns_ips.ip_text, '.'), split_part(ns_ips.ip_text, '.', 4)), '.'),
    domain_records.domain as domain
  from
    domain_records
    inner join ns_ips on domain_records.target = ns_ips.domain
  where
    ns_ips.type = 'A'
    and domain_records.type = 'NS'
)
select
  domain as resource,
  case
    when count(*) = 1 then 'alarm'
    else 'ok'
  end as status,
  case
    when count(*) = 1 then domain || ' name servers are in the same location.'
    else domain || ' name servers are in different locations.'
  end as reason
from
  check_ips
group by domain;