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 -60 AND
         spj.TIME < replace(trace.parent, rtrim(trace.parent, replace(Trace.parent, ':', '')), '') / 10000000 - 11644473600 +60 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 -60 AND
         spj.TIME < replace(trace.SophosPID, rtrim(trace.SophosPID, replace(Trace.SophosPID, ':', '')), '') / 10000000 - 11644473600 +60 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
   );

 

epName Date_Time Process_Tree SophosPID Cmdline sha256 LEVEL
2020-05-15 14:23:40
smss.exe
648:132340262209381866
\SystemRoot\System32\smss.exe
5f48638e3397204c2c63d7b76d025d62302d0e45fc5055c0a692b0bbc7e6b337
-2
2020-05-15 14:23:42
►smss.exe
720:132340262220750818
\SystemRoot\System32\smss.exe 0000012c 00000084
5f48638e3397204c2c63d7b76d025d62302d0e45fc5055c0a692b0bbc7e6b337
-1
2020-05-15 14:23:44
▬ ANCESTORS ABOVE ▬
840:132340262241329750
0
0
0
2020-05-15 14:23:44
C:\Windows\System32\wininit.exe
840:132340262241329750
wininit.exe
d5e122606054fa0b03db3ee8cf9ea7701e523875e2bdb87581ad7232ffc9308e
0
2020-05-15 14:23:44
▬ CHILDREN BELOW ▬
840:132340262241329750
0
0
0
2020-05-15 14:23:44
►services.exe
912:132340262242164501
C:\WINDOWS\system32\services.exe
9090e0e44e14709fb09b23b98572e0e61c810189e2de8f7156021bc81c3b1bb6
1
2020-05-15 14:23:44
►►svchost.exe
432:132340262245014484
C:\WINDOWS\system32\svchost.exe -k DcomLaunch -p -s PlugPlay
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
664:132340262245187105
C:\WINDOWS\system32\svchost.exe -k DcomLaunch -p
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►WUDFHost.exe
700:132340262245228839
"C:\Windows\System32\WUDFHost.exe" -HostGUID:{193a1820-d9ac-4997-8c55-be817523f6aa} -IoEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-cdb948d8-5836-430f-a450-b0e08dfb1a71 -SystemEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-ce692ef7-4eca-457b-8ec3-3576c9c2bd34 -IoCancelEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-64408170-2c2a-4975-9304-7ce5415c1e10 -NonStateChangingEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-9cbbb516-8574-4bf1-aaf0-592588fc336a -LifetimeId:5aa600ba-7bef-4aec-a56d-497f7e67072d -DeviceGroupId: -HostArg:0
8fe1e68d6ea8c270e81ee2ac71fcae54be037875977995d6793629b175d9cb8d
2
2020-05-15 14:23:44
►►svchost.exe
1052:132340262246413534
C:\WINDOWS\system32\svchost.exe -k RPCSS -p
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
1104:132340262246716119
C:\WINDOWS\system32\svchost.exe -k DcomLaunch -p -s LSM
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►WUDFHost.exe
1128:132340262247008145
"C:\Windows\System32\WUDFHost.exe" -HostGUID:{193a1820-d9ac-4997-8c55-be817523f6aa} -IoEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-3b77d383-2fc2-4ad0-a30b-0fe9d0fb8669 -SystemEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-77b1beaf-2516-459e-8689-da4efb741b46 -IoCancelEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-32abbe2f-547e-4922-b75f-f1bdda1021d8 -NonStateChangingEventPortName:\UMDFCommunicationPorts\WUDF\HostProcess-74c7ea9e-b463-4ced-91d3-287f0f87e40c -LifetimeId:488fc871-6b2b-4088-8dfa-290ddfbb0561 -DeviceGroupId:WudfDefaultDevicePool -HostArg:0
8fe1e68d6ea8c270e81ee2ac71fcae54be037875977995d6793629b175d9cb8d
2
2020-05-15 14:23:44
►►svchost.exe
1356:132340262249432500
C:\WINDOWS\System32\svchost.exe -k LocalServiceNetworkRestricted -p -s lmhosts
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
1368:132340262249443926
C:\WINDOWS\system32\svchost.exe -k LocalServiceNetworkRestricted -s BTAGService
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
1392:132340262249473415
C:\WINDOWS\system32\svchost.exe -k LocalService -p -s BthAvctpSvc
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
1416:132340262249492458
C:\WINDOWS\system32\svchost.exe -k LocalService -p -s bthserv
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
1476:132340262249733894
C:\WINDOWS\System32\svchost.exe -k LocalSystemNetworkRestricted -p -s NcbService
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►►svchost.exe
1484:132340262249737054
C:\WINDOWS\system32\svchost.exe -k LocalServiceNetworkRestricted -p -s TimeBrokerSvc
dd191a5b23df92e12a8852291f9fb5ed594b76a28a5a464418442584afd1e048
2
2020-05-15 14:23:44
►lsass.exe
936:132340262242650089
C:\WINDOWS\system32\lsass.exe
aa52b2d3dd4b9b47ff4496c0460bdedda791354018cf0782b899ef28acee8d21
1
2020-05-15 14:23:44
►fontdrvhost.exe
672:132340262245167871
"fontdrvhost.exe"
8d4d87697b4761194e8cd6dd028660501bc89e6aef1da9e23d231d5d8868edf1
1
  • I made some changes to correct showing a process twice.  The sophos process journal records process start and process end. we wanted to exclude process end records from the tree.

  • In reply to Karl_Ackerman:

    I found that time is not always as percise as I would like, so I relaxed the time match components for the recursive search. It will be slower now, but appears to address the issue. Those changes are in BOLD BLUE