NDR - Raw record data

-- NAME:        NDR - Raw record data
-- CATEGORY:    NDR
-- DESCRIPTION: Display all fields for the NDR Detection or Report record.  
--              NOTE the interesting bits are in the 'raw' field.  It is a JSON structure.  
--              The 'mapped_raw' is an array structure of the same.
--              USE this query to view the raw field so it is easier to build more advanced queries
-- SOURCE:      Data Lake

-- VARIABLE $$Report Name$$ STRING

WITH NDR_Data AS (
   SELECT
      NDR_IOC.ioc_created_at date_time,
      NDR_IOC.ioc_detection_id,
      NDR_IOC.ioc_severity,
     -- DDE 'reports' have a name at the top level of the JSON structure, but FLOW based detections do not
      CASE
         WHEN CAST(JSON_EXTRACT(NDR_IOC.raw, '$.type') AS VARCHAR) = '1' THEN 'Threat detection'
         WHEN CAST(JSON_EXTRACT(NDR_IOC.raw, '$.type') AS VARCHAR) = '2' THEN CAST(JSON_EXTRACT(NDR_IOC.raw,'$.name') AS VARCHAR)
         ELSE 'UNKNOWN TYPE'
      END Report_Name,
      -- DDE 'reports' have a description, at the top level of the JSON structure, For Flow based detections a description is often in each detection context record, but now always
      CASE
         WHEN CAST(JSON_EXTRACT(NDR_IOC.raw, '$.type') AS VARCHAR) = '1' THEN CAST(JSON_EXTRACT(NDR_IOC.raw,'$.detection_context[0].ids_description') AS VARCHAR)
         WHEN CAST(JSON_EXTRACT(NDR_IOC.raw, '$.type') AS VARCHAR) = '2' THEN CAST(JSON_EXTRACT(NDR_IOC.raw,'$.description') AS VARCHAR)
         ELSE 'UNKNOWN TYPE'
      END Description,
      NDR_IOC.raw,
      NDR_IOC.mapped_raw,
      * -- SHOW FULL RECORD
   FROM mdr_ioc_all AS NDR_IOC
   WHERE NDR_IOC.ioc_worker_id = 'worker_ndr'
   )
   
SELECT * FROM NDR_Data WHERE LOWER(Report_Name) LIKE LOWER('%$$Report Name$$%') ORDER BY date_time DESC, Report_Name ASC

Sample Output: