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

The Windows Firewall is a security component to help protect your computer from network risks. It comes equipped with inbound and outbound rule configurations. This guide will show how you'll be able to see the firewall profile settings, rules, and if enabled, parse the log file.

Prerequisites

You must have XDR enabled in your environment. 

This is intended for Windows only on Live Discovery.

Query Focus #1 - What are my Windows Firewall settings?

System Impact	Smallest
Data Transferred	Small
Execution Time	Fast

--Domain Profile Information
WITH domain (domainProfileStatus, domainDropLog, domainSuccesLog) AS ( 
    SELECT
            CASE
                WHEN data = 1 THEN 'Enabled'
                ELSE 'Disabled'  
            END,
    CAST((SELECT
            CASE
                WHEN data = 1 THEN 'Enabled'
                ELSE 'Disabled'
            END
        FROM registry
        WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\Logging' AND name = 'LogDroppedPackets') AS TEXT),
    CAST((SELECT
            CASE
                WHEN data = 1 THEN 'Enabled'
                ELSE 'Disabled'
            END
        FROM registry
        WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\Logging' AND name = 'LogSuccessfulConnections') AS TEXT)
    FROM registry
    WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile' AND name = 'EnableFirewall'),
--Public Profile Information
public (publicProfileStatus, publicDropLog, publicSuccessLog) AS (
    SELECT
        CASE
            WHEN data = 1 THEN 'Enabled'
            ELSE 'Disabled'
        END,
    CAST((SELECT
            CASE
                WHEN data = 1 THEN 'Enabled'
                ELSE 'Disabled'
            END
        FROM registry
        WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile\Logging' AND name = 'LogDroppedPackets') AS TEXT),
    CAST((SELECT
            CASE
                WHEN data = 1 THEN 'Enabled'
                ELSE 'Disabled'
            END
        FROM registry
        WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile\Logging' AND name = 'LogSuccessfulConnections') AS TEXT)
    FROM registry
    WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile' AND name = 'EnableFirewall'),
--Private Profile Information
private (privateProfileStatus, privateDropLog, privateSuccessLog) AS (
    SELECT 
        CASE
            WHEN data = 1 THEN 'Enabled'
            ELSE 'Disabled'
        END,
        CAST((SELECT
                CASE
                    WHEN data = 1 THEN 'Enabled'
                    ELSE 'Disabled'
                END
            FROM registry
            WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\Logging' AND name = 'LogDroppedPackets') AS TEXT),
        CAST((SELECT
                CASE
                    WHEN data = 1 THEN 'Enabled'
                    ELSE 'Disabled'
                END
            FROM registry
            WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\Logging' AND name = 'LogSuccessfulConnections') AS TEXT)
    FROM registry
    WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile' AND name = 'EnableFirewall')
--Merge Firewall Information
SELECT * FROM domain JOIN public JOIN private

Results:

Query Focus #2 - What are the firewall rules?

System Impact	Largest
Data Transferred	Average
Execution Time	Fast

--FirewallRules
WITH firewallRules AS (
    SELECT name,
    data,
    datetime(mtime, 'unixepoch', 'localtime') AS modifiedTimeStamp
    FROM registry
    WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\FirewallRules'),
firewallRulesDetails (name, version, action, isActive, direction, description) AS (
    SELECT name, SPLIT(data,'|',0), REPLACE(SPLIT(data,'|',1),'Action=',''), REPLACE(SPLIT(data,'|',2),'Active=',''), REPLACE(SPLIT(data,'|',3),'Dir=',''), REPLACE(substring(data,38,(length(data))),'|',' ')
    FROM firewallRules WHERE data != '')
SELECT name, action, isActive, direction, description FROM firewallRulesDetails
WHERE isActive = 'TRUE'
AND direction = 'In'
ORDER BY name ASC

Results:

Understanding the Code

In this query, it returns all of the firewall rules, but this can be rather time consuming to parse. You could search on specifics within the description field. Add the following snippet below after line 14 in the query.

AND description LIKE '%LPort=3389%'

You can use any of the description contents. It is important to use the format exactly as shown in '%LPort=3389%'. The examples could be

• '%Profile = Public%'
• '%App =%'
• '%Name =%'

Query Focus #3 - What about the firewall logs?

DISCLAIMER: WINDOWS FIREWALL LOGGING IS DISABLED BY DEFAULT

This query uses a variable. You will create a variable type string for the variable name YYYY-MM-DD

System Impact	Largest
Data Transferred	Large
Execution Time	Slow

--Read Log Files
WITH firewallLogs AS(
    SELECT *
    FROM grep
    WHERE path = 'C:\Windows\System32\LogFiles\Firewall\pfirewall.log'
    AND pattern = '$$YYYY-MM-DD$$'),
--Parse Log File Line by Line
    firewallLogsDetail (date, time, action, protocol, srcIP, dstIP, srcPort, dstPort, size, tcpFlags, tcpSyn, tcpPack, tcpWin, icmpType, icmpcode, info, path, pid) AS (
        SELECT SPLIT(line, ' ', 0),
            CAST(SPLIT(line, ' ', 1) AS VARCHAR),
            SPLIT(line, ' ', 2),
            SPLIT(line, ' ', 3),
            SPLIT(line, ' ', 4),
            SPLIT(line, ' ', 5),
            SPLIT(line, ' ', 6),
            SPLIT(line, ' ', 7),
            SPLIT(line, ' ', 8),
            SPLIT(line, ' ', 9),
            SPLIT(line, ' ', 10),
            SPLIT(line, ' ', 11),
            SPLIT(line, ' ', 12),
            SPLIT(line, ' ', 13),
            SPLIT(line, ' ', 14),
            SPLIT(line, ' ', 15),
            SPLIT(line, ' ', 16),
            SPLIT(line, ' ', 17)
        FROM firewallLogs)
--Collect Details in Readable Order
SELECT date, time, pid, action, protocol, srcIP, srcPort, dstIP, dstPort, size, tcpFlags, tcpSyn, tcpPack, tcpWin, icmpType, icmpcode, info, path 
FROM firewallLogsDetail
ORDER BY time ASC

Results:

Understanding the Code

You may want to broaden the scope on returning logs. The variable could be switched from YYYY-MM-DD to YYYY-MM. It will return more data and could possibly fail depending on how big the file is. The watchdog timer will not allow the agent to abuse the system resources.

In addition, adding more constraints will return more precise logs. Much like query two, you can add something like the following examples:

• AND PID = '2596'
• AND destIP = '52.96.222.178'
• AND destPort = '3389' or '22'

The more constraints, the less data returned, and precise the answer.

---

Happy querying!

-jk

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