REVIEWED by Sophos
For this query we want to identify all portable executables that have been written to the device.
We have some variables so if you want to can look for the Portable Executables created by a specific process %powershell% or all processes %.
You also select the start and end dates you are interested in
VARIABLES
STRING $$Name of process deploying the file Uses wildcards$$
DATE $$endTime$$
DATE $$startTime$$
SELECT
CAST(datetime(sfj.time,'unixepoch') AS TEXT) deployTime,
replace(sfj.pathName, rtrim(sfj.pathName, replace(sfj.pathName, '\', '')), '') fileName,
CASE sfj.eventType
WHEN "0" THEN "Executable File Created"
WHEN "1" THEN "Executable File Renamed" END eventType,
spj.processName deployingProcessName,
sfj.pathName filePath,
spj.pathname deployingProcessPath,
sfj.sophosPID, /* The process that deployed the executable */
spj.cmdLine deployingProcessCmdLine,
u.username deployingProcessUser,
sfp.mlScore,
sfp.localRep,
sfp.globalRep,
CASE sfj.sha256
WHEN "" THEN sfhj.sha256 /* The sha256 is not available for file creation events,
but it will be available from the file hash table if the file has been run */
ELSE sfj.sha256 END sha256
FROM sophos_file_journal sfj
LEFT JOIN sophos_process_journal spj ON spj.sophosPID = sfj.sophosPID
LEFT JOIN users u ON u.uuid = spj.sid
LEFT JOIN sophos_file_hash_journal sfhj ON sfhj.fileID = sfj.fileID AND sfhj.time = sfj.time
LEFT JOIN sophos_file_properties sfp ON sfp.sha256 =
(CASE
CAST(datetime(sfj.time,'unixepoch') AS TEXT) deployTime,
replace(sfj.pathName, rtrim(sfj.pathName, replace(sfj.pathName, '\', '')), '') fileName,
CASE sfj.eventType
WHEN "0" THEN "Executable File Created"
WHEN "1" THEN "Executable File Renamed" END eventType,
spj.processName deployingProcessName,
sfj.pathName filePath,
spj.pathname deployingProcessPath,
sfj.sophosPID, /* The process that deployed the executable */
spj.cmdLine deployingProcessCmdLine,
u.username deployingProcessUser,
sfp.mlScore,
sfp.localRep,
sfp.globalRep,
CASE sfj.sha256
WHEN "" THEN sfhj.sha256 /* The sha256 is not available for file creation events,
but it will be available from the file hash table if the file has been run */
ELSE sfj.sha256 END sha256
FROM sophos_file_journal sfj
LEFT JOIN sophos_process_journal spj ON spj.sophosPID = sfj.sophosPID
LEFT JOIN users u ON u.uuid = spj.sid
LEFT JOIN sophos_file_hash_journal sfhj ON sfhj.fileID = sfj.fileID AND sfhj.time = sfj.time
LEFT JOIN sophos_file_properties sfp ON sfp.sha256 =
(CASE
WHEN sfhj.sha256 IS NULL OR sfhj.sha256 = '' THEN '0000000000000000000000000000000000000000000000000000000000000000'
ELSE sfhj.sha256
END )
WHERE sfj.subject = "FileBinaryChanges"
AND sfj.eventType IN (0, 1) /* File created or renamed */
AND sfj.time >= $$startTime$$
AND sfj.time <= $$endTime$$
AND deployingProcessName LIKE '$$Name of process deploying the file Uses wildcards$$'
ELSE sfhj.sha256
END )
WHERE sfj.subject = "FileBinaryChanges"
AND sfj.eventType IN (0, 1) /* File created or renamed */
AND sfj.time >= $$startTime$$
AND sfj.time <= $$endTime$$
AND deployingProcessName LIKE '$$Name of process deploying the file Uses wildcards$$'
