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 Firewall uses firewall rule ID "0" in your log viewer to identify dropped traffic that does not match any rules. This traffic is not logged into XDR. Many users want to be able to use this information to identify dropped packets for troubleshooting or identify potential recon taking place against their firewall or web servers.

In this post, we will review how to enable the firewall rule to start collecting on that traffic and an accompanying query to hunt through those logs. We will include common ports that may be used in hypothetical attack surfaces to aid in your security program.

Prerequisites

You must have XDR enabled in your environment with an Sophos XG or XGS firewall running SFOS v18 or better. We will need designer mode enabled.

If your unit is not currently managed through Sophos Central, or if you are unsure, please review our documentation to do so, located here.

Sophos Firewall: Threat Hunting Logs

NOTE: This query requires you to have a firewall rule like this at the bottom of your rule set - I am exclusively hunting my dropped traffic report.

NOTE: Data Lake queries are performed entirely on Sophos Central. Designer mode is required.

/*

-- author: jkopacko

  += Descriptive names: fw_rule_id, src_ip, dst_ip, dst_port
  += Variable type: String
  += Value: wildcards are supported
  += version 1.0 - 2/26/23
  += Note: if you change '=' to '!=', it can exclude seeing specific entries too
  
*/

SELECT
    "timestamp",
    log_type,
    log_subtype,
    log_component,
    fw_rule_id,
    src_ip,
    dst_ip,
    dst_port,
    coalesce(bytes_sent, 0) + coalesce(bytes_received, 0) AS bytes
FROM
    xgfw_data
WHERE
   COALESCE(CAST(fw_rule_id AS VARCHAR ),'') = '$$fw_rule_id$$'
   AND COALESCE(CAST(src_ip AS VARCHAR ),'') = '$$src_ip$$'
   AND COALESCE(CAST(dst_ip AS VARCHAR ),'') = '$$dst_ip$$'
   AND COALESCE(CAST(dst_port AS VARCHAR ),'') = '$$dst_port$$'
ORDER BY
    "timestamp" DESC

Results

Understanding the Code

The query is setup for explicit hunting. This means the lines of code from 26 through 29 are looking for the specific entry in the logs.

If you enter a wildcard (%) as the variable value, you will need to make a slight change in order to get the proper results. See below for full explanation.

-- Lines 26 - 29 of the source code 

/* 
This change will reflect a wildcard search for src_ip, dst_ip, and dst_port
*/

   COALESCE(CAST(fw_rule_id AS VARCHAR ),'') = '$$fw_rule_id$$'
   AND COALESCE(CAST(src_ip AS VARCHAR ),'') LIKE '$$src_ip$$'
   AND COALESCE(CAST(dst_ip AS VARCHAR ),'') LIKE '$$dst_ip$$'
   AND COALESCE(CAST(dst_port AS VARCHAR ),'') LIKE '$$dst_port$$'
   
/*
This change will reflect a search for everything BUT the variable value entered
*/

   COALESCE(CAST(fw_rule_id AS VARCHAR ),'') = '$$fw_rule_id$$'
   AND COALESCE(CAST(src_ip AS VARCHAR ),'') != '$$src_ip$$'
   AND COALESCE(CAST(dst_ip AS VARCHAR ),'') != '$$dst_ip$$'
   AND COALESCE(CAST(dst_port AS VARCHAR ),'') != '$$dst_port$$'

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

Useful Ports to Hunt

Happy querying!

-jk