OS vulnerabilities not remediated within SLO - patchable non-exploitable

Description

The percentage of systems that were active in the last 30 days that have resolved patchable, non-exploitable operating system vulnerabilities within the agreed Service Level Objective (SLO), providing insight into the organisation's "Standard Cycle" - Include in regular patching effectiveness.

How we measure it

Find all active hosts per your vulnerability management system. Join the hosts with the vulnerability database, and filter the vulnerabilities on the criteria as defined in the metric definition.

Meta Data

Attribute	Value
Metric id	vm_performance_patchable_non_exploitable_os
Category	Vulnerability Management
SLO	85.00% - 90.00%
Weight	0.4
Type

References

Framework	Ref	Domain	Control
CIS 8.1	7.7	Continuous Vulnerability Management	Remediate Detected Vulnerabilities
ISO 27001:2022	A.8.8	8 Technological controls	Management of technical vulnerabilities
NIST CSF v2.0	ID.RA-01	Risk Assessment (ID.RA)	ID.RA-01: Vulnerabilities in assets are identified, validated, and recorded

Code

SELECT
  host.hostname AS resource,
  'host'    AS resource_type,
  CASE
    WHEN cve.status NOT IN ('open', 'reopen') then 1
    WHEN CURRENT_DATE - CAST(cve.published_on AS DATE) < 30 THEN 1
    WHEN CURRENT_DATE - CAST(cve.created_at AS DATE) < 21 THEN 1
    ELSE 0
  END AS compliance,
  'Crowdstrike : ' || cve.cve_id || '(' || cve.severity || ') - ' || coalesce(remediation.title, 'No remediation listed') AS detail
FROM
  {{ ref('crowdstrike_hosts') }} AS host
LEFT JOIN
  {{ ref('crowdstrike_vulnerabilities') }} AS cve
  ON host.device_id = cve.agent_id
  AND cve.status IN ('open', 'reopen')
LEFT JOIN
  {{ ref('crowdstrike_vulnerability_remediations') }} AS remediation
  ON remediation.id = cve.id
WHERE
  CURRENT_DATE - CAST(host.last_seen AS DATE) < 30 AND
  coalesce(cve.remediation_level = 'O', false) is true AND
  coalesce(cve.exploit_status = 0, true) is true AND
  cve.severity IN ('HIGH', 'CRITICAL') AND
  cve.cve_id IN (SELECT cve_id FROM {{ ref('cve_db_cve_summary') }} WHERE is_os = 1)

SELECT
  cve.asset_hostname as resource,
  'host'    AS resource_type,
  CASE
    WHEN cve.state NOT IN ('OPEN', 'REOPENED') then 1
    WHEN CURRENT_DATE - CAST(cve.publication_date AS DATE) < 30 THEN 1
    WHEN CURRENT_DATE - CAST(cve.first_found AS DATE) < 21 THEN 1
    ELSE 0
  END AS compliance,
  'TenableIO : ' || unnest(from_json(cve.cve, '["VARCHAR"]')) || '(' || cve.severity || ') - ' || cve.plugin_name AS detail
FROM
  {{ ref('tenableio_vulnerabilities') }} AS cve
WHERE
  CURRENT_DATE - CAST(cve.last_found AS DATE) < 30 AND
  cve.exploit_available is false AND
  cve.has_patch is true AND
  cve.severity in ('high','critical') AND
  EXISTS (SELECT 1 FROM {{ ref('cve_db_cve_summary') }} AS summary WHERE summary.is_os = 1 AND list_contains(from_json(cve.cve, '["VARCHAR"]'), summary.cve_id))