Vulnerability Management - Macs without actively exploited macOS vulnerabilities

Description

The percentage of Macs running a macOS version with no known actively exploited vulnerabilities that a later release has already fixed, measuring how quickly critical operating system updates reach the fleet.

How we measure it

Find all Macs managed by Kandji that checked in within the last 30 days. Using Apple's security release notes (via macadmins.io), a Mac is considered non-compliant if a later release of its macOS major version fixes a CVE that Apple reports as actively exploited.

Meta Data

Attribute	Value
Metric id	vm_macos_exploited_cves
Category	Vulnerability Management
SLO	90.00% - 98.00%
Weight	0.8
Type

References

Framework	Ref	Domain	Control
ISO 27001:2022	A.8.8	8 Technological controls	Management of technical vulnerabilities
CIS 8.1	7.3	Continuous Vulnerability Management	Perform Automated Operating System Patch Management
CIS 8.1	7.7	Continuous Vulnerability Management	Remediate Detected Vulnerabilities
NIST CSF v2.0	ID.RA-01	Risk Assessment (ID.RA)	ID.RA-01: Vulnerabilities in assets are identified, validated, and recorded
Essential8-ML1	ISM-1695	Patch operating systems	Patches, updates or other vendor mitigations for vulnerabilities in operating systems of workstations, non-internet-facing servers and non-internet-facing network devices are applied within one month of release.
Essential8-ML2	ISM-1695	Patch operating systems	Patches, updates or other vendor mitigations for vulnerabilities in operating systems of workstations, non-internet-facing servers and non-internet-facing network devices are applied within one month of release.

Code

WITH macs AS (
  SELECT details.device_name || ' (' || details.serial_number || ')' AS device_name, details.os_version,
         list_transform(string_split(details.os_version, '.'), part -> TRY_CAST(part AS INTEGER)) AS version
  FROM {{ ref('kandji_device_details') }} AS details
  JOIN {{ ref('kandji_devices') }} AS devices ON devices.device_id = details.device_id
  WHERE details.platform = 'Mac'
    AND NOT devices.is_missing AND NOT devices.is_removed
    AND CURRENT_DATE - CAST(devices.last_check_in AS DATE) <= 30
),
fixes AS (
  SELECT product_version, cve_id,
         list_transform(string_split(product_version, '.'), part -> CAST(part AS INTEGER)) AS version
  FROM {{ ref('macadmins_macos_cves') }}
  WHERE exploited
),
exposure AS (
  SELECT macs.device_name, macs.os_version,
         count(DISTINCT fixes.cve_id) AS exploited_cves,
         string_agg(DISTINCT fixes.cve_id, ', ') AS cve_ids,
         max(fixes.version) AS fixed_in
  FROM macs
  LEFT JOIN fixes
    ON fixes.version[1] = macs.version[1]
    AND fixes.version > macs.version
  GROUP BY 1, 2
)
SELECT
  device_name AS resource,
  'host' AS resource_type,
  CASE WHEN exploited_cves = 0 THEN 1 ELSE 0 END AS compliance,
  CASE
    WHEN exploited_cves = 0 THEN 'macOS ' || os_version || ': no known exploited CVEs fixed in a later ' || CAST(split_part(os_version, '.', 1) AS VARCHAR) || '.x release'
    ELSE 'macOS ' || os_version || ': ' || CAST(exploited_cves AS VARCHAR) || ' actively exploited CVE(s) fixed in a later release, update to macOS ' || array_to_string(fixed_in, '.') || ' (' || cve_ids || ')'
  END AS detail
FROM exposure