REVIEWED by Sophos
The Sophos Incident Response team is often very busy, today I checked in on some of their current efforts to help accounts respond to active breaches and lent a hand with a query. An account had ransomware hit some unprotected devices, and they wanted to identify all the files that had been encrypted by the adversary. The challenge is Sophos was not on those boxes when the ransomware hit so once deployed all we could do is hunt through the current state of the devices. The adversary had added an extension to the files they encrypted and we wanted a simple query to identify all productivity files that had an extra extension. The extra extension was random characters so a simple LIKE statement would not do the trick.
Below is the query I crafted for them. It helped move that investigation forward and you may find it of value in your own situations.
With ransomware the adversary often modifies the file extension or adds an extension to the filename. When you have Intercept X deployed you are starting with the best protection but it is still possible that through mis-configuration or other reasons you still get hit. If you already had Sophos Intercept X deployed you can search through the Sophos_File_Journal for these types of changes.
The more common experience is an account that does not have Sophos Intercept X protecting a box and that device gets hit. Once you deploy Intercept X with EDR we will start collecting journal information on all actions. We will need to use another type of query to find the ransomware modified files.
Here are two queries to look for productivity files that have had a change in their extension information.
The version of this query when no journals are available is here:
SELECT DISTINCT
filename,
replace(filename, rtrim(filename, replace(filename, '.', '')), '') extension,
rtrim (filename, '.' || replace(filename, rtrim(filename, replace(filename, '.', '')), '')) Extension_removed,
*
FROM file
WHERE path LIKE '$$FilePath to search$$'
AND replace(Extension_removed, rtrim(Extension_removed, replace(Extension_removed, '.', '')), '') IN
('odt', 'ods', 'odp', 'odm', 'odc', 'odb', 'doc', 'docx', 'docm', 'wps', 'xls', 'xlsx', 'xlsm',
'xlsb', 'xlk', 'ppt', 'pptx', 'pptm', 'mdb', 'accdb', 'pst', 'dwg', 'dxf', 'dxg', 'wpd', 'rtf',
'wb2', 'mdf', 'dbf', 'psd', 'pdd', 'pdf', 'eps', 'ai', 'indd', 'cdr', 'dng', '3fr', 'arw', 'srf',
'sr2', 'mp3', 'bay', 'crw', 'cr2', 'dcr', 'kdc', 'erf', 'mef', 'mrw', 'nef', 'nrw', 'orf', 'raf',
'raw', 'rwl', 'rw2', 'r3d', 'ptx', 'pef', 'srw', 'x3f', 'der', 'cer', 'crt', 'pem', 'pfx', 'p12',
'p7b', 'p7c', 'jpg', 'png', 'jfif', 'jpeg', 'gif', 'bmp', 'exif', 'txt')
LIMIT 20000;
If you already have CIXA Deployed you can use this one:
/***************************************************
divided 24 hours up into 10 min increments
This is to ensure we do not consume too much memory
as we process the Sophos_file_journal
****************************************************/
WITH RECURSIVE
for(x) AS (
VALUES ( CAST (strftime ('%s', 'now','-5 days') AS INT) )
UNION ALL
SELECT x + 600 FROM for WHERE x < CAST (strftime ('%s', 'now') AS INT)
)
SELECT DISTINCT
SELECT DISTINCT username FROM users WHERE uuid = spj.sid) User,
datetime(sfj.time,'unixepoch') 'Date Time(UTC)',
replace(spj.pathname, rtrim(spj.pathname, replace(spj.pathname, '\', '')), '') process_name,
CASE sfj.eventType
WHEN 0 THEN 'created'
WHEN 1 THEN 'renamed'
WHEN 2 THEN 'deleted'
WHEN 3 THEN 'modified'
WHEN 4 THEN 'hard link created'
WHEN 5 THEN 'time stamps modified'
WHEN 6 THEN 'permisions modified'
WHEN 7 THEN 'ownership modified'
WHEN 8 THEN 'accessed'
WHEN 9 THEN 'binary file mapped'
ELSE 'unknown ' || sfj.eventType
END Event_TYPE,
replace(sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '\', '')), '') File,
replace(sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '.', '')), '') Initial_extension,
replace(sfj.targetpathname, rtrim(sfj.targetpathname, replace(sfj.targetpathname, '\', '')), '') New_Name,
replace(sfj.targetpathname, rtrim(sfj.targetpathname, replace(sfj.targetpathname, '.', '')), '') New_extension,
sfj.pathname File_pathname,
sfj.SophosPID,
*
FROM for
LEFT JOIN sophos_file_journal sfj ON sfj.subject = 'FileOtherChanges'
AND sfj.time > x
AND sfj.time <= x + 600
AND sfj.eventType IN (1)
LEFT JOIN sophos_process_journal spj ON spj.time = sfj.processStartTime
AND spj.SophosPID = sfj.SophosPID
WHERE Initial_extension IN
('odt', 'ods', 'odp', 'odm', 'odc', 'odb', 'doc', 'docx', 'docm', 'wps', 'xls', 'xlsx', 'xlsm',
'xlsb', 'xlk', 'ppt', 'pptx', 'pptm', 'mdb', 'accdb', 'pst', 'dwg', 'dxf', 'dxg', 'wpd', 'rtf',
'wb2', 'mdf', 'dbf', 'psd', 'pdd', 'pdf', 'eps', 'ai', 'indd', 'cdr', 'dng', '3fr', 'arw', 'srf',
'sr2', 'mp3', 'bay', 'crw', 'cr2', 'dcr', 'kdc', 'erf', 'mef', 'mrw', 'nef', 'nrw', 'orf', 'raf',
'raw', 'rwl', 'rw2', 'r3d', 'ptx', 'pef', 'srw', 'x3f', 'der', 'cer', 'crt', 'pem', 'pfx', 'p12',
'p7b', 'p7c', 'jpg', 'png', 'jfif', 'jpeg', 'gif', 'bmp', 'exif', 'txt')
AND Initial_extension <> New_extension
GROUP BY file
ORDER BY sfj.time
LIMIT 20000;
