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

Find traffic for destination port

Variables DestinationPort and DaysToLookBack

SELECT
strftime('%Y-%m-%dT%H:%M:%SZ', datetime(snj.time,'unixepoch')) dateTime,
u.username userName,
snj.sophosPID,
spj.processName processName,
CAST(spj.cmdline AS TEXT) cmdLine,
snj.source,
snj.sourcePort,
snj.destination,
snj.destinationPort,
snj.dataSent,
snj.dataRecv
FROM sophos_network_journal snj
LEFT JOIN sophos_process_journal spj USING (sophosPID)
LEFT JOIN users u ON spj.sid = u.uuid
WHERE snj.destinationPort LIKE '$$DestinationPort$$' AND snj.time > strftime('%s','NOW','-$$DaysToLookBack$$ days')

j0hnV
Parents

  • Or with start- and stop time:

    SELECT
    strftime('%Y-%m-%dT%H:%M:%SZ', datetime(snj.time,'unixepoch')) dateTime,
    u.username userName,
    snj.sophosPID,
    spj.processName processName,
    CAST(spj.cmdline AS TEXT) cmdLine,
    strftime('%Y-%m-%dT%H:%M:%SZ',datetime(snj.processStartTime,'unixepoch')) AS Process_Start,
    strftime('%Y-%m-%dT%H:%M:%SZ',datetime(snj.startTime,'unixepoch')) AS Connection_Start,
    snj.source,
    snj.sourcePort,
    snj.destination,
    snj.destinationPort,
    snj.dataSent,
    snj.dataRecv
    FROM sophos_network_journal snj
    LEFT JOIN sophos_process_journal spj USING (sophosPID)
    LEFT JOIN users u ON spj.sid = u.uuid
    WHERE snj.destinationPort LIKE '$$DestinationPort$$' AND snj.time BETWEEN $$FirstTime$$ AND $$LastTime$$

Comment

  • Or with start- and stop time:

    SELECT
    strftime('%Y-%m-%dT%H:%M:%SZ', datetime(snj.time,'unixepoch')) dateTime,
    u.username userName,
    snj.sophosPID,
    spj.processName processName,
    CAST(spj.cmdline AS TEXT) cmdLine,
    strftime('%Y-%m-%dT%H:%M:%SZ',datetime(snj.processStartTime,'unixepoch')) AS Process_Start,
    strftime('%Y-%m-%dT%H:%M:%SZ',datetime(snj.startTime,'unixepoch')) AS Connection_Start,
    snj.source,
    snj.sourcePort,
    snj.destination,
    snj.destinationPort,
    snj.dataSent,
    snj.dataRecv
    FROM sophos_network_journal snj
    LEFT JOIN sophos_process_journal spj USING (sophosPID)
    LEFT JOIN users u ON spj.sid = u.uuid
    WHERE snj.destinationPort LIKE '$$DestinationPort$$' AND snj.time BETWEEN $$FirstTime$$ AND $$LastTime$$

Children
No Data
Unfiltered HTML