REVIEWED by Sophos
Here at Sophos we are working on dozens of additional queries and would like your input.
If you have a query you would like to see created or you have one you've been working on and want input from the community please just ask a question or comment on this discussion topic.
Below is a query that identifies all processes that have performed a file modification in a 1 min window from 1 hour ago It will search through the Sophos_file_journal looking for activity by a process and will provide the process name and type of activity performed.
Do note that this is an expensive query, it may be looking at LOTS of data on the device. To run it you can copy the below text and replace the content of one of the already available queries with this syntax.
We will be adding the ability for admins to create and save their own queries as part of general availability.
Identify the Process that modified a file or file path
SELECT
datetime(sfj.time,'unixepoch') DATE_TIME,
(select 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 'created hard link'
WHEN 5 THEN 'modified time stamps'
WHEN 6 THEN 'modified permisions'
WHEN 7 THEN 'modified ownership'
WHEN 8 THEN 'accessed'
WHEN 9 THEN 'mapped binary file'
ELSE 'unknown'
END Event_TYPE,
CAST((select replace(sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '\', '')), '')) AS TEXT) File_name,
CAST(sfj.SophosPID AS TEXT) SophosPID,
sfj.pathname File_pathname
FROM sophos_file_journal sfj
LEFT JOIN sophos_process_journal spj ON
spj.time = sfj.processStartTime AND
spj.SophosPID = sfj.SophosPID
WHERE sfj.eventType > 0 AND sfj.pathname LIKE '%%' AND sfj.time >= strftime('%s','now','-1 hours') AND sfj.time < strftime('%s','now','-59 minutes')
ORDER BY sfj.time;
