Control: Parent server should have name server information

Usage

Run the control in your terminal:

steampipe check net_insights.control.dns_parent_ns_listed_at_parent

Snapshot and share results via Steampipe Cloud:

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

net_dns_record

Params

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

SQL

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;