Network Security - DNS Domains with SPF configured

Description

The percentage of DNS domains with email configured that has an SPF 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 an SPF record has been created. Zones without an MX record are assumed not to send email.

Meta Data

Attribute	Value
Metric id	ns_domains_with_spf
Category	Network Security
SLO	95.00% - 99.00%
Weight	0.5
Type

References

Framework	Ref	Domain	Control
ISO 27001:2022	A.8.20	8 Technological controls	Networks security
CIS 8.1	9.2	Email and Web Browser Protections	Use DNS Filtering Services
CIS 8.1	9.3	Email and Web Browser Protections	Maintain and Enforce Network-Based URL Filters
CIS 8.1	12.6	Network Infrastructure Management	Use of Secure Network Management and Communication Protocols
NIST CSF v2.0	PR.DS-01	Data Security (PR.DS)	PR.DS-01: The confidentiality, integrity, and availability of data-at-rest 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 = '' AND content ILIKE '%v=spf1%') > 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 = '' AND content ILIKE '%v=spf1%') > 0 THEN 'SPF record found'
    ELSE 'MX record found, but no SPF 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 = zones.name AND records.content ILIKE '%v=spf1%') > 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 = zones.name AND records.content ILIKE '%v=spf1%') > 0 THEN 'SPF record found'
    ELSE 'MX record found, but no SPF 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