With the Sophos NDR Connector configured and working you will have detections and reports available.
How to setup the NDR Connector
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
