LIve Discovery Query: Process tree for a SophosPID

 REVIEWED by Sophos 

One of the first things I like to understand when looking at a suspect process is how did it get started and what children processes if any did it create.  To do that we need to build a process tree from the sophos process journal.

I realize this query is not the easiest to undrestand. The top section is used to get the ancestors of the sophos PID but looking up the partentPID recursivly.

Once we have that info we insert information about the target sophos PID using a UNION ALL 

We then repeat the use of a recursive loop but this time looking for children of the sophos pid.

This query has a string variable $$SPID$$ that you will need to setup.

SELECT *
FROM (
   WITH RECURSIVE Trace(SophosPID, LEVEL, parent, TIME, cmdline, sha256, pathname) AS (
      VALUES (
         '$$SPID$$',
         0,
         (SELECT ParentSophosPID FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
         (SELECT TIME FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
         0, /* not required Row is used to explain children are above */
         0, /* not required */
         '▬ ANCESTORS ABOVE ▬' /* Create a seperator to indicate where the target SPID is in the tree */
         )

      UNION ALL

      SELECT spj.SophosPID,
         Trace.LEVEL - 1,
         spj.parentSophosPID,
         spj.TIME,
         spj.cmdline,
         spj.sha256,
         spj.pathname
      FROM sophos_process_journal spj
      JOIN Trace ON spj.SophosPID = trace.parent AND
         spj.TIME > replace(trace.parent, rtrim(trace.parent, replace(Trace.parent, ':', '')), '') / 10000000 - 11644473600 -10 AND
         spj.TIME < replace(trace.parent, rtrim(trace.parent, replace(Trace.parent, ':', '')), '') / 10000000 - 11644473600 +3600 AND
         CAST (spj.endtime AS TEXT) = '0'
      )
   SELECT DATETIME (TIME,'unixepoch') Date_Time,
      CASE Trace.SophosPID
         WHEN '$$SPID$$' THEN pathname
         ELSE substr('►►►►►►►►►►►►►►►►►►►►►►►►►', 1, (LEVEL - (SELECT LEVEL FROM trace ORDER BY LEVEL ASC LIMIT 1)) * 1) || (SELECT replace(pathname, rtrim(pathname, replace(pathname, '\', '')), ''))
      END Process_Tree,
      Trace.SophosPID SophosPID,
      cmdline Cmdline,
      sha256,
      LEVEL
   FROM Trace
   ORDER BY LEVEL
   )

UNION ALL

SELECT DATETIME (TIME,'unixepoch'),
   pathname,
   SophosPID,
   cmdline,
   sha256,
   0
FROM sophos_process_journal
WHERE SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600

UNION ALL

SELECT *
FROM (
   WITH RECURSIVE Trace(SophosPID, LEVEL, parent, TIME, cmdline, sha256, pathname) AS (
      VALUES (
         '$$SPID$$',
         0,
         (SELECT ParentSophosPID FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
         (SELECT TIME FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
         0, /* not required  */
         0, /* not required  */
         '▬ CHILDREN BELOW ▬' /* Create a seperator to indicate where the target SPID is in the tree */
         )

      UNION ALL

      SELECT spj.SophosPID,
         Trace.LEVEL + 1,
         spj.parentsophospid,
         spj.TIME,
         spj.cmdline,
         spj.sha256,
         spj.pathname
      FROM sophos_process_journal spj
      JOIN Trace ON spj.parentSophosPID = trace.SophosPID AND
         spj.TIME > replace(trace.SophosPID, rtrim(trace.SophosPID, replace(Trace.SophosPID, ':', '')), '') / 10000000 - 11644473600 -10 AND
         spj.TIME < replace(trace.SophosPID, rtrim(trace.SophosPID, replace(Trace.SophosPID, ':', '')), '') / 10000000 - 11644473600 +3600 AND
         CAST (spj.endtime AS TEXT) = '0'
      ORDER BY 2 DESC
      )
   SELECT DATETIME (TIME,'unixepoch') Date_Time,
      CASE Trace.SophosPID
         WHEN '$$SPID$$' THEN substr('▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬v', 1, LEVEL * 1) || (SELECT replace(pathname, rtrim(pathname, replace(pathname, '\', '')), ''))
         ELSE substr('►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►', 1, LEVEL * 1) || (SELECT replace(pathname, rtrim(pathname, replace(pathname, '\', '')), ''))
      END Process_Tree,
      Trace.SophosPID SophosPID,
      cmdline Cmdline,
      sha256,
      LEVEL
   FROM Trace
   );