QUESTION

How can I use Panther's Data Explorer to query the number of cloud resources currently failing policies?

ANSWER

To do this, run the following SQL query in Panther's Data Explorer. It will show the total number of cloud resources, as well as the number of failing and passing resources.

WITH current_resources AS (
  SELECT * 
  FROM panther_cloudsecurity.public.compliance_history
  WHERE p_occurs_since('2 days')
),
latest_check AS (SELECT * FROM 
(SELECT
   *, row_number() over(PARTITION BY resourceId, policyId ORDER BY p_event_time DESC) AS ranking
FROM
   current_resources
WHERE status in ('PASS', 'FAIL', 'ERROR'))
WHERE ranking = 1)
SELECT 
  (SELECT count(DISTINCT resourceId)
   FROM latest_check) AS total_resources, 
  (SELECT count(DISTINCT resourceId)
   FROM latest_check WHERE status in ('FAIL', 'ERROR') and not suppressed) AS failed_resources, 
   total_resources - failed_resources AS passed_resources