Under Review

List top threat indicators for Windows

This query evaluates the machine learning and reputation scores to provide a list of the most suspect executables observed in the environment.

Descriptive name Variable Type Notes
Begin Search on date
$$Begin Search on date$$ Date Date and time to search from
Hours to Search
$$Hours to Search$$ String Hours to Search

-- 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)
	),
List_of_Stuff AS ( SELECT * 
FROM Sophos_File_Properties 
WHERE sophos_File_properties.pathname IN (SELECT DISTINCT pathname FROM Time_Interval t JOIN sophos_process_journal spj ON spj.time > t.x and spj.time < t.x+14400) GROUP BY sha256
)
SELECT DISTINCT 
   CAST(sha256 AS TEXT) SHA256,
   pathname,
   CASE mlscore < 24
     WHEN 1 THEN 'Low suspecion : ' || CAST(mlscore AS TEXT)
     ELSE CASE mlscore < 27 WHEN 1 THEN 'Medium suspecion : ' || CAST(mlscore AS TEXT) ELSE 'High suspecion : ' || CAST(mlscore AS TEXT) END
   END mlcore,
   CASE puascore > 20
     WHEN 1 THEN 'Suspect PUA : ' || CAST(puascore AS TEXT)
     ELSE ''
   END puacore,
   localrep,
   globalrep
FROM List_of_Stuff WHERE (mlscore > 23 OR puaScore > 20) AND (localRep < 91) ORDER BY puaScore DESC

SAMPLE OUTPUT

epName
SHA256
pathname
mlScore
puaScore
LocalRep
GlobalRep
cef955780c5e76d65681c71cbd83fc909db66ace929149110043eb01f72a0d48
C:\Program Files\Dell\SupportAssistAgent\PCDr\SupportAssist\6.0.7240.285\SystemIdleCheck.exe
Low suspecion : 4
Suspect PUA : 21
81