Control: MX records should not have duplicate A records

Usage

Run the control in your terminal:

steampipe check net_insights.control.dns_mx_no_duplicate_a_record

Snapshot and share results via Steampipe Cloud:

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

net_dns_record

Params

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

SQL

with domain_mx_records as (
  select
    domain,
    target
  from
    net_dns_record
  where
    domain in (
      select
        jsonb_array_elements_text(to_jsonb($1 :: text [ ]))
    )
    and type = 'MX'
),
mx_count_by_domain as (
  select
    domain,
    count(*)
  from
    domain_mx_records
  group by
    domain
),
mx_ips as (
  select
    domain,
    type,
    ip
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_mx_records
    )
),
mx_with_public_ips as (
  select
    domain_mx_records.domain,
    count(*) as ip_usage_count
  from
    domain_mx_records
    inner join mx_ips on domain_mx_records.target = mx_ips.domain
  where
    mx_ips.type = 'A'
  group by
    domain_mx_records.domain,
    mx_ips.ip
),
mx_with_public_ips_count as (
  select
    domain,
    count(*)
  from
    mx_with_public_ips
  where
    ip_usage_count > 1
  group by
    domain
)
select
  d.domain as resource,
  case
    when p.domain is null then 'ok'
    else 'alarm'
  end as status,
  case
    when p.domain is null then d.domain || ' MX records do not have duplicate IPs.'
    else d.domain || ' MX records have duplicate IPs.'
  end as reason
from
  mx_count_by_domain as d
  left join mx_with_public_ips_count as p on d.domain = p.domain;