REVIEWED by Sophos
We have added a new table to the sophos forensics journals. The sophos_process_activity table.
Often as part of an investigation you need to to get a quick view of what a process did in the past and this table provides a quick lookup location for that information.
This table contains a subject for each of the other Sophos 'journals' and collects some of the more useful information like Registry Key/Values for the registry journal, IP/Port/Protocol for the various network activity journals and much more.
A process can generate thousands of recorded actions and can be running for several days so using this table requires us set some limits on how much data we want. Below is an example that requires three variables to ensure we do not try and collect too much data.
/* Collect the process name and cmdline of the target process */
WITH Target_process(process_name, process_cmdLine)
AS (
SELECT replace(spj.pathname, rtrim(spj.pathname, replace(spj.pathname, '\', '')), '') process_name, spj.cmdline process_cmdline
FROM sophos_process_journal spj WHERE spj.SophosPID = CAST ('$$SophosPID$$' AS TEXT) AND
time = replace('$$SophosPID$$', rtrim('$$SophosPID$$', replace('$$SophosPID$$', ':', '')), '') / 10000000 - 11644473600
LIMIT 1)
SELECT
subject,
DateTime(time,'Unixepoch') Time_of_activity,
Target_process.process_name,
Target_process.process_cmdline,
action,
replace(object, rtrim(object, replace(object, '\', '')), '') Object_name,
object Object_Path, fileid File_ID, pathname, filesize, targetpathname, url, source, sourcePort, destination, destinationPort, protocol, targetSophosPID, cmdLine, keyname, valuename, value, sophosTID
FROM sophos_process_activity JOIN Target_process
WHERE SophosPID = '$$SophosPID$$' AND
time > replace('$$SophosPID$$', rtrim('$$SophosPID$$', replace('$$SophosPID$$', ':', '')), '') / 10000000 - 11644473600
+ CAST ('$$Begin collection N Minutes after process start$$' AS INT) AND
time < replace('$$SophosPID$$', rtrim('$$SophosPID$$', replace('$$SophosPID$$', ':', '')), '') / 10000000 - 11644473600
+ CAST ('$$Begin collection N Minutes after process start$$' AS INT)
+ CAST ('$$Number of minutes of activity to collect$$' AS INT)
;
