Control: MX records should have valid hostname

Usage

Run the control in your terminal:

steampipe check net_insights.control.dns_mx_valid_hostname

Snapshot and share results via Steampipe Cloud:

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

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
),
mx_records as (
  select
    domain,
    rtrim(target, '.') as target,
    rtrim(target, '.') ~ '^[^.].*[^-_.]$' as is_valid
  from
    net_dns_record
  where
    domain in (
      select
        domain
      from
        domain_list
    )
    and type = 'MX'
)
select
  domain as resource,
  case
    when (
      select
        count(*)
      from
        mx_records
      where
        domain = domain_list.domain
        and not is_valid
    ) > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when (
      select
        count(*)
      from
        mx_records
      where
        domain = domain_list.domain
        and not is_valid
    ) > 0 then domain || ' has MX record(s) ' || (
      select
        string_agg(target, ', ')
      from
        mx_records
      where
        domain = domain_list.domain
        and not is_valid
    ) || ' with invalid host name.'
    else domain || ' has no MX records with invalid host name.'
  end as reason
from
  domain_list;