-- QUERY NAME: NDR Report with last execution time
-- CATEGORY: All queries, NDR
-- DESCRIPTION: List the available NDR reports and the most current report execution time
-- SOURCE: Data Lake
-- VARIABLE $$Report Name$$ STRING
SELECT DISTINCT
CASE
WHEN CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('1' AS VARCHAR) THEN 'Detection Severity: ' || CAST(JSON_EXTRACT(raw,'$.severity_score') AS VARCHAR)
WHEN CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('2' AS VARCHAR) THEN CAST(JSON_EXTRACT(raw, '$.name') AS VARCHAR)
ELSE 'UNKNOWN'
END Report_Name,
ioc_detection_description,
CASE
WHEN CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('1' AS VARCHAR) THEN 'FLOW_DETECTION'
WHEN CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('2' AS VARCHAR) THEN 'DDE_REPORT'
ELSE 'Unknown Type: '||CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR)
END Report_Type,
COUNT(*) Instances,
MAX(CAST(JSON_EXTRACT(raw, '$.ingest_date') AS VARCHAR)) Last_run
FROM mdr_ioc_all
WHERE ioc_worker_id = 'worker_ndr'
AND CASE
WHEN CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('1' AS VARCHAR) THEN LOWER('Detection Severity: ' || CAST(JSON_EXTRACT(raw,'$.severity_score') AS VARCHAR)) LIKE LOWER('%$$Report Name$$%')
ELSE LOWER(CAST(JSON_EXTRACT(raw, '$.name') AS VARCHAR)) LIKE LOWER('%$$Report Name$$%')
END
GROUP by 1,2,3
ORDER By 5 DESC, 1 ASC
Sample output:
