Approved

This query is one you are recommended to read the full content of the post to use. It is not simply a copy and paste query, like others in the forum. It is valuable in the right situation 

Outbound SMB Traffic

I am trying to determine what process is generating outbound SMB traffic on a system. I can see the traffic in the firewall logs, but when I use the query below, nothing comes up. It doesn't matter which system I check, or whether I use port 137 or 445. Is there some reason why Sophos XDR would not return outbound SMB traffic?

SELECT sophosPID,
destination,
destinationPort
FROM
sophos_network_journal
WHERE destinationPort = '445'

  • Try out this query, It has a lot more rich information in it. You can search for SMB traffic by specifying port 445 in the port variable of the query.

    --       VARIABLE       start_time  DATE
    --       VARIABLE       end_time  DATE
    --       VARIABLE       ip_address  IP Address
    --       VARIABLE       port      String
    
    SELECT
        STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(network_journal.time, 'unixepoch')) AS date_time,
        users.username AS username,
        process_journal.processName AS process_name,
        CAST(process_journal.cmdline AS TEXT) AS cmd_line,
        CASE CAST(SUM(network_journal.dataSent) / 1024 AS INT)
            WHEN 0 THEN '|'
            ELSE PRINTF('%.' || CAST(SUM(network_journal.dataSent) / 1024 AS TEXT) || 'c', '█')
        END AS data_sent_size,
        CASE CAST(SUM(network_journal.dataRecv) / 1024 AS INT)
            WHEN 0 THEN '|'
            ELSE PRINTF('%.' || CAST(SUM(network_journal.dataRecv) / 1024 AS TEXT) || 'c', '█')
        END AS data_recv_size,
        network_journal.source,
        network_journal.sourcePort AS source_port,
        network_journal.destination,
        network_journal.destinationPort AS destination_port,
        SUM(network_journal.dataSent) AS total_data_sent,
        SUM(network_journal.dataRecv) AS total_data_recv,
        network_journal.sophosPID AS sophos_pid
    FROM sophos_network_journal AS network_journal
    LEFT JOIN sophos_process_journal AS process_journal
        USING (sophosPID)
    LEFT JOIN users
        ON process_journal.sid = users.uuid
    WHERE
        (
            network_journal.source LIKE '$$ip_address$$'
            OR network_journal.destination LIKE '$$ip_address$$'
        )
        AND network_journal.time > $$start_time$$
        AND network_journal.time < $$end_time$$
        AND (network_journal.sourcePort = $$port$$ OR network_journal.destinationPort = $$port$$)
    GROUP BY
        network_journal.sophosPID,
        network_journal.source,
        network_journal.sourcePort,
        network_journal.destination,
        network_journal.destinationPort

    Note: You can use % as wildcard in variable