Thread Info
  • State Suggested Answer
  • Locked Locked
  • Replies 17 replies
  • Answers 1 answer
  • Subscribers 14 subscribers
  • Views 5826 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Live Discover: Query Cancelled: E Process SophosOsqueryExtension.exe exceeded 30% CPU limit

LHerzog

Hi,

I need this Live Response quickly, unfortunately Sophos Intercept X is aborting the Query.

What is this and how do I get to my data? I just want to use that product with a default query!

2022-03-31T14:29:22.937Z [ 9644: 8204] E Process SophosOsqueryExtension.exe exceeded 30% CPU limit for over: 9 counts

2022-03-31T14:29:22.946Z [ 9644: 6484] W Query Cancelled

2022-03-31T14:28:37.916Z [ 9644: 8204] I Starting FileProcessor: C:\ProgramData\Sophos\Live Query\Queries\Incoming
2022-03-31T14:29:12.534Z [ 9644: 6484] I Running LiveQuery: correlationId:29652b93-474f-41a7-8531-c7104b733871 requestJson:{"name":"File access history","query":"SELECT    \n    STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(file_journal.time,'unixepoch')) AS date_time,\n    process_journal.processName AS process_name,\n    CASE file_journal.eventType\n        WHEN 0 THEN 'Created'\n        WHEN 1 THEN 'Renamed'\n        WHEN 2 THEN 'Deleted'\n        WHEN 3 THEN 'Modified'\n        WHEN 4 THEN 'HardLink Created'\n        WHEN 5 THEN 'Timestamps Modified'\n        WHEN 6 THEN 'Permissions Modified'\n        WHEN 7 THEN 'Ownership Modified'\n        WHEN 8 THEN 'Accessed'\n        WHEN 9 THEN 'Binary File Mapped'\n    END AS event_type,\n    REPLACE(file_journal.pathname, RTRIM(file_journal.pathname, REPLACE(file_journal.pathname, '\\', '')), '') AS file_name,\n    process_journal.pathname AS process_path,\n    file_journal.pathname AS file_path,\n    file_journal.sophosPID AS sophos_pid,\n    process_journal.sha256 AS sha256,\n    process_properties.mlScore AS ml_score,\n    process_properties.puaScore AS pua_score,\n    process_properties.localRep AS local_rep,\n    process_properties.globalRep AS global_rep\nFROM sophos_file_journal AS file_journal\nLEFT JOIN sophos_process_journal AS process_journal\n    ON process_journal.sophosPID = file_journal.sophosPID\n    AND process_journal.time = REPLACE(file_journal.sophosPID, RTRIM(file_journal.sophosPID, REPLACE(file_journal.sophosPID  , ':', '')), '') / 10000000 - 11644473600\nLEFT JOIN sophos_process_properties AS process_properties \n    USING (sophosPID)\nWHERE\n    file_journal.pathname LIKE 'F:\\Folder\\Folder\\Folder%'\n    AND file_journal.time > 1648563081\n    AND file_journal.time < 1648735200\nORDER BY file_journal.time DESC","type":"sophos.mgt.action.RunLiveQuery"}
2022-03-31T14:29:22.937Z [ 9644: 8204] E Process SophosOsqueryExtension.exe exceeded 30% CPU limit for over: 9 counts
2022-03-31T14:29:22.943Z [ 9644: 8204] I Stopping FileProcessor: C:\ProgramData\Sophos\Live Query\Queries\Incoming
2022-03-31T14:29:22.943Z [ 9644: 8204] I Stopping process SophosOsquery.exe
2022-03-31T14:29:22.946Z [ 9644: 6484] W Query Cancelled



