For query assistance, please see the following Best Practices guide


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.

JOURNALS consolidated into the Sophos_process_activity table

  • DirectoryChanges
  • Dns
  • File
    • BinaryChanges
    • BinaryReads
    • DataChanges
    • DataReads
    • OtherChanges
    • OtherReads
  • Http
  • Image
  • Ip
  • Network
  • Process
  • Registry
  • Thread
  • Url

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

 

Also check out the query to show ALL system activity from up to 90 days in the past (Depends on when EDR was deployed on the device and some tables may exceed their storage limits on machines that have higher activity)

https://community.sophos.com/products/intercept/early-access-program/f/live-discover-queries/120672/live-discover-query-all-system-activity-for-n-seconds-from-a-date-time