Security

 View Only

  • 1.  Microsoft Defender ATP Sync Issue

    Posted Apr 28, 2025 04:55 AM

    Dear Community,

    I have Intergrated defender ATP with the ClearPass and it is getting below error

    [2025-04-28T10:57:47.277] [INFO] MSFT Defender ATP - Got 11 devices to process (page 1)...
    [2025-04-28T10:57:47.300] [ERROR] MSFT Defender ATP - Request failed with status code 400
    [2025-04-28T10:57:47.301] [ERROR] MSFT Defender ATP - { error: 
       { code: 'BadRequest',
         message: '\'where\' operator: Failed to resolve table or column expression named \'DeviceInfo\'. Fix semantic errors in your query.',
         target: '|5d845edd-426c52b9ef5439e7.1.1.' } }
    [2025-04-28T10:57:47.302] [WARN] MSFT Defender ATP - No results returned from Advanced Hunting query.
    [2025-04-28T10:57:47.302] [INFO] MSFT Defender ATP - Loaded 11 devices from the device list API and 0 from the Advanced Hunting API.
    [2025-04-28T10:57:47.306] [WARN] MSFT Defender ATP - No mac addresses were found for the device (undefined). Skipping.
    [2025-04-28T10:57:47.307] [WARN] MSFT Defender ATP - No mac addresses were found for the device (undefined). Skipping.

    I run the debug from the extension itself below is the out of that

    [2025-04-28T11:50:44.308] [WARN] MSFT Defender ATP - No mac addresses were found for the device (undefined). Skipping.
[2025-04-28T11:50:44.308] [DEBUG] MSFT Defender ATP - { MachineId: 'f8825e7f23b1630d0cfe8e497b93d8c7fb382921',
  Details: 
   { id: 'f8825e7f23b1630d0cfe8e497b93d8c7fb382921',
     mergedIntoMachineId: null,
     isPotentialDuplication: false,
     isExcluded: false,
     exclusionReason: null,
     computerDnsName: 'hohr-shereesn',
     firstSeen: '2024-09-05T09:38:37.9370456Z',
     lastSeen: '2025-04-28T08:11:27.5954438Z',
     osPlatform: 'Windows11',
     osVersion: null,
     osProcessor: 'x64',
     version: '23H2',
     lastIpAddress: '192.168.1.14',
     agentVersion: '10.8760.22631.4317',
     osBuild: 22631,
     healthStatus: 'Active',
     deviceValue: 'Normal',
     rbacGroupId: 0,
     rbacGroupName: null,
     riskScore: 'None',
     exposureLevel: 'High',
     isAadJoined: true,
     aadDeviceId: '6ae23c71-2edf-4c4a-8019-f5aadfc882c6',
     machineTags: [],
     onboardingStatus: 'Onboarded',
     osArchitecture: '64-bit',
     managedBy: 'Intune',
     managedByStatus: 'Unknown',
     ipAddresses: 
      [ [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object],
        [Object] ],
     vmMetadata: null },
  MACAddresses: [],
  Unknown: 0,
  Info: 0,
  Low: 0,
  Medium: 0,
  High: 0 }
[2025-04-28T11:50:44.309] [INFO] MSFT Defender ATP - Full sync completed. Processing Results: Added 0 - Updated: 0 - Errors: 57 - Time Taken: 1.710 Seconds
[2025-04-28T11:50:44.311] [INFO] MSFT Defender ATP - The sync on start full update has completed.

    Any Idea about this issue?

     



    ------------------------------
    Tharindu Pamoda
    ------------------------------


  • 2.  RE: Microsoft Defender ATP Sync Issue
    Best Answer

    Posted Apr 28, 2025 05:12 AM

    'where' operator: Failed to resolve table or column expression named 'DeviceInfo'. Fix semantic errors in your query.

    This to me looks like your Advanced Hunting Query inside Defender ATP (MDE) is using a reference to a table called DeviceInfo, but that table does not exist (at least not in the schema your tenant/environment provides). That's why you are getting HTTP status code 400 (Bad Request) - because the query itself is invalid.

    I suppose that there is no MAC addresses retrieved (because the hunting query is supposed to pull MACs based on the device info). ClearPass cannot link devices without MAC address. So the integration processes 0 devices from Advanced Hunting, even though it gets 11 from the Device API.

    This is the reason why you see:  "No MAC addresses were found for the device (undefined). Skipping."

    You need to fix the Hunting Query the extension is using. Most probably you should query from the proper tables like:
    DeviceNetworkInfo
    DeviceTvmInfo
    DeviceInfoEvents
    DeviceRegistryEvents
    or whatever matches your needs or use-case



    ------------------------------
    Shpat | ACEP | ACMP | ACCP | ACDP
    Just an Aruba enthusiast and contributor by cases
    If you find my comment helpful, KUDOS are appreciated.
    ------------------------------

    Original Message


  • 3.  RE: Microsoft Defender ATP Sync Issue

    Posted Apr 28, 2025 05:26 AM

    Hi Shpat,

    Thanks for the quick response, can you give me any guideline that how to edit the Hunting Query 



    ------------------------------
    Tharindu Pamoda
    ------------------------------

    Original Message


  • 4.  RE: Microsoft Defender ATP Sync Issue

    Posted Apr 28, 2025 05:54 AM

    I am not very familiar with the interface but what i checked on Google, it should be on behalf of "Advanced Hunting" (under Threat Analytics or Hunting section).

    Then there should be the section to build queries and you can build something like:
    DeviceNetworkInfo
    | project DeviceName, MacAddress

    Or if you need to Match IP address of Clearpass it should be something like:
    DeviceNetworkInfo
    | where IPAddresses contains "192.168.1.14"
    | project DeviceName, MacAddress, IPAddresses

    Also, you can try this query:
    DeviceNetworkInfo
    | where isnotempty(MacAddress)
    | project DeviceName, MacAddress

    But you should always consider to do such queries and modification with someone who is familiar on administrating ATP because it might get complicated.



    ------------------------------
    Shpat | ACEP | ACMP | ACCP | ACDP
    Just an Aruba enthusiast and contributor by cases
    If you find my comment helpful, KUDOS are appreciated.
    ------------------------------

    Original Message


Related Content