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

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.
  • Thanks. In the initial post, you are querying from:29th March to 31st March. I wonder how many .xz files (now it's in the past, all the data will be coming from xz files not .bin) are accessed to cover that 3 day time frame on this server?

    Did you manage to run a Process Monitor trace while the query was running with a filter for paths that end if .xz, where the process is SophosOsqueryExtension.exe. Once the query has completed then create a report with the "Count occurrences" for the path. This will give a unique list of file paths. How many is that? Thousands?

    If it's a busy server, under the Filter Menu, choose "Drop filtered Events" so only those events that match the filter are retained.

  • Hi,

    it's about 50 files in DataChanges for the 3 days.

    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges>dir | find "29.03.2022"
    29.03.2022  05:34             1.328 FileDataChanges-00000000d60c996c-00000000d60d7ad7-132929665066719021-132929676050159249.xz
    29.03.2022  06:33             1.272 FileDataChanges-00000000d621d75e-00000000d6238299-132929981303360973-132929997867519067.xz
    29.03.2022  07:40             3.764 FileDataChanges-00000000d624a2e5-00000000d627cbfe-132930018921075218-132930048676843580.xz
    29.03.2022  08:45            11.836 FileDataChanges-00000000d628524f-00000000d62b0d7e-132930058078122819-132930095518865813.xz
    29.03.2022  09:50            21.536 FileDataChanges-00000000d62b218b-00000000d62df2e3-132930095888162623-132930134435765231.xz
    29.03.2022  10:55            31.640 FileDataChanges-00000000d62df403-00000000d631115a-132930134614349814-132930173475262785.xz
    29.03.2022  11:55            34.528 FileDataChanges-00000000d6311286-00000000d633e06e-132930173599434091-132930209522586452.xz
    29.03.2022  12:55            34.336 FileDataChanges-00000000d633e119-00000000d636aa96-132930209603746037-132930245518409002.xz
    29.03.2022  13:55            22.960 FileDataChanges-00000000d636ac7f-00000000d6394728-132930245733325204-132930281504029499.xz
    29.03.2022  14:55            60.604 FileDataChanges-00000000d6394d7c-00000000d63c05c4-132930281765089475-132930317411613050.xz
    29.03.2022  16:00            24.768 FileDataChanges-00000000d63c1088-00000000d63ec9ce-132930317861781663-132930355935582385.xz
    29.03.2022  17:01            22.808 FileDataChanges-00000000d63ed134-00000000d6447237-132930356621884923-132930393370838740.xz
    29.03.2022  18:03            15.320 FileDataChanges-00000000d64486d2-00000000d6482019-132930394573454676-132930429978684584.xz
    29.03.2022  19:03             6.868 FileDataChanges-00000000d6482f3d-00000000d64a8c57-132930430938434334-132930464684625087.xz
    29.03.2022  21:03             1.092 FileDataChanges-00000000d64ab9c5-00000000d64ce252-132930467939053506-132930499655385313.xz
    
    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges>dir | find "30.03.2022"
    30.03.2022  05:44             2.584 FileDataChanges-00000000d64f9ef5-00000000d651d2da-132930541790486129-132930574320526286.xz
    30.03.2022  06:44             2.804 FileDataChanges-00000000d6659c23-00000000d667f9f3-132930851165648419-132930887023648277.xz
    30.03.2022  07:44             3.128 FileDataChanges-00000000d6681758-00000000d66a17dd-132930887427883352-132930917334586962.xz
    30.03.2022  08:49            17.572 FileDataChanges-00000000d66a9494-00000000d66d514c-132930923773825018-132930961091767598.xz
    30.03.2022  09:54            33.412 FileDataChanges-00000000d66d5d2a-00000000d670a0c7-132930962199167659-132931000920768761.xz
    30.03.2022  10:54            22.564 FileDataChanges-00000000d670a242-00000000d6732ff3-132931001125777481-132931036799623809.xz
    30.03.2022  11:59            33.808 FileDataChanges-00000000d673404d-00000000d6760aa6-132931037470637289-132931075942190195.xz
    30.03.2022  13:04            26.068 FileDataChanges-00000000d67615d2-00000000d678e2d4-132931076410841190-132931115094116358.xz
    30.03.2022  14:09            52.340 FileDataChanges-00000000d678e679-00000000d67baff1-132931115288213230-132931154107772172.xz
    30.03.2022  15:12            47.108 FileDataChanges-00000000d67bb020-00000000d67ec2fd-132931154121733531-132931192002749171.xz
    30.03.2022  16:14            23.224 FileDataChanges-00000000d67ec613-00000000d682a40c-132931192120150649-132931229177282443.xz
    30.03.2022  17:19            17.796 FileDataChanges-00000000d682b788-00000000d685835a-132931230388440284-132931268163032139.xz
    30.03.2022  18:19            12.416 FileDataChanges-00000000d6858714-00000000d6880834-132931268309057574-132931303465952525.xz
    30.03.2022  19:24             7.172 FileDataChanges-00000000d6882555-00000000d68aa5cc-132931305146910501-132931342089274549.xz
    30.03.2022  20:24             9.544 FileDataChanges-00000000d68abbbc-00000000d68d5105-132931343435333719-132931378892854938.xz
    30.03.2022  21:24             5.736 FileDataChanges-00000000d68d5fce-00000000d68f14f9-132931379869654038-132931401221496540.xz
    30.03.2022  22:34             1.288 FileDataChanges-00000000d68ff01c-00000000d6905b7b-132931416882201367-132931424764512160.xz
    30.03.2022  23:59             1.112 FileDataChanges-00000000d692a867-00000000d694fc90-132931459322833115-132931493203288058.xz
    
    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges>dir | find "31.03.2022"
    31.03.2022  05:19               548 FileDataChanges-00000000d696407e-00000000d6967368-132931508532584844-132931511981667229.xz
    31.03.2022  06:19               872 FileDataChanges-00000000d6a4a671-00000000d6a64eda-132931701467854805-132931724526128667.xz
    31.03.2022  07:19             1.416 FileDataChanges-00000000d6a7047f-00000000d6a9093d-132931737946600776-132931766234113422.xz
    31.03.2022  08:24             4.564 FileDataChanges-00000000d6a9757a-00000000d6abe89e-132931774085967185-132931811045570140.xz
    31.03.2022  09:29            14.976 FileDataChanges-00000000d6ac0d55-00000000d6aea94e-132931813724704759-132931850202734965.xz
    31.03.2022  10:34            23.600 FileDataChanges-00000000d6aeb34f-00000000d6b17adf-132931850646549651-132931889194347201.xz
    31.03.2022  11:34            25.632 FileDataChanges-00000000d6b17b8a-00000000d6b430e5-132931889250411972-132931925100286301.xz
    31.03.2022  12:39            21.364 FileDataChanges-00000000d6b4323e-00000000d6b737e7-132931925266443076-132931963713645702.xz
    31.03.2022  13:42            18.644 FileDataChanges-00000000d6b74495-00000000d6ba69b8-132931964361081838-132932001526105660.xz
    31.03.2022  14:44            17.364 FileDataChanges-00000000d6ba80c9-00000000d6be4d21-132932002724466278-132932039249775830.xz
    31.03.2022  15:49            15.988 FileDataChanges-00000000d6be86de-00000000d6c20976-132932040096451977-132932078304296533.xz
    31.03.2022  16:49            21.692 FileDataChanges-00000000d6c21e8e-00000000d6c569be-132932078738751633-132932114237578629.xz
    31.03.2022  17:51            16.284 FileDataChanges-00000000d6c573c5-00000000d6cb0ae1-132932114918344513-132932151331085132.xz
    31.03.2022  19:16             7.876 FileDataChanges-00000000d6cb25a3-00000000d6cd11bb-132932151906829065-132932183106856709.xz
    31.03.2022  20:16             1.708 FileDataChanges-00000000d6cea3ff-00000000d6d06cf5-132932204648587280-132932230340696659.xz
    31.03.2022  21:21             2.556 FileDataChanges-00000000d6d0f94d-00000000d6d38e70-132932240777231770-132932276119199432.xz

    But in DataReads, the number of files is almost identical but they are much larger.

    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads>dir | find "29.03.2022"
    29.03.2022  05:34             2.900 FileDataReads-00000000d60c9961-00000000d60d84b7-132929665057614989-132929676653941863.xz
    29.03.2022  06:33             1.620 FileDataReads-00000000d621d7d2-00000000d623857e-132929981313060003-132929997997294276.xz
    29.03.2022  07:40             7.732 FileDataReads-00000000d624a2e0-00000000d62809fa-132930018919200082-132930052686768364.xz
    29.03.2022  08:45            46.008 FileDataReads-00000000d628524b-00000000d62b0dbd-132930058071557532-132930095522940349.xz
    29.03.2022  09:45           222.464 FileDataReads-00000000d62b11be-00000000d62dbfcc-132930095584624800-132930131554631917.xz
    29.03.2022  10:50           306.380 FileDataReads-00000000d62dc9ed-00000000d630a0c8-132930131705470474-132930170552024289.xz
    29.03.2022  11:50           265.352 FileDataReads-00000000d630a0cd-00000000d6336cdc-132930170558430791-132930206555596990.xz
    29.03.2022  12:50           163.076 FileDataReads-00000000d6336ced-00000000d63635dd-132930206559516080-132930242558325434.xz
    29.03.2022  13:50           172.968 FileDataReads-00000000d636364c-00000000d638d7d3-132930242572310761-132930278562303786.xz
    29.03.2022  14:55           232.820 FileDataReads-00000000d638d7f6-00000000d63c072b-132930278616662977-132930317547187447.xz
    29.03.2022  15:55           133.204 FileDataReads-00000000d63c073c-00000000d63ea415-132930317579195159-132930353385265223.xz
    29.03.2022  16:56           281.672 FileDataReads-00000000d63ea70e-00000000d6444252-132930353653443748-132930390629105295.xz
    29.03.2022  17:58           199.212 FileDataReads-00000000d6444254-00000000d6480259-132930390631449090-132930427809769003.xz
    29.03.2022  18:58            30.120 FileDataReads-00000000d64802f8-00000000d64a7ade-132930427863888999-132930463850712688.xz
    29.03.2022  20:53             4.984 FileDataReads-00000000d64a7f45-00000000d64ce251-132930464042416440-132930499655385313.xz
    29.03.2022  21:58             7.012 FileDataReads-00000000d64f43ce-00000000d6512960-132930535528014729-132930566951930550.xz
    
    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads>dir | find "30.03.2022"
    30.03.2022  03:47             1.248 FileDataReads-00000000d651d245-00000000d653fa9b-132930574195682577-132930606025722553.xz
    30.03.2022  05:44               612 FileDataReads-00000000d6602314-00000000d6602316-132930783720573974-132930783720573974.xz
    30.03.2022  06:44             3.692 FileDataReads-00000000d6659c6c-00000000d667f9f2-132930851174435539-132930887023648277.xz
    30.03.2022  07:44             5.340 FileDataReads-00000000d667fef2-00000000d66a1987-132930887181663668-132930917464385505.xz
    30.03.2022  08:49            64.896 FileDataReads-00000000d66a9497-00000000d66d5b58-132930923777137507-132930962063866865.xz
    30.03.2022  09:54           477.960 FileDataReads-00000000d66d5d2b-00000000d670a239-132930962199167659-132931001111441228.xz
    30.03.2022  10:54           111.384 FileDataReads-00000000d670a23a-00000000d67335e7-132931001113641371-132931037046277566.xz
    30.03.2022  11:54           125.864 FileDataReads-00000000d673369a-00000000d675dba7-132931037144739357-132931073018894461.xz
    30.03.2022  12:59           167.052 FileDataReads-00000000d675dd15-00000000d678b24b-132931073151585945-132931112103796288.xz
    30.03.2022  14:04           194.120 FileDataReads-00000000d678b2b0-00000000d67b7f02-132931112157461320-132931151118947701.xz
    30.03.2022  15:07           180.432 FileDataReads-00000000d67b7f05-00000000d67e99b5-132931151127541976-132931189077029769.xz
    30.03.2022  16:09            89.948 FileDataReads-00000000d67e9a2c-00000000d6826854-132931189080947745-132931226123885530.xz
    30.03.2022  17:14            68.632 FileDataReads-00000000d6826cb6-00000000d6855208-132931226300210229-132931265146154925.xz
    30.03.2022  18:14            46.428 FileDataReads-00000000d68553ee-00000000d687e3dd-132931265254635974-132931301036711106.xz
    30.03.2022  19:14            72.512 FileDataReads-00000000d687ed77-00000000d68a5411-132931301498877353-132931336136948868.xz
    30.03.2022  20:19            10.116 FileDataReads-00000000d68a6711-00000000d68cf145-132931337686409550-132931372749106508.xz
    30.03.2022  21:24             4.472 FileDataReads-00000000d68d5100-00000000d68f6d7d-132931378892542195-132931407330059553.xz
    30.03.2022  22:34             1.000 FileDataReads-00000000d68ff020-00000000d6905b85-132931416883295243-132931424774534873.xz
    30.03.2022  23:59               872 FileDataReads-00000000d692a86a-00000000d694fc92-132931459327722308-132931493213689424.xz
    
    C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads>dir | find "31.03.2022"
    31.03.2022  01:47             6.564 FileDataReads-00000000d69637d7-00000000d696a0f9-132931508196173048-132931515275689791.xz
    31.03.2022  05:19             1.196 FileDataReads-00000000d69af460-00000000d69d9c5b-132931575722326515-132931611574230965.xz
    31.03.2022  06:19             1.052 FileDataReads-00000000d6a4a68c-00000000d6a64fb7-132931701474928358-132931724654323204.xz
    31.03.2022  07:19             2.204 FileDataReads-00000000d6a703b5-00000000d6a943ae-132931737807599867-132931770480987390.xz
    31.03.2022  08:24            14.108 FileDataReads-00000000d6a97575-00000000d6abe914-132931774084560947-132931811130611997.xz
    31.03.2022  09:29           192.376 FileDataReads-00000000d6abf911-00000000d6aea9a3-132931811994816827-132931850242545448.xz
    31.03.2022  10:34           273.948 FileDataReads-00000000d6aeab40-00000000d6b17b27-132931850309540765-132931889235828445.xz
    31.03.2022  11:34           243.780 FileDataReads-00000000d6b17b94-00000000d6b4322e-132931889254469983-132931925250907135.xz
    31.03.2022  12:39           169.504 FileDataReads-00000000d6b4323b-00000000d6b74066-132931925263161257-132931964216671152.xz
    31.03.2022  13:42           198.692 FileDataReads-00000000d6b740c9-00000000d6ba6e29-132931964267996530-132932001750634983.xz
    31.03.2022  14:44           116.764 FileDataReads-00000000d6ba7ba0-00000000d6be761a-132932002345223187-132932039655240051.xz
    31.03.2022  15:49         1.722.012 FileDataReads-00000000d6be7c63-00000000d6c21d27-132932039761155774-132932078709224015.xz
    31.03.2022  16:49           341.440 FileDataReads-00000000d6c21e7b-00000000d6c56d33-132932078736876580-132932114474997140.xz
    31.03.2022  17:51           928.004 FileDataReads-00000000d6c5728c-00000000d6cb14dc-132932114893743464-132932151712443462.xz
    31.03.2022  18:56           137.808 FileDataReads-00000000d6cb1bf0-00000000d6cde6a9-132932151786350324-132932190748707945.xz
    31.03.2022  19:56            82.400 FileDataReads-00000000d6cde6ac-00000000d6d03a4d-132932190769045224-132932226758930033.xz
    31.03.2022  20:56            92.172 FileDataReads-00000000d6d03a5d-00000000d6d2dbee-132932226779322840-132932262769420862.xz
    31.03.2022  21:56            77.104 FileDataReads-00000000d6d2dbf9-00000000d6d52ffd-132932262789866481-132932298751200028.xz
    31.03.2022  22:56            44.784 FileDataReads-00000000d6d53013-00000000d6d791eb-132932298792852905-132932334756629253.xz

    "Value","Count"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000d640acae-00000000d64336ba-132930383320048756-132930384658558137.xz","17"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000d645f316-00000000d645f338-132930419321316176-132930419321490243.xz","17"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryChanges\FileBinaryChanges-00000000d64995bd-00000000d64995df-132930455322444124-132930455322600210.xz","17"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000d63e0156-00000000d6414a64-132930347769879292-132930383972888843.xz","32"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000d6419da4-00000000d6462a13-132930384113492746-132930420492579310.xz","28"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileBinaryReads\FileBinaryReads-00000000d64630b6-00000000d649d98b-132930420984228810-132930457698140290.xz","19"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-00000000d63ed134-00000000d6447237-132930356621884923-132930393370838740.xz","18"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataChanges\FileDataChanges-00000000d64486d2-00000000d6482019-132930394573454676-132930429978684584.xz","18"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads\FileDataReads-00000000d63ea70e-00000000d6444252-132930353653443748-132930390629105295.xz","34"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileDataReads\FileDataReads-00000000d6444254-00000000d6480259-132930390631449090-132930427809769003.xz","30"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000d63e2b43-00000000d642f227-132930350566220726-132930384555012201.xz","347"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000d642f228-00000000d6462af3-132930384555012201-132930420622135119.xz","64"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherChanges\FileOtherChanges-00000000d6462b24-00000000d649da8b-132930420679217011-132930457848741916.xz","33"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000d63d27b5-00000000d63fb1d6-132930335566174489-132930371563461079.xz","54"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000d63fb1db-00000000d645552f-132930371568340174-132930408628778314.xz","66"
    "C:\ProgramData\Sophos\Endpoint Defense\Data\Event Journals\SophosED\FileOtherReads\FileOtherReads-00000000d645553c-00000000d649085c-132930408648863603-132930445837701901.xz","57"
    

  • Thanks. Looking at:
    https://docs.sophos.com/central/References/schemas/index.html?schema=ld_schema

    for the table of interest here: sophos_file_journal, it has the subject column:

    subject text The subject of the file event can be:
    FileBinaryChanges,
    FileBinaryReads,
    FileDataChanges,
    FileDataReads,
    FileOtherChanges,
    FileOtherRead

    Where binary is defined as a PE file on the machine, data is of specific extension type set: doc, docx, xls,
    xlsx, ppt, pptx, pdf, rtf, wpd and other is any other type of extension. Supported with multiple equals
    operators for known subjects.

    So we could as a test, limit the query to a specific subject or possibly subjects and keep the time window I suspect. E.g.

    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.subject = 'FileBinaryChanges'
        AND file_journal.pathname LIKE '$$file_path$$'
        AND file_journal.time > $$start_time$$
        AND file_journal.time < $$end_time$$
    ORDER BY file_journal.time DESC

    Maybe try just all the changes, just the WHERE clause and below:

    WHERE
        file_journal.subject in( 'FileBinaryChanges','FileDataChanges','FileOtherChanges')
        AND file_journal.pathname LIKE '$$file_path$$'
        AND file_journal.time > $$start_time$$
        AND file_journal.time < $$end_time$$
    ORDER BY file_journal.time DESC

    ...but I suspect multiple could still be an issue?

    You could try 6 queries, one for each and see if any return and which ones do not.  That would tell us something.

    You could potentially go back to the original query for the extra decoration and add in the subject filter if you're interested in just FileDataReads for example.  I suppose it depends on what you're looking for.

    I heard at some point there might be a change coming to run it at lower priority so the CPU is kept down but would take longer.  This could help prevent the CPU trigger.

  • Hi ,

    I really appreciate your support and knowledge here. Thank you very much!! But currently I cannot put more time into this. Tech support is also holding the case at low level "works as designed" #05100415.

    For PM of that product I can say:

    This product is a "fail" with this limitations*. We're using XDR Advanced + MTR on our servers and putting a lot of coins in your wallet. On the other hand, the product is full of limitations, that you only notice when you need to use it. Nobody would tell you before you buy. It does not work when your machines meet the System specs and the software has no way to work around. This is very disappointing.

    Will speak to our Sales rep about the issue and rethink of using XDR Adv. on our servers.

    *Limitations:
    https://support.sophos.com/support/s/article/KB-000034920?language=en_US

    https://support.sophos.com/support/s/article/KB-000039257?language=en_US#FurtherInformation

    where at least this is not true, as can be seen in the thread you're reading here.

  • As expected, it is currently not possible to query file access on file servers with 2 CPU due to strict process termination after 9 seconds when the process SophosOsqueryExtension.exe uses more than 30% CPU.

    What do you do when your trying to find out which files have been read / copied on a file server by malware?

    The recommendation was to use Data Lake Queries.

    As far as I know, file reads are not logged there and there is no default query for that.

  • I think you will have to reduce the timeframe initially and possibly export to CSV each report, beyond that, maybe bring the  subject column into play when querying the sophos_file_journal table as I assume it will then return with one "subject". I suspect the FileDataReads subject is the problem, as you mention the files are a lot larger.  

  • I think you will have to reduce the timeframe initially

    yes, that's currently the only "workaround". I can select 2 hours on that server then it works. If I select 3 hours, it get's aborted.

    Not a scenario if you need to span a query over the whole XDR Adv timeframe.

    That live discover feature needs a way to disable that watchdog. It stops your show and significantly reduces your ROI.