Rare process trees with a LOLBIN tool

With leadless threat hunting where you are simply looking for strange activity in the environment to determine if it is an as yet undiscovered adversary it is often valuable identify things that are RARE or UNIQUE.

With the Rare Tree query below we are looking for rare parent, child, grandchild process relationships that include a LOL bin tool, like powershell. 

The query works best in a larger environment were we have a large set of process threes we can identify and count.

-- VARIABLE $$Tree must contain process with name like$$ STRING
-- VARIABLE $$Maximum number of identical trees$$        STRING
WITH LOL_BIN_Tree (Instances, TREE, Grand_parent, Parent_path, Child_path, Suspect_SophosPID) AS (
   SELECT COUNT(X1.sophos_pid) instances, X2.parent_name||' ► ► '||X1.parent_name||' ► '||X1.name, X2.parent_name, X2.name, X1.name, MAX(X1.sophos_pid) SophosPID 
   FROM xdr_data X1 JOIN xdr_data X2 ON X2.query_name = 'running_processes_windows_sophos' AND X2.sophos_pid = X1.parent_sophos_pid AND X2.meta_hostname = X1.meta_hostname
   WHERE X1.query_name = 'running_processes_windows_sophos'
      AND 
         (X1.path LIKE 'C:\Windows\System32\%.exe' OR X1.path LIKE 'C:\Windows\System32\%\%.exe' OR X1.path LIKE 'C:\Windows\System32\%\%\%.exe'  
          OR X1.path LIKE 'C:\Windows\SysWOW64\%.exe' OR X1.path LIKE 'C:\Windows\SysWOW64\%\%.exe'OR X1.path LIKE 'C:\Windows\SysWOW64\%\%\%.exe'
          OR X1.path LIKE 'C:\Windows\SxS\%.exe' OR X1.path LIKE 'C:\Windows\SxS\%\%.exe' OR X1.path LIKE 'C:\Windows\SxS\%\%\%.exe')
      AND X2.parent_name > '' AND X2.parent_name NOT IN ('SophosSetup_Stage2.exe')
   GROUP by X2.parent_name, X1.parent_name, X1.name, x2.name
   ORDER BY Instances ASC
   )

SELECT 
   meta_hostname Device_Name,
   CASE 
      WHEN Instances = 1 THEN 'Unique LOL_BIN TREE'
      ELSE 'Rare LOL_BIN_TREE( Insances: '||CAST(instances as VARCHAR)||')'
   END Rarity,
   Tree,
   name LOL_BIN_Process_Name, 
   cmdline, 
   path,
   Suspect_SophosPID
FROM LOL_BIN_Tree JOIN xdr_data ON query_name = 'running_processes_windows_sophos' AND sophos_pid =  Suspect_SophosPID
WHERE Instances < $$Maximum number of identical trees$$
   AND Tree LIKE '%$$Tree must contain process with name like$$%'
ORDER BY Instances ASC

Not just because something is an anomaly from normal does not mean it is malicious, so use this to find rare things that may be worth investigating.

I would love some feedback from folks that find this useful or from folks that simply determine it is too noisy to be of any value.

In the result set below splunkd.exe is the RAT I deployed on the VM_TEST_1 device

Thanks

KA