Browse Live Discover and Response Queries by Category
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'

Albert Straniti
Parents

  • 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

Comment

  • 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

Children
No Data
Unfiltered HTML