Control: DNS should have at least 2 MX records

Usage

Run the control in your terminal:

steampipe check net_insights.control.dns_mx_at_least_two

Snapshot and share results via Steampipe Cloud:

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

net_dns_record

Params

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

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;