Similar to the Threat Indicators report in Central today, this query evaluates the machine learning and reputation scores to provide a list of the most suspect executables observed in the environment with the added benefit that customers can fine tune the query to help expand or reduce the resulting list.
SELECT
ARRAY_JOIN(ARRAY_AGG(DISTINCT meta_hostname), CHR(10)) AS ep_list,
COUNT(DISTINCT meta_hostname) AS ep_count,
DATE_FORMAT(FROM_UNIXTIME(MIN(time)), '%Y-%m-%dT%H:%i:%SZ') AS first_seen,
DATE_FORMAT(FROM_UNIXTIME(MAX(time)), '%Y-%m-%dT%H:%i:%SZ') AS last_seen,
sha256,
path,
CASE
WHEN ml_score >= 30 THEN 'Malicious : ' || CAST(ml_score AS varchar)
WHEN ml_score >= 27 THEN 'High suspicion : ' || CAST(ml_score AS varchar)
WHEN ml_score >= 24 THEN 'Medium suspicion : ' || CAST(ml_score AS varchar)
WHEN ml_score >= 20 THEN 'Low suspicion : ' || CAST(ml_score AS varchar)
ELSE ''
END AS ml_suspicion,
CASE
WHEN pua_score > 20 THEN 'Suspect PUA : ' || CAST(pua_score AS varchar)
ELSE ''
END AS pua_suspicion,
local_rep,
global_rep,
ARRAY_JOIN(ARRAY_AGG(DISTINCT sophos_pid), CHR(10)) AS sophos_pid_list,
ARRAY_JOIN(ARRAY_AGG(DISTINCT parent_sophos_pid), CHR(10)) AS parent_sophos_pid_list
FROM
xdr_data
WHERE
query_name = 'running_processes_windows_sophos'
AND (ml_score >= 20 OR pua_score > 20)
AND (local_rep < 91)
GROUP BY
sha256,
path,
local_rep,
global_rep,
ml_score,
pua_score
ORDER BY
ml_score DESC,
last_seen DESC
