In this query I correlate 'Appliace Access' log entries logged by the Sophos Firewall to see if someone ran a port scan against my IP address / appliance.
-- VARIABLE $$Ports_Seen_Threshold$$ String
-- Ignoring log entries with src_port 53 (DNS) due to Unity Media Connect Box reset issues
WITH IP_Ports AS (
SELECT distinct device_serial_id AS Device_ID, src_ip AS Source_IP, dst_port, in_interface AS Interface, CAST(timestamp AS DATE) AS Scan_Date
FROM xgfw_data
WHERE log_type = 'Firewall'
AND log_component = 'Appliance Access'
AND src_port <> 53
ORDER BY dst_port
),
IP_Port_Count AS (
SELECT Device_ID, Source_IP, COUNT(dst_port) AS Port_Count, array_join(array_agg(dst_port), ',') AS Port_List, Interface, Scan_Date
FROM IP_Ports
GROUP BY Source_IP, Interface, Scan_Date, Device_ID
)
SELECT
'PORTSCAN DETECTED' AS Detection, Scan_Date, Source_IP, Port_Count, Port_List, Interface, Device_ID
FROM IP_Port_Count
WHERE Port_Count >= $$Ports_Seen_Threshold$$
ORDER BY Scan_Date DESC
Below you will find an example of the output generated. Note that ýou can use the pivot functions (the 3 dots behind the IP addresses listed) to get additional information about them.
Please note that I had to exclude source port 53 due to some strange behavior of my router. You can remove this part of the query ('AND src_port <> 53) if you want to include port 53 as source as well.
