Under Review
over 1 year ago

Live Discovery Query - SophosPID process activity digest

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