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.
Fullscreen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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,
