Control: DNS should have at least 2 MX records

Description

It is recommended to have at least 2 MX records for your domain to provide some load balancing by using multiple MX records with the same preference set, as well as provide a backup MX that can be used if the primary one is unavailable.

Usage

Run the control in your terminal:

powerpipe control run net_insights.control.dns_mx_at_least_two

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run net_insights.control.dns_mx_at_least_two --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_list as (
  select
    distinct domain
  from
    net_dns_record
  where
    domain in (
      select
        jsonb_array_elements_text(to_jsonb($1 :: text [ ]))
    )
  order by
    domain
),
domain_mx_records as (
  select
    domain,
    target
  from
    net_dns_record
  where
    domain in (
      select
        domain
      from
        domain_list
    )
    and type = 'MX'
  order by
    domain
),
mx_ips as (
  select
    domain,
    ip
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_mx_records
    )
    and type = 'A'
),
mx_record_with_ip as (
  select
    domain_mx_records.domain,
    domain_mx_records.target,
    mx_ips.ip
  from
    domain_mx_records
    inner join mx_ips on domain_mx_records.target = mx_ips.domain
),
mx_record_count_by_domain as (
  select
    domain,
    count(*)
  from
    mx_record_with_ip
  group by
    domain
  order by
    domain
)
select
  domain_list.domain as resource,
  case
    when mx_record_count_by_domain.domain is null then 'alarm'
    when mx_record_count_by_domain.count < 2 then 'alarm'
    else 'ok'
  end as status,
  case
    when (
      select
        count(*)
      from
        domain_mx_records
      where
        domain = domain_list.domain
    ) < 2
    and mx_record_count_by_domain.count > 2 then domain_list.domain || ' has 1 MX record, but that MX record has multiple IPs.'
    else domain_list.domain || ' has ' || (
      select
        count(*)
      from
        domain_mx_records
      where
        domain = domain_list.domain
    ) || ' MX record(s).'
  end as reason
from
  domain_list
  left join mx_record_count_by_domain on domain_list.domain = mx_record_count_by_domain.domain;