NDR Data exploration

With the Sophos NDR Connector configured and working you will have detections and reports available.

How to setup the NDR Connector

https://community.sophos.com/mdr-community-channel/mdr-integrations-eap/w/ndr_wiki/127/deployment-and-configuration 

Queries:

NDR List available reports

-- List of Distinct Reports
SELECT DISTINCT
   CAST(JSON_EXTRACT(raw, '$.ingest_date') AS VARCHAR) Ingest_Date,
   CAST(JSON_EXTRACT(raw, '$.name') AS VARCHAR) Report_Name,
--   ARRAY_JOIN(ARRAY_AGG(DISTINCT CAST( JSON_EXTRACT(raw, '$.id') AS VARCHAR)), ', '||CHR(10)) List_of_report_IDs,
   CAST( JSON_EXTRACT(raw, '$.id') AS VARCHAR) ID,
   COUNT(*) Instances
FROM mdr_ioc_all

WHERE ioc_detection_id LIKE 'NDR%' 
   AND CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('2' AS VARCHAR)
GROUP by JSON_EXTRACT(raw, '$.name'), JSON_EXTRACT(raw,'$.id'), JSON_EXTRACT(raw, '$.ingest_date') 
ORDER By 2 ASC, 1 DESC

NDR View a report

To view a report you can take the ID information from the above query and use this to see the contents.

NDR - Show devices without Sophos deployed:

-- Show Devices without XDR
SELECT DISTINCT

   -- Device
   REPLACE(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x-1 AS VARCHAR)||'].src_ip') AS VARCHAR),',',CHR(10)) src_ip,
   CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x-1 AS VARCHAR)||'].src_mac') AS VARCHAR) src_mac,
   REPLACE(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x-1 AS VARCHAR)||'].web_hostname') AS VARCHAR),',',CHR(10)) web_hostname
FROM mdr_ioc_all, UNNEST(SEQUENCE(1,JSON_ARRAY_LENGTH(JSON_EXTRACT(raw,'$.detection_context')))) AS A(x)

WHERE ioc_detection_id LIKE 'NDR%' 
   AND CAST(JSON_EXTRACT(raw, '$.type') AS VARCHAR) = CAST('2' AS VARCHAR)
   AND CAST(JSON_EXTRACT(raw, '$.name') AS VARCHAR) = 'InterceptXNotDetected'
ORDER BY 1 ASC