REVIEWED by Sophos
This query will show ALL system activity that was recorded from a time period.
Given the volume of data that can be returned we recommend only pulling a few seconds of information at a time and then using that to narrow down on the particular process/event you are interested in.
I find this is a good forensics collection if I know that an adversary reached the system at a particular moment in time and want to understand what was happening at that time.
If you see a process you are interested in you can take the SophosPID and get the process tree, or pivot to focus just on what that process was doing by using some of the other queries.
SELECT
spa.sophosPID,
spa.subject,
DateTime(spa.time,'Unixepoch') Time_of_activity,
replace(spj.pathname, rtrim(spj.pathname, replace(spj.pathname, '\', '')), '') process_name,
spj.cmdline command_line,
spa.action,
replace(spa.object, rtrim(spa.object, replace(spa.object, '\', '')), '') Object_name,
spa.object Object_Path, spa.fileid File_ID, spa.pathname, spa.filesize, spa.targetpathname, spa.url, spa.source, spa.sourcePort, spa.destination, spa.destinationPort, spa.protocol,
spa.targetSophosPID, spa.cmdLine, spa.keyname, spa.valuename, spa.value, spa.sophosTID
FROM sophos_process_activity spa
LEFT JOIN sophos_process_journal spj ON spj.SophosPID = spa.SophosPID AND
spj.time = replace(spa.SophosPID, rtrim(spa.SophosPID, replace(spa.SophosPID, ':', '')), '') / 10000000 - 11644473600
WHERE
spa.time >= '$$Monitor start period$$' AND
spa.time <= '$$Monitor start period$$' + CAST ('$$Seconds of activity$$' AS INT) ;
