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.
Parents
  • 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.

Reply
  • 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.

Children
No Data
Unfiltered HTML