Under Review

[LiveDiscoverHelp] IP address activity

Hi there!

I would like to run the default IP address activity query in live discover. Unfortunately I do not get any data older then 2 Weeks from my endpoint. Everything within the last 2 weeks I get the information. I tried IP addresses I visit every day.

The error message I receive from Live Discover is:
finished – errors – Query Cancelled.


SELECT
    STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(ip_journal.time, 'unixepoch')) AS date_time,
    ip_journal.sophosPID AS sophos_pid,
    process_journal.processName AS process_name,
    ip_journal.source AS source,
    ip_journal.sourcePort AS source_port,
    ip_journal.destination AS destination,
    ip_journal.destinationPort AS destination_port,
    ip_journal.originalDestination AS original_destination,
    ip_journal.originalDestinationPort AS original_destination_port,
    CASE ip_journal.protocol
        WHEN 0 THEN 'Unsupported'
        WHEN 1 THEN 'ICMP/ICMPv4'
        WHEN 6 THEN 'TCP'
        WHEN 17 THEN 'UDP'
        WHEN 58 THEN 'ICMPv6'
        ELSE ip_journal.protocol
    END AS protocol,
    file_properties.pathname AS path,
    file_properties.mlScore AS ml_score,
    file_properties.puaScore AS pua_score,
    file_properties.globalRep AS global_rep,
    file_properties.localRep AS local_rep,
    file_properties.sha256 AS sha256
FROM sophos_ip_journal AS ip_journal
LEFT JOIN sophos_process_journal AS process_journal ON
    process_journal.sophosPID = ip_journal.sophosPID
    AND process_journal.time = REPLACE(ip_journal.sophosPID, RTRIM(ip_journal.sophosPID, REPLACE(ip_journal.sophosPID, ':', '')), '') / 10000000 - 11644473600
LEFT JOIN sophos_file_properties AS file_properties ON
    file_properties.sha256 = (
        CASE
            WHEN
                process_journal.sha256 IS NULL
                OR process_journal.sha256 = ''
                THEN '0000000000000000000000000000000000000000000000000000000000000000'
            ELSE
                process_journal.sha256
        END
    )
WHERE
    (
        ip_journal.source LIKE '$$ip_address$$'
        OR ip_journal.destination LIKE '$$ip_address$$'
    )
    AND ip_journal.time > $$start_time$$
    AND ip_journal.time < $$end_time$$
ORDER BY ip_journal.time

Can you please assist?

Thank you!

  • In case others encounter the same issue. This error was found to be related to the "Memory utilization" of the query exceeding the defined limit of "256MB". It's possible to inspect the log-file "SophosOsqueryExtension.log" (located in C:\ProgramData\Sophos\Live Query\Logs\), to see if you find the entry "Usage exceeded" to verify.

    Running the same query with a slightly smaller date range will work to circumvent the issue. You may need to put together the information obtained from a few of these queries to get the full date range you’re looking for, however. 

  • Hi Qoosh

    Thanks for your reply. The second query I have run at the last date / timestampe worked. So I think also it is related to query limitations.

    I will send you the logs in a minute.

  • Hello heartbeat, 

    Thank you for reaching out to the Sophos Community. 

    I suspect what may be happening is that one of the Query limitations is being reached, resulting in the data obtained being truncated. 

    Is it possible to define a second query to run that will begin at the "last date/timestamp" received in the original report data?

    I will also reach out to you via DM to request logging information from one of the affected devices to investigate further.