Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.
For query assistance, please see the following Best Practices guide
With limited resources available it is important that you focus on what matters most. It is no different in Cyber Security. I wanted a query that updated the risk score for XDR Detections when they are happening on my critical infrastructure....
So I wrote a query to first identify the critical systems in my environment and assign a score to those systems. With that understood I then take the basic xdr_ti_detections data and add the system score to the detections to get my prioritized list. As this runs against the data lake I can set the query to run nightly.
This query uses a few different methods to identify the critical systems. For this to work with your environment you will need to update the query with your specific data.
Critical system by Unique Sophos ID
The ID used here is the 'host_identifier' a UID assigned by sophos for each device with the software deployed. By adding the ID to the list below I can provide a note on what the system is and assign a value. The higher the number the more critical it is. I recommend keeping the system values from 0-5. so that when we add them to the detection risk score we do not simply generate noise.
ID_Critical_Systems(host_ID, Note, Score) AS (VALUES
('4C4C4544-0046-3310-804A-B8C04F504E32', 'Critical diagnostic equipment','1'),
('e7ca4d56-9236-1ef8-0b86-c3c49fb26e98', 'Keys to the kingdom', '4')
),
Critical systems by Device type
With this query we simply assign a value to all my server infrastructure.
Server_Critical_Systems(host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'Server Infrastructure',
'1'
FROM xdr_data
WHERE meta_endpoint_type = 'server'
)
Critical Systems by User
Like all organization we have some users that handle more sensitive information than others, so any devices used by them are more likely to need investigations when something is detected. Think of the C-Suite or board members.
-- Critical Systems by User
User_Critical_Systems (host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'CEO Accessed Device',
'5'
FROM xdr_data
WHERE meta_username IN ('marymacdonald')
)
Critical Systems by software
Next i look at assigning a critical score to systems that are running business critical software. Like our accounting system, or public facing website.
Software_Critical_Systems (host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'Running critical infrastructure software',
'2'
FROM xdr_data
WHERE query_name IN ('running_processes_windows_sophos','running_processes_linux_events','running_processes_osx_events')
AND name IN ('Teams.exe')
)
Managed Device
Last I roll up all managed devices as a base score of 0
Managed_Devices (host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'Managed Device',
'0'
FROM xdr_data
WHERE query_name IN ('running_processes_windows_sophos','running_processes_linux_events','running_processes_osx_events')
)
Now with the list of critical systems and their values I can run a basic query against all detections to get the prioritized list.
Query Type: Data lake
Variables
| Variable | Type | Value | NOTES |
| Days | STRING | 1 | As I will run this as a scheduled query I set it to 1 day |
| Device name | DEVICE NAME | % | I want to check all devices, but you can put in a device name if just checking that system |
|
critical System Score >= N
|
STRING | 0 | I want to show information for ALL systems not just the critical systems. Use a score of 1 or more to filter this by system criticality. |
SQL
-- Collect a list of critical infrastructure
---VARIABLE $$critical System Score >= N$$ STRING
---VARIABLE $$Days$$ STRING
-- VARIABLE $$Device name$$ DEVICE NAME
-- Provide a manual list of critical infrastructure
WITH ID_Critical_Systems(host_ID, Note, Score) AS (VALUES
('4C4C4544-0046-3310-804A-B8C04F504E32', 'Critical diagnostic equipment','1'),
('e7ca4d56-9236-1ef8-0b86-c3c49fb26e98', 'Keys to the kingdom', '4')
),
-- Critical System by device_type
Server_Critical_Systems(host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'Server infrastructure',
'1'
FROM xdr_data
WHERE meta_endpoint_type = 'server'
),
-- Critical Systems by User
User_Critical_Systems (host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'CEO Accessed Device',
'5'
FROM xdr_data
WHERE meta_username IN ('marymacdonald')
),
-- Critical Systems by software
Software_Critical_Systems (host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'Running critical infrastructure software',
'2'
FROM xdr_data
WHERE query_name IN ('running_processes_windows_sophos','running_processes_linux_events','running_processes_osx_events')
AND name IN ('Teams.exe')
),
-- Managed Device
Managed_Devices (host_id, Note, Score) AS (
SELECT DISTINCT
host_identifier,
'Managed Device',
'0'
FROM xdr_data
WHERE query_name IN ('running_processes_windows_sophos','running_processes_linux_events','running_processes_osx_events')
),
-- Get a full list, some systems may meet multiple criteria
Full_Critical_System_List AS (
SELECT host_id, Note, Score FROM ID_Critical_Systems
UNION ALL
SELECT host_id, Note, Score FROM Server_Critical_Systems
UNION ALL
SELECT host_id, Note, Score FROM User_Critical_Systems
UNION ALL
SELECT host_id, Note, Score FROM Software_Critical_Systems
UNION ALL
SELECT host_id, Note, Score FROM Managed_Devices),
-- Crit System List with aggragate scores
Critical_Systems AS (
SELECT
host_id,
ARRAY_JOIN(ARRAY_AGG(Note),CHR(10)) Notes,
SUM(CAST(Score AS INT)) Total_Score
FROM Full_Critical_System_List
GROUP BY host_id
)
SELECT
meta_hostname,
Notes,
'System('||CAST(Total_Score AS VARCHAR) ||') + Risk('|| CAST(ioc_detection_weight AS VARCHAR) || ') = ' || CAST(ioc_detection_weight + Total_Score AS VARCHAR) Aggragate_Score,
ioc_detection_id,
ioc_detection_description,
ARRAY_JOIN(ARRAY_AGG(DISTINCT name), CHR(10)) Process_Names,
ARRAY_JOIN(ARRAY_AGG(DISTINCT cmdline), CHR(10)) Cmd_Lines,
MIN(DISTINCT sophos_pid) First_SophosPID,
ARRAY_JOIN(ARRAY_AGG(DISTINCT sophos_pid), CHR(10)) Sophos_Pids,
ioc_worker_name,
query_name, MIN(calendar_time) First_Seen,
MAX(Calendar_time) Last_Seen,
ioc_detection_category,
username,
meta_ip_address,
meta_os_platform,
meta_username,
CAST(Total_Score + ioc_detection_weight AS BIGINT) Total_Risk
FROM xdr_ti_data JOIN Critical_Systems ON host_id = host_identifier
WHERE to_unixtime(calendar_time) > TO_UNIXTIME(NOW()) - 3600*24*$$Days$$
AND LOWER(meta_hostname) LIKE LOWER('%$$Device name$$%')
AND Total_Score >= $$critical System Score >= N$$
GROUP BY meta_hostname, meta_os_platform, meta_username, Notes, Total_Score, ioc_detection_weight, ioc_detection_category, ioc_detection_id, ioc_detection_description,
ioc_worker_name, query_name, username, meta_ip_address
ORDER BY 19 DESC, Total_Score DESC
Sampel Results
Updated disclaimer
[edited by: Qoosh at 9:46 PM (GMT -7) on 31 Mar 2023]