Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.

Purpose

Sophos Community has amassed an incredible catalog of queries for endpoints and servers. The Sophos Data Lake allows us to connect in additional sets of data from Sophos Products and supported third-party connectors. In this post, we will highlight a query submitted by Marcel to show how the Sophos Firewall Data can identify port scans on your appliance.

Prerequisites

You must have XDR enabled in your environment. We will need designer mode enabled.

Sophos Firewall: Port Scanning Detections

NOTE: Data Lake queries are performed entirely on Sophos Central.

/*

-- Credit to https://community.sophos.com/members/marcel for creating the bulk of this query 

  += Descriptive name: portsSeenThreshold
  += Variable type: String
  += Value: use 1 or more 
  += version 3.0 - 2/22/23 
  
*/


--Creates a table of firewall logs with appliance access attempts
WITH ipPorts AS (
 SELECT distinct device_serial_id AS deviceSerial, device_model AS deviceModel, src_ip AS sourceIP, dst_port, in_interface AS interface, CAST(ingestion_time AS VARCHAR) AS scanTimeStamp
   FROM xgfw_data
   WHERE log_type = 'Firewall' 
     AND log_component = 'Appliance Access'
     ),
     
--Counts the ports and sorts the values from the above table
   ipPortCount AS (
      SELECT deviceSerial, deviceModel, sourceIP, COUNT(dst_port) AS portCount, array_join(array_agg(dst_port), ',') AS portList, 
        interface, scanTimeStamp
      FROM ipPorts
      GROUP BY sourceIP, interface, scanTimeStamp, deviceModel, deviceSerial
   )
--Return the data based on a minimum count value
SELECT 
   'PORTSCAN DETECTED' AS detection, scanTimeStamp, sourceIP, portCount, portList, interface, deviceSerial, deviceModel
FROM ipPortCount
WHERE portCount >= $$portsSeenThreshold$$
ORDER BY scanTimeStamp DESC

Results

 

Understanding the Code

In the variable "portsSeenThreshold," you will want to use at least a minimum value of 1. The lower the threshold, the larger the results.

The query relies on two tables:

ipPorts	Gathers device S/N, model, and log information
ipPortCount	Counts the logs and sorts the values

The primary thing someone can use this query for is a framework for examining "log_type" and "log_component" examples that can be found in our Sys Log Collector Guide: https://docs.sophos.com/nsg/sophos-firewall/19.5/PDF/SF-syslog-guide-19.5.pdf

Remember to schedule these reports daily to save you time in threat hunting or network management activities later.

---

Happy querying!

-jk

Added Disclaimer
[edited by: GlennSen at 4:01 PM (GMT -7) on 5 Apr 2023]