Malware Protection - Agent Deployment Coverage

Description

The percentage of systems with an up-to-date malware detection agent deployed, ensuring the organization\u2019s defenses are robust against the latest threats, and is critical for minimizing vulnerability to malware attacks.

How we measure it

Find all computers managed by Kandji (checked in within the last 30 days) or Workspace ONE (enrolled and seen within the last 30 days), and match them by serial number to CrowdStrike hosts. Computers are considered compliant if the CrowdStrike sensor has reported in within the last 7 days and is not in reduced functionality mode.

Meta Data

Attribute	Value
Metric id	mp_coverage
Category	Malware Protection
SLO	90.00% - 95.00%
Weight	0.5
Type

References

Framework	Ref	Domain	Control
CIS 8.1	10.1	Malware Defenses	Deploy and Maintain Anti-Malware Software
ISO 27001:2022	A.8.7	8 Technological controls	Protection against malware
NIST CSF v2.0	PR.PS-05	Platform Security (PR.PS)	PR.PS-05: Installation and execution of unauthorized software are prevented

Code

WITH inventory AS (
  SELECT upper(serial_number) AS serial, device_name AS name, 'Kandji' AS source
  FROM {{ ref('kandji_devices') }}
  WHERE NOT is_missing AND NOT is_removed
    AND CURRENT_DATE - CAST(last_check_in AS DATE) <= 30
  UNION ALL
  SELECT upper(serial_number), device_friendly_name, 'Workspace ONE'
  FROM {{ ref('workspaceone_computers') }}
  WHERE enrollment_status = 'ENROLLED'
    AND CURRENT_DATE - CAST(last_seen AS DATE) <= 30
),
devices AS (
  SELECT serial, any_value(name) AS name, string_agg(DISTINCT source, ', ') AS sources
  FROM inventory
  WHERE serial IS NOT NULL AND serial <> ''
  GROUP BY serial
),
sensors AS (
  SELECT upper(serial_number) AS serial,
         max(last_seen) AS last_seen,
         arg_max(reduced_functionality_mode, last_seen) AS reduced_functionality_mode
  FROM {{ ref('crowdstrike_hosts') }}
  GROUP BY 1
)
SELECT
  devices.name || ' (' || devices.serial || ')' AS resource,
  'host' AS resource_type,
  CASE
    WHEN sensors.serial IS NULL THEN 0
    WHEN CURRENT_DATE - CAST(sensors.last_seen AS DATE) > 7 THEN 0
    WHEN sensors.reduced_functionality_mode IS TRUE THEN 0
    ELSE 1
  END AS compliance,
  CASE
    WHEN sensors.serial IS NULL THEN 'No CrowdStrike sensor (serial ' || devices.serial || ', managed by ' || devices.sources || ')'
    WHEN CURRENT_DATE - CAST(sensors.last_seen AS DATE) > 7 THEN 'CrowdStrike sensor last seen ' || CAST(CAST(sensors.last_seen AS DATE) AS VARCHAR)
    WHEN sensors.reduced_functionality_mode IS TRUE THEN 'CrowdStrike sensor in reduced functionality mode'
    ELSE 'CrowdStrike sensor reporting'
  END AS detail
FROM devices
LEFT JOIN sensors ON sensors.serial = devices.serial