REVIEWED by Sophos
Below are a few basic queries for pulling back data from places that malware likes to use for persistence.
First up Registry Run keys:
SELECT
r.path,
r.name,
r.data,
REPLACE(REPLACE(REPLACE(REGEX_SPLIT(r.data, ' [-/]', 0), '"', ''), '%windir%', 'C:\Windows'), '%SystemRoot%', 'C:\Windows') AS filepath,
p.mlScore,
p.puaScore,
p.localRep,
p.globalRep,
json_extract(p.localRepData, '$.reputationData.isSigned') AS is_signed,
json_extract(p.localRepData, '$.reputationData.signerInfo[0].isValid') AS isValid,
json_extract(p.localRepData, '$.reputationData.signerInfo[0].signer') AS signer,
p.sha256 as sha256
FROM registry AS r
JOIN sophos_file_properties p ON p.pathname=filepath
WHERE
( key == 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run' OR
key == 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Run' OR
key == 'HKEY_USERS\Software\Microsoft\Windows\CurrentVersion\Run' OR
key == 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunOnce' OR
key == 'HKEY_USERS\Software\Microsoft\Windows\CurrentVersion\RunOnce' OR
key == 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunServices' OR
key == 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunServicesOnce' OR
key == 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\RunOnce\Setup' )
Notes:
- The query joins on the filepath on the sophos_file_properties table to pull back reputation data
- The query may fail if there is any filepath that doesn't lead to an executable. In that case there is no entry in the sophos_file_properties table and the json_extract will cause the query to fail. In that case simply remove the josn_extracts and replace it with p.localRepData
Second up we have Services:
SELECT
s.display_name,
s.path,
s.status,
s.start_type,
s.service_type,
REPLACE(REGEX_SPLIT(s.path, ' [-/]', 0), '"', '') AS filepath,
p.mlScore,
p.puaScore,
p.localRep,
p.globalRep,
p.sha256,
p.localRepData
FROM services s
JOIN sophos_file_properties p ON p.pathname=filepath;
Note:
There is very little point in trying to extract the json data in the localRepData field as that almost certainly would cause the query to fail when a non binary file is found.
And last but not least files in the startup folders:
SELECT
f.path,
p.mlScore,
p.puaScore,
p.localRep,
p.globalRep,
p.localRepData,
p.sha256 as sha256
FROM file AS f
JOIN sophos_file_properties p ON p.pathname=f.path
WHERE
( path LIKE 'C:\ProgramData\Microsoft\Windows\Start Menu\Programs\StartUp\%' OR
path LIKE 'C:\Users\%\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\%' ) AND NOT ( path LIKE "%desktop.ini" );
