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