Network Security - DNS Domains with DMARC Configured

Description

The percentage of DNS domains with email configured that have a DMARC record created in the DNS zone.

How we measure it

Find all DNS zones in DNSimple and Cloudflare. For every zone with an MX record, check that a DMARC record is present at _dmarc. Zones without an MX record are assumed not to send email.

Meta Data

Attribute	Value
Metric id	ns_domains_with_dmarc
Category	Network Security
SLO	90.00% - 95.00%
Weight	0.6
Type

References

Framework	Ref	Domain	Control
ISO 27001:2022	A.8.20	8 Technological controls	Networks security
CIS 8.1	9.4	Email and Web Browser Protections	Restrict Unnecessary or Unauthorized Browser and Email Client Extensions
CIS 8.1	12.6	Network Infrastructure Management	Use of Secure Network Management and Communication Protocols
NIST CSF v2.0	PR.DS-02	Data Security (PR.DS)	PR.DS-02: The confidentiality, integrity, and availability of data-in-transit are protected

Code

SELECT
  zone AS resource,
  'domain' AS resource_type,
  CASE
    WHEN count(*) FILTER (WHERE type = 'MX' AND name = '') = 0 THEN 1
    WHEN count(*) FILTER (WHERE type = 'TXT' AND name = '_dmarc' AND content ILIKE '%v=DMARC1%') > 0 THEN 1
    ELSE 0
  END AS compliance,
  CASE
    WHEN count(*) FILTER (WHERE type = 'MX' AND name = '') = 0 THEN 'No MX record found - assumed no email'
    WHEN count(*) FILTER (WHERE type = 'TXT' AND name = '_dmarc' AND content ILIKE '%v=DMARC1%') > 0 THEN 'DMARC record found'
    ELSE 'MX record found, but no DMARC record'
  END AS detail
FROM {{ ref('dnsimple_zone_records') }}
GROUP BY zone

SELECT
  zones.name AS resource,
  'domain' AS resource_type,
  CASE
    WHEN count(records.id) FILTER (WHERE records.type = 'MX' AND records.name = zones.name) = 0 THEN 1
    WHEN count(records.id) FILTER (WHERE records.type = 'TXT' AND records.name = '_dmarc.' || zones.name AND records.content ILIKE '%v=DMARC1%') > 0 THEN 1
    ELSE 0
  END AS compliance,
  CASE
    WHEN count(records.id) FILTER (WHERE records.type = 'MX' AND records.name = zones.name) = 0 THEN 'No MX record found - assumed no email'
    WHEN count(records.id) FILTER (WHERE records.type = 'TXT' AND records.name = '_dmarc.' || zones.name AND records.content ILIKE '%v=DMARC1%') > 0 THEN 'DMARC record found'
    ELSE 'MX record found, but no DMARC record'
  END AS detail
FROM {{ ref('cloudflare_zones') }} AS zones
LEFT JOIN {{ ref('cloudflare_dns_records') }} AS records ON records.zone_id = zones.id
GROUP BY zones.name