User Security - Phishing Simulation Resilience

Description

The percentage of users who did not fall for any simulated phishing email in the last 12 months, showing how well staff recognise and resist social engineering attacks.

How we measure it

Find all active Okta users who were sent a KnowBe4 simulated phishing email in the last 12 months. Users are considered compliant if, in every simulation, they did not click a link, open an attachment, enable macros, scan a QR code or enter data.

Meta Data

Attribute	Value
Metric id	us_phishing_simulation
Category	User Security
SLO	80.00% - 90.00%
Weight	0.5
Type

References

Framework	Ref	Domain	Control
ISO 27001:2022	A.6.3	6 People controls	Information security awareness, education and training
CIS 8.1	14.2	Security Awareness and Skills Training	Train Workforce Members to Recognize Social Engineering Attacks
NIST CSF v2.0	PR.AT-01	Awareness and Training (PR.AT)	PR.AT-01: Personnel are provided with awareness and training so that they possess the knowledge and skills to perform general tasks with cybersecurity risks in mind

Code

WITH results AS (
  SELECT lower(recipients.user_email) AS email,
         count(*) AS simulations,
         count(*) FILTER (WHERE coalesce(clicked_at, data_entered_at, attachment_opened_at, macro_enabled_at, qr_code_scanned_at) IS NOT NULL) AS failures,
         max(coalesce(data_entered_at, clicked_at, attachment_opened_at, macro_enabled_at, qr_code_scanned_at)) AS last_failure
  FROM {{ ref('knowbe4_pst_recipients') }} AS recipients
  WHERE recipients.delivered_at >= CURRENT_DATE - INTERVAL 365 DAY
  GROUP BY 1
)
SELECT
  users.profile_login AS resource,
  'user' AS resource_type,
  CASE WHEN results.failures = 0 THEN 1 ELSE 0 END AS compliance,
  CASE
    WHEN results.failures = 0 THEN 'Passed all ' || CAST(results.simulations AS VARCHAR) || ' simulated phishing emails'
    ELSE 'Failed ' || CAST(results.failures AS VARCHAR) || ' of ' || CAST(results.simulations AS VARCHAR) || ' simulated phishing emails, last on ' || CAST(CAST(results.last_failure AS DATE) AS VARCHAR)
  END AS detail
FROM results
JOIN {{ ref('okta_users') }} AS users
  ON lower(users.profile_login) = results.email
WHERE users.status = 'ACTIVE'