This thread was automatically locked due to age.
  • 0

    the VM machine has 2 vCPU - sure - when that process runs, it consumes 50% - what the heck is that 30% limitation? Are you serious, live discover will only run longer than 9 seconds on machines with 4 core CPU??

  • 0 in reply to LHerzog

    What happens if you change the query to just:

    SELECT    
    STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(file_journal.time,'unixepoch')) AS date_time,
    CASE file_journal.eventType
        WHEN 0 THEN 'Created'
        WHEN 1 THEN 'Renamed'
        WHEN 2 THEN 'Deleted'
        WHEN 3 THEN 'Modified'
        WHEN 4 THEN 'HardLink Created'
        WHEN 5 THEN 'Timestamps Modified'
        WHEN 6 THEN 'Permissions Modified'
        WHEN 7 THEN 'Ownership Modified'
        WHEN 8 THEN 'Accessed'
        WHEN 9 THEN 'Binary File Mapped'
    END AS event_type,
    REPLACE(file_journal.pathname, RTRIM(file_journal.pathname, REPLACE(file_journal.pathname, '\', '')), '') AS file_name,
    file_journal.pathname AS file_path,
    file_journal.sophosPID AS sophos_pid
FROM sophos_file_journal AS file_journal
WHERE
    file_journal.pathname LIKE '$$file_path$$'
    AND file_journal.time > $$start_time$$
    AND file_journal.time < $$end_time$$
ORDER BY file_journal.time DESC

    This just uses the same time frame as you define in the variables but only reads from the sophos_file_journal table.

    It might be worth running Process Explorer on the client, with the Performance Graph tab of the SophosOsqueryExtension.exe process open.

    If that is still slow, then we can look into the data behind this table.

  • 0 in reply to Sophos User930

    thanks for your answer.

    that's the same with your modified query.

    It's just, that the query is stopped after 10 seconds due to the 30% for 9 seconds soft limit.

    then it stops.

    This would only run if I put 2 more CPU core into that machine. And I think that is not a solution.

  • FormerMember
    0 FormerMember in reply to LHerzog

    To prevent users from causing severe performance degradation, the queries have a CPU and RAM limiter that prevents any one query from locking/degrading a system down to unresponsive. 

    Are you looking for things on the machines within the last 24 hours?

  • 0 in reply to LHerzog

    Thanks for that, that simplifies the problem somewhat. I wonder how your data is distributed across the archived journal files?

    For example if I run a Process Monitor trace while running the modified query, with a filter for paths that end if .xz and .bin, where the process is SophosOsqueryExtension.exe, then create a report with the "Count occurrences" for the path, I get the following:

    "Value","Count"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003164745-0000000003250d7f-132933840932865956-132933867673898059.xz","218"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000340b8f8-132935705696774197.bin","91"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-0000000003183c6d-00000000032972a8-132933848745300477-132933886082289206.xz","90"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000316472e-0000000003282e33-132933840890635270-132933877533763127.xz","86"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000030cba92-0000000003122f0a-132933691431778441-132933727435204637.xz","60"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-0000000003282e7c-00000000032dab03-132933877540154213-132933916382080575.xz","60"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000340b8a7-132935705682146621.bin","60"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000030cba8e-0000000003122ed6-132933691409439966-132933727124201209.xz","54"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000030cba91-0000000003122f19-132933691416625746-132933727485232707.xz","52"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000334339d-000000000337ac43-132934042507952658-132934091597705364.xz","50"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003250d80-00000000032bbb06-132933867673908032-132933904453521310.xz","42"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000316c3a6-000000000328d5f1-132933843889941531-132933880512936546.xz","41"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003123170-0000000003149e94-132933728208560180-132933804781110586.xz","40"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-000000000340b8c1-132935705689653792.bin","39"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000032dd5a7-00000000033100c4-132933918241352546-132933966088059542.xz","38"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000328d75d-00000000032dd51e-132933880588809428-132933918216194629.xz","34"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000030ac3a7-00000000030cb982-132933279101539688-132933280184891740.xz","34"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000030414a7-0000000003085ccb-132933222917324032-132933260408351291.xz","32"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000032dabb9-00000000033101d1-132933916445576886-132933966687644855.xz","32"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000002ffbf28-00000000030396ab-132933091430769813-132933128067779456.xz","30"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003082eda-00000000030ac291-132933257437188130-132933279004435186.xz","30"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000032bbbea-0000000003310185-132933904541641632-132933966687127990.xz","30"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000331132d-000000000334337d-132934006488505601-132934042497631497.xz","30"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000338adc3-00000000033b86f3-132934856572415035-132934913417738966.xz","28"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000033b87f3-00000000033f1b4d-132934913457479442-132934950592210685.xz","28"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000030414a2-0000000003085cc6-132933222917143475-132933260408020495.xz","28"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-0000000003149f76-000000000316470f-132933804874354665-132933840869528127.xz","28"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000338ae42-00000000033b86a2-132934856574404526-132934913414827354.xz","28"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000033b87b1-00000000033f1dc4-132934913447237160-132934950667842504.xz","28"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000030ac3ab-00000000030ca8e4-132933279102476972-132933280144804412.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000003311336-00000000033432fe-132934006488661887-132934042433530454.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000003343700-000000000337ac36-132934042794612783-132934091457781825.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000033b87ca-00000000033f1bd6-132934913456007507-132934950597458806.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-000000000304143e-0000000003082eb2-132933220975626737-132933257409135276.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003311372-000000000334337a-132934006489087527-132934042496998190.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-000000000334348d-000000000337abee-132934042558282785-132934086277963377.xz","26"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-000000000338adc5-00000000033b8719-132934856572430185-132934913418197011.xz","24"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-0000000003085d26-00000000030ac290-132933260466097417-132933279004435186.xz","24"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000003085ee7-00000000030ac10a-132933260502870349-132933278982370226.xz","22"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000312323a-0000000003149cdf-132933728590057854-132933804580481981.xz","22"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000003149fa4-000000000316c314-132933804899220632-132933843869784729.xz","22"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000337b8b4-0000000003384998-132934187568878154-132934501211811533.xz","22"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-0000000003024eb2-0000000003041421-132933113342673521-132933214517390696.xz","22"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-000000000314f51b-0000000003183c2e-132933812080114413-132933848743728376.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000003384b5b-000000000338adbf-132934501222322796-132934856572324775.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003024e81-000000000304142a-132933113336299806-132933214519557630.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003149fae-0000000003164705-132933804899372418-132933840850612466.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-000000000337b8d8-0000000003384b06-132934187569566699-132934501220464832.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000030ac3ac-00000000030cb8dd-132933279125284071-132933280183485838.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-0000000003122f78-0000000003149f60-132933727826909560-132933804862844304.xz","20"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000030dc3a7-000000000311e205-132933691956932711-132933720977923655.xz","18"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-000000000337f4fa-000000000338595c-132934500467515757-132934501284761258.xz","18"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-0000000003039c49-00000000030413fb-132933128367287489-132933206558125766.xz","18"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-0000000003384b07-000000000338ad6f-132934501220495857-132934856570971646.xz","18"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000337b8b3-000000000338490c-132934187568801550-132934501196710269.xz","18"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-000000000309d507-00000000030a9616-132933275585410318-132933278864589023.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000030ac78c-00000000030c6789-132933279170117250-132933279883596039.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-0000000003125dbc-000000000314652a-132933743087072489-132933802606416139.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000032b3b39-0000000003306572-132933899867667119-132933936170583138.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000033068b0-000000000330e5bd-132933936241091969-132933938487176346.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000033101c6-0000000003311256-132933966687543759-132934003256892784.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-000000000337ac52-000000000337b865-132934094676628434-132934179168359367.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000033fe86d-000000000340b879-132934991039647205-132935705655446367.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-0000000003181646-000000000322448b-132933848493901687-132933863916254424.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000033101fc-000000000331124c-132933966688259075-132934003245511625.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000033fe85b-000000000340b876-132934991038718531-132935705655290187.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000033101e4-0000000003311255-132933966687862450-132934003256580684.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-0000000003384c6d-000000000338add1-132934501234082286-132934856572672786.xz","16"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-0000000003005f1e-0000000003036bd6-132933095472883285-132933126994235522.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-0000000003049aee-00000000030652a7-132933224966162634-132933239584973736.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-0000000003310fbe-0000000003326264-132934002113545697-132934026772588079.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-000000000335fbc0-0000000003365caf-132934057799845137-132934062984406633.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-000000000338bcce-00000000033aafc7-132934856628773014-132934878050458260.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000033b720b-00000000033e3314-132934912235232719-132934939442052630.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000033f1e87-00000000033fe859-132934950731618597-132934991038569908.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-000000000301d964-000000000302f6d1-132933110477677193-132933118037169723.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-000000000310ec3b-0000000003138012-132933710515625555-132933743859006010.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-0000000003149641-0000000003149642-132933803957650732-132933803957700830.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-00000000032ba8ae-00000000032c5256-132933903469702030-132933908703526358.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads\FileDataReads-000000000320a718-000000000320cd1c-132933861581398935-132933861855441797.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-000000000337ac93-000000000337b853-132934094678656416-132934170771486807.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-000000000337ac50-000000000337b83b-132934094676372583-132934170767686813.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000033fe881-000000000340b88e-132934991040528990-132935705662103602.xz","15"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000033f1f28-00000000033fe858-132934950793028440-132934991038544384.xz","14"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000033f1e6f-00000000033fe862-132934950710701756-132934991038895114.xz","14"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000033f3afd-00000000033f3afe-132934951744302922-132934951744302922.xz","13"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-0000000003402411-132935704283670497.bin","13"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-000000000339529a-132934862623471307.bin","13"
"C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads\FileDataReads-00000000032baceb-132933903518449338.bin","13"

    For me, the data comes from about 90 different files.  As you go back further in time or choose a bigger time window, you would expect that the extension has to unpack more data from the compressed journal files but I wonder how many files are being accessed to create the results.

    It is intensive on disk, reading lots of files but for CPU, is it in the decompression?

    If you say, the data is coming from 500 different xz files, that might explain it.

    I wonder how these numbers vary for going back 1, 2 and 3 days for example?  Maybe there is a lot of data on a certain day due to an OS update for example?  The file paths contain the "subject", e.g. "FileOtherChanges", "FileOtherReads", etc. to give a clue as to the types of file operations being extracted and also has the timestamps in the filenames.

    Beyond understanding that, It might be worth running from an admin prompt:

    wpr.exe -start GeneralProfile

    Running the query and when it completes, stop the performance capture with:

    wpr.exe -stop C:\gp.etl

    I'd be interested to see where SophosOsqueryExtension.exe is spending all it's CPU time. Opening that trace in Windows Performance Analyzer would help.  Without the symbols for SophosOsqueryExtension it will be a bit trickier but you can see the APIs being called.  Happy to take a look if needed.

    Thanks

  • FormerMember
    0 FormerMember in reply to Sophos User930

    Writing to the journals is optimized. Reading is slower. To bridge that gap, we created the Data Lake in our XDR offering. The endpoints upload their journals into the lake and you can process a query against that much faster. The limitation is that the data is not the most up to date - you have to wait for data transmission. 

    So, if the data is okay to be a little stale - use the Data Lake. 

    Live Queries are for data that you need to be 100% up to date as of execution.

    One of the ways to constrain the burden a query like that places on the endpoint - limit it to an hour or two in time range. Then use the Data Lake for data older than that.

  • 0 in reply to FormerMember

    : "To prevent users from causing severe performance degradation, the queries have a CPU and RAM limiter that prevents any one query from locking/degrading a system down to unresponsive."

    A: I think this is a good setting. But from my point of perspective, there must be a way to disable that limitation directly from the query within Central. If you have a critical situation you would not care about performance - you need results, ASAP!

    Put a checkbox to Central Query like "Run query with highest priority. This can cause heavy load on the target machine(s)!"

    The target machine is a file server. So surely, there are lots of journal files as mentioned by  . Someone modified a special directory and we don't know when. We want to determine what happened afterwards but cannot.

    How can I run that query? I just want to start the query at the evening and I don't care for high CPU load on the file server at night.

    Update: Support sent me this KB: https://support.sophos.com/support/s/article/KB-000039257?language=en_US

    I think it's outdated because I have 9 seconds, written is 12 seconds. And they mention other soft limits as well. I wonder if this product is designed to work on servers where you will find proably more changes than on endpoints. Especially when your servers have been attacked successfully.

  • 0 in reply to Sophos User930
    Sophos User930 said:
    I wonder how your data is distributed across the archived journal files

    In

    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges

    there are 9123 .xz files beginning with 15.07.2020.

    ..\FileBinaryChanges

    has 17755 files first from 23.12.2019

    as written: it's a file server

    who's cleaning up that old stuff?

  • 0 in reply to LHerzog

    The data is purged but it's based on size not time. Under:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sophos Endpoint Defense\EventJournal\Subjects

    ...is a key for each subject, e.g. in this case: FileDataChanges:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sophos Endpoint Defense\EventJournal\Subjects\FileDataChanges

    MaxDiskUsageMB = 150 (dec) 

    The SEDService will keep the total for this subject under 150MB.  Some of the others are 300MB, E.g. FileBinaryReads.

    This is how you are able to query months worth of data.

    You could, in theory, change the 150 to 10, wait 5 mins and a number of xz files will be removed to keep it under the new size specified. This is removing data.  

    The files aren't all opened when a query comes in, the file name is enough to hint to the query which files need to be opened so the smaller the timeframe queried for, the less files are opened and decompressed.

  • 0 in reply to Sophos User930

    good answer to that sub-question. thanks!

    unfortunately Sophos Support just came back to me with a link to the system requirements of Intercept-X... which are met by the server  :-(

Unfiltered HTML