Live Discover Query - Malware persistence

  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" );