-
Notifications
You must be signed in to change notification settings - Fork 3
/
reportThreatScoringOnDestIpAddr
38 lines (37 loc) · 1.64 KB
/
reportThreatScoringOnDestIpAddr
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/*
--author: @jkopacko
+= Descriptive names: IPaddress
+= Variable type: String
+= Value: 192.168.0.1
+= Version 1.0 - 03/10/21
+= Query type: Live Discover
+= OS Support: Windows
*/
SELECT datetime(sophos_ip_journal.processStartTime, 'unixepoch', 'localtime') AS process_timestamp,
sophos_process_journal.PID AS ProcessID,
sophos_process_journal.pathName,
sophos_process_journal.processName,
sophos_process_journal.cmdLine AS CLI_Log,
datetime(sophos_ip_journal.time, 'unixepoch', 'localtime') AS ip_timestamp,
sophos_ip_journal.source,
sophos_ip_journal.destination,
sophos_ip_journal.destinationPort,
CASE
WHEN (sophos_file_properties.mlScore >= 27 AND sophos_file_properties.mlScore < 30) THEN 'High'
WHEN (sophos_file_properties.mlScore >= 24 AND sophos_file_properties.mlScore < 27) THEN 'Medium'
WHEN (sophos_file_properties.mlScore >= 20 AND sophos_file_properties.mlScore < 24) THEN 'Low'
WHEN (sophos_file_properties.mlScore > 30 AND sophos_file_properties.puaScore > 20) THEN 'High (likely PUA)'
ELSE 'N/A'
END AS threatIndicationLevel,
CASE
WHEN (sophos_file_properties.globalRep >= 65 OR sophos_file_properties.localRep >= 65) THEN 'Safe'
WHEN (sophos_file_properties.globalRep >= 30 AND sophos_file_properties.globalRep <= 64 OR sophos_file_properties.localRep >= 30 AND sophos_file_properties.localRep <= 64) THEN 'Potential'
ELSE 'Bad'
END AS reputationLevel,
FROM sophos_ip_journal
JOIN sophos_process_journal
ON sophos_ip_journal.sophosPID = sophos_process_journal.sophosPID
JOIN sophos_file_properties
ON sophos_process_journal.sha256 = sophos_file_properties.sha256
WHERE source <> '127.0.0.1' OR '::1'
AND destination = '$$IPaddress$$'