Port scan detection using Sophos Firewall data in the Data Lake

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.