Query Details
Arc Compare MDE
Query
# Azure Arc - Compare Azure Arc Computer Resources with Defender for Endpoint Resources
## Query Information
### Description
Use the below query to compare the Azure Arc Server Inventory with the Defender for Endpoint resources.
#### References
### Author
- **Alex Verboon**
## Defender XDR
```kql
let ServerOS = dynamic(["Linux","WindowsServer2025","WindowsServer2022","WindowsServer2019","WindowsServer2016","WindowsServer2012","WindowsServer2012R2"]);
let arcservers = arg("").Resources
| where type == 'microsoft.hybridcompute/machines'
| project
ArcComputerName = tolower(tostring(properties.osProfile.computerName)),
ArcLocation = location,
resourceGroup,
subscriptionId,
ArcOSName = tostring(properties.osName);
DeviceInfo
| where OSPlatform in(ServerOS)
| summarize arg_max(TimeGenerated,*) by DeviceName
| project
MDEDeviceName = tolower(split(DeviceName,".")[0]),
MDEOSPlatform = OSPlatform,
OnboardingStatus
| join kind=leftouter hint.remote=left (arcservers)
on $left. MDEDeviceName == $right.ArcComputerName
| project ArcComputerName, MDEDeviceName, ArcOSName, MDEOSPlatform, OnboardingStatus, ArcLocation, resourceGroup, subscriptionId
```
Explanation
This query is designed to compare the list of servers managed by Azure Arc with the resources managed by Microsoft Defender for Endpoint (MDE). Here's a simplified breakdown of what the query does:
-
Define Server Operating Systems: It starts by defining a list of server operating systems that are of interest, including various versions of Windows Server and Linux.
-
Retrieve Azure Arc Servers: It fetches a list of resources from Azure Arc that are identified as hybrid compute machines. For each machine, it extracts details such as the computer name, location, resource group, subscription ID, and operating system name.
-
Retrieve Defender for Endpoint Devices: It then retrieves information about devices from Defender for Endpoint, filtering for those whose operating system platform matches the list defined earlier. It summarizes this data to get the most recent information for each device.
-
Normalize and Compare: The query normalizes the computer names from both Azure Arc and Defender for Endpoint to lowercase and compares them to find matches. It uses a left outer join to ensure all devices from Defender for Endpoint are included, even if they don't have a corresponding entry in Azure Arc.
-
Output: Finally, it projects a list of relevant fields, including the computer names from both systems, operating system details, onboarding status, location, resource group, and subscription ID, allowing for easy comparison of the two sets of resources.
In essence, this query helps identify discrepancies or overlaps between the servers managed by Azure Arc and those monitored by Defender for Endpoint.
Details
Alex Verboon
Released: August 22, 2025
Tables
Keywords
Operators