Control: MX records should not have duplicate A records

Description

It is recommended that MX records should not use same IPs, since if the server with IP x.x.x.x shuts down the MX service will still be able to work since it has another backup server.

Usage

Run the control in your terminal:

powerpipe control run net_insights.control.dns_mx_no_duplicate_a_record

Snapshot and share results via Turbot Pipes:

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

Steampipe Tables

net_dns_record

Params

Args	Name	Default	Description	Variable
$1	domain_names	`["github.com","microsoft.com"]`	DNS domain names.	domain_names

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;