Approved

Generic Process Search on Windows

Hi folks,

Sophos already published a canned query for  'Search for processes (Windows)', and while that one is really useful I had some asks for a different approach that allowed for larger time windows in the search and some different parameters. 

Descriptive Name Variable Type NOTES
Begin Search on date
$$Begin Search on date$$ DATE Allows selection from the date picker down to seconds
Hours to Search
$$Hours to Search$$ STRING Hours to search accepts fractional data but the minimum search window is 4 hours
command line
$$command line$$ STRING Use % as a wildcard.  Will search the cmdline for any matching text
parent process name
$$parent process name$$
STRING Use % as a wildcard, will search for matching parent process names
process name
$$process name$$
STRING Use % as a wildcard will search for matching process names
user name
$$user name$$
STRING Use % as a wildcard will search for matching username

NOTE: you can specify multiple variables to perform filtering, so say you are looking for powershells run by a specific user that were encoded using -ec.  you can set each of those variables and it will refien the search accordingly.

-- Generic Search
-- VARIABLE:   $$Begin Search on date$$                  DATE
-- VARIABLE:   $$Hours to Search$$                       STRING
-- VARIABLE:   $$command line$$                          STRING
-- VARIABLE:   $$process name$$                          STRING
-- VARIABLE:   $$parent process name$$                   STRING
-- VARIABLE:   $$user name$$                             STRING

-- In order to avoid the watchdog on the device, we will query the journals in in 4 hour chunks (3600 seconds)
WITH RECURSIVE
	Time_Interval(x) AS (
		VALUES ( CAST($$Begin Search on date$$ AS INT) )
		UNION ALL
		SELECT x+14400 FROM Time_Interval WHERE x < CAST($$Begin Search on date$$ AS INT) + CAST( $$Hours to Search$$ * 3600 AS INT)
	)
	
SELECT 
   CAST( replace(datetime(spj.time,'unixepoch'),' ','T') AS TEXT)Date_Time, -- add the T to help excel understand this is a date and time
   CAST( users.username AS TEXT) User_Name,
   CAST( (SELECT processname FROM sophos_process_journal spj2 WHERE spj2.sophosPID = spj.parentSophosPID) AS TEXT) Parent_Process_Name,
   CAST( spj.processname AS TEXT) Process_Name,
   CAST( spj.cmdline AS TEXT) CmdLine,
   
   -- SHOW a pretty bar whre the size depends on the execution duration  Duration bar is a sqrt function based on execution time
   CASE CAST( (CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END)/15 AS INT)
      WHEN 0 THEN '│'
      ELSE printf('%.' || CAST(CAST(SQRT(CAST( CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END AS INT)/15) AS INT) AS TEXT) ||'c', '█')
   END Execution_Duration,
   
   CAST( CASE spj.eventType WHEN 0 THEN strftime('%s', 'now') - spj.processStartTime ELSE MAX(spj.endtime) - spj.processStartTime END AS TEXT) Seconds,
   CAST( spj.sophosPid AS TEXT) SophosPID,
   CAST( spj.parentSophosPID AS TEXT) Parent_SophosPID,
   CAST( spj.SHA256 AS TEXT) SHA256,
   CAST( CASE spj.eventType WHEN 0 THEN 'Process Running' ELSE 'Process Stopped' END AS TEXT)ProcessStatus,
   CAST( spj.pathname AS TEXT) PathName
FROM Time_Interval t 
   LEFT JOIN sophos_process_journal spj ON  spj.time > t.x AND spj.time < t.x+14400
   LEFT JOIN users ON uuid LIKE sid
WHERE 
   -- SEARCH AND FILTER CRITERIA
   LOWER(spj.cmdline) LIKE LOWER('%$$command line$$%') AND 
   LOWER(users.username) LIKE LOWER('%$$user name$$%') AND
   LOWER(spj.processname) LIKE LOWER('%$$process name$$%') AND
   LOWER(Parent_Process_Name) LIKE LOWER('%$$parent process name$$%')
GROUP BY SophosPID