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 XDR and the detections being generated on the endpoint and in the cloud it is important to recognize that the majority of those detections are NOT indicators that an adversary is present. Detections map to the MITRE ATT&CK framework and include activity as simple as a file was deleted to as nefarious as a script wrote encoded data into a registry key and set it to run every 15 min and connect to domain to download files.
Given the overall volume of detections and the variety of risk levels assigned it is often valuable to look at trendlines over time for the device and detections.
To aid in that I have a query that will provide a simple table view of said data.
The query provides a col for each of the MITRE ATT&CK Tactics and a count of the number of detections for that Tactic by day and groups the results for the devices that match the filter criteria. IE if you run this with % for device name it gives you the aggregate number of detections by tactic for all devices.
Query Type: Data lake
Variables
| Variable | Type | Value | NOTES |
| Days | String | 10 | The number of days to get the trendline information for |
| Device | Device Name | % | The name of the device with support for wildcards |
SQL
-- Detection Trends
-- VARIABLE $$Days$$ STRING
-- VARIABLE $$Device$$ DEVICE NAME
With IOC_Counts(host, IOC_TTP, Day) AS (
SELECT meta_hostname, ioc_detection_mitre_attack, date_format(X20.calendar_time,'%m-%d-%Y') Day
FROM xdr_ti_data X20
WHERE ioc_detection_category IN ('Threat','Classifier')
AND ioc_detection_mitre_attack != '[]'
-- FILTERS FOR DEVICE AND DAYS
AND TO_UNIXTIME(X20.calendar_time) > TO_UNIXTIME(NOW())-$$Days$$*24*3600
AND LOWER(meta_hostname) LIKE LOWER('%$$Device$$%')
),
-- Counts by Host and Day for Each Tactic
Tactic_by_Day_and_Host AS (
SELECT
X20.host ep_name,
X20.Day,
-- (SELECT COUNT(X1.host) FROM IOC_Counts X1 WHERE IOC_TTP LIKE '%TA0043%' AND X1.host = X20.host AND X1.Day = X20.Day) Reconnaissance,
-- (SELECT COUNT(X2.host) FROM IOC_Counts X2 WHERE IOC_TTP LIKE '%TA0042%' AND X2.host = X20.host AND X2.Day = X20.Day) Resource_Development,
(SELECT COUNT(X3.host) FROM IOC_Counts X3 WHERE IOC_TTP LIKE '%TA0001%' AND X3.host = X20.host AND X3.Day = X20.Day) Initial_Access,
(SELECT COUNT(X4.host) FROM IOC_Counts X4 WHERE IOC_TTP LIKE '%TA0002%' AND X4.host = X20.host AND X4.Day = X20.Day) Execution,
(SELECT COUNT(X5.host) FROM IOC_Counts X5 WHERE IOC_TTP LIKE '%TA0003%' AND X5.host = X20.host AND X5.Day = X20.Day) Persistence,
(SELECT COUNT(X6.host) FROM IOC_Counts X6 WHERE IOC_TTP LIKE '%TA0004%' AND X6.host = X20.host AND X6.Day = X20.Day) Privilege_Escalation,
(SELECT COUNT(X7.host) FROM IOC_Counts X7 WHERE IOC_TTP LIKE '%TA0005%' AND X7.host = X20.host AND X7.Day = X20.Day) Defense_Evasion,
(SELECT COUNT(X8.host) FROM IOC_Counts X8 WHERE IOC_TTP LIKE '%TA0006%' AND X8.host = X20.host AND X8.Day = X20.Day) Credential_Access,
(SELECT COUNT(X9.host) FROM IOC_Counts X9 WHERE IOC_TTP LIKE '%TA0007%' AND X9.host = X20.host AND X9.Day = X20.Day) Discovery,
(SELECT COUNT(X10.host) FROM IOC_Counts X10 WHERE IOC_TTP LIKE '%TA0008%' AND X10.host = X20.host AND X10.Day = X20.Day) Lateral_Movement,
(SELECT COUNT(X11.host) FROM IOC_Counts X11 WHERE IOC_TTP LIKE '%TA0009%' AND X11.host = X20.host AND X11.Day = X20.Day) Collection,
(SELECT COUNT(X12.host) FROM IOC_Counts X12 WHERE IOC_TTP LIKE '%TA0011%' AND X12.host = X20.host AND X12.Day = X20.Day) Command_And_Control,
(SELECT COUNT(X13.host) FROM IOC_Counts X13 WHERE IOC_TTP LIKE '%TA0010%' AND X13.host = X20.host AND X13.Day = X20.Day) Exfiltration,
(SELECT COUNT(X14.host) FROM IOC_Counts X14 WHERE IOC_TTP LIKE '%TA0040%' AND X14.host = X20.host AND X14.Day = X20.Day) Impact
FROM IOC_Counts X20
GROUP BY X20.host, X20.Day
ORDER BY X20.host, X20.Day
),
Sum_By_day AS (
SELECT
ARRAY_JOIN(ARRAY_AGG(ep_name),CHR(10)) ep_name_list,
Day,
SUM(Initial_Access) Initial_Access,
SUM(Execution) Execution,
SUM(Persistence) Persistence,
SUM(Privilege_Escalation) Privilege_Escalation,
SUM(Defense_Evasion) Defense_Evasion,
SUM(Credential_Access) Credential_Access,
SUM(Discovery) Discovery,
SUM(Lateral_Movement) Lateral_Movement,
SUM(Collection) Collection,
SUM(Command_And_Control) Command_And_Control,
SUM(Exfiltration) Exfiltration,
SUM(Impact) Impact
FROM Tactic_by_Day_and_Host
GROUP BY Day
)
SELECT
ep_name_list,
Day "Day MM-DD-YYYY",
CASE Initial_Access WHEN 0 THEN '' ELSE CAST(Initial_Access AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Initial_Access) AS BIGINT)) END Initial_Access,
CASE Execution WHEN 0 THEN '' ELSE CAST(Execution AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Execution) AS BIGINT)) END Execution,
CASE Persistence WHEN 0 THEN '' ELSE CAST(Persistence AS VARCHAR) || ' ' || substr('█████████████████████', 1, CAST(SQRT(Persistence) AS BIGINT)) END Persistence,
CASE Privilege_Escalation WHEN 0 THEN '' ELSE CAST(Privilege_Escalation AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Privilege_Escalation) AS BIGINT)) END Privilege_Escalation,
CASE Defense_Evasion WHEN 0 THEN '' ELSE CAST(Defense_Evasion AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Defense_Evasion) AS BIGINT)) END Defense_Evasion,
CASE Credential_Access WHEN 0 THEN '' ELSE CAST(Credential_Access AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Credential_Access) AS BIGINT)) END Credential_Access,
CASE Discovery WHEN 0 THEN '' ELSE CAST(Discovery AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Discovery) AS BIGINT)) END Discovery,
CASE Lateral_Movement WHEN 0 THEN '' ELSE CAST(Lateral_Movement AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Lateral_Movement) AS BIGINT)) END Lateral_Movement,
CASE Collection WHEN 0 THEN '' ELSE CAST(Collection AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Collection) AS BIGINT)) END Collection,
CASE Command_And_Control WHEN 0 THEN '' ELSE CAST(Command_And_Control AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Command_And_Control) AS BIGINT)) END Command_And_Control,
CASE Exfiltration WHEN 0 THEN '' ELSE CAST(Exfiltration AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Exfiltration) AS BIGINT)) END Exfiltration,
CASE Impact WHEN 0 THEN '' ELSE CAST(Impact AS VARCHAR) || ' ' ||substr('█████████████████████', 1, CAST(SQRT(Impact) AS BIGINT)) END Impact
FROM Sum_By_Day
ORDER BY Day
Example output
Updated disclaimer
[edited by: Qoosh at 9:46 PM (GMT -7) on 31 Mar 2023]