Control: Parent server should have name server information

powerpipe control run net_insights.control.dns_parent_ns_listed_at_parent

powerpipe login
powerpipe control run net_insights.control.dns_parent_ns_listed_at_parent --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[])))
),
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'
),
domain_parent_server_ip as (
  select domain, type, ip from net_dns_record where domain in (select parent_server from domain_parent_server)
),
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, string_agg(net_dns_record.target, ', ') as ns_records 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
)
select
  domain as resource,
  case
    when (select ns_records from domain_parent_server_ns_list where domain = domain_list.domain) is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when (select ns_records from domain_parent_server_ns_list where domain = domain_list.domain) is not null then domain || ' parent server has listed name servers.'
    else domain || ' parent server don''t have information for name servers.'
  end as reason
from
  domain_list;