Azure VM MDE Onboarding Status

MDE Azure VM MDE Onboarding

Query

// Configuration: Azure subscriptions to include
let vSubscriptionIds = dynamic([
    "00000000-0000-0000-0000-000000000000"
]);
// Azure VM inventory from Exposure Graph
ExposureGraphNodes
| where NodeLabel =~ "microsoft.compute/virtualmachines"
| where Categories has "virtual_machine"
// Extract Azure resource information
| extend AzureResourceId = tostring(EntityIds[0].id)
| extend
    SubscriptionId = extract(@"(?i)/subscriptions/([^/]+)", 1, AzureResourceId),
    ResourceGroup = extract(@"(?i)/resourcegroups/([^/]+)", 1, AzureResourceId)
// Filter subscriptions
| where SubscriptionId in~ (vSubscriptionIds)
// Extract VM attributes
| extend
    VMName = tostring(split(AzureResourceId, "/")[-1]),
    DeviceName = tostring(NodeProperties.rawData.deviceName),
    LastSeen = todatetime(NodeProperties.rawData.lastSeen),
    OnboardingStatus = tostring(NodeProperties.rawData.onboardingStatus),
    OSPlatform = tostring(NodeProperties.rawData.osPlatformFriendlyName),
    FirstSeenByInventory = todatetime(NodeProperties.rawData.firstSeenByInventory),
    DeviceGroup = tostring(NodeProperties.rawData.machineGroup),
    SensorHealthState = tostring(NodeProperties.rawData.sensorHealthState)
// Normalize hostname (remove DNS suffix)
| extend
    DeviceHostName = tolower(tostring(split(DeviceName, ".")[0]))
// Final inventory
| project
    SubscriptionId,
    ResourceGroup,
    VMName,
    DeviceName,
    DeviceHostName,
    OSPlatform,
    OnboardingStatus,
    SensorHealthState,
    DeviceGroup,
    FirstSeenByInventory,
    LastSeen,
    AzureResourceId 
| order by VMName asc

About this query

Explanation

This KQL query is designed to check the onboarding status and sensor health of Azure virtual machines (VMs) with Microsoft Defender for Endpoint. Here's a simple breakdown of what the query does:

Configuration: It starts by specifying which Azure subscription IDs to include in the check. You can customize this list with your own subscription IDs.
Data Source: The query pulls data from the ExposureGraphNodes table, which is part of Microsoft Defender XDR.
Filtering: It filters the data to only include nodes labeled as Azure virtual machines.
Extracting Information: For each VM, it extracts important details such as:
- Azure Resource ID
- Subscription ID
- Resource Group
- VM Name
- Device Name
- Last Seen Date
- Onboarding Status
- Operating System Platform
- First Seen Date by Inventory
- Device Group
- Sensor Health State
Hostname Normalization: It normalizes the device hostname by removing any DNS suffix.
Output: The query then organizes and displays this information in a table, sorted by VM Name.

The purpose of this query is to help you review which Azure VMs are properly onboarded to Microsoft Defender for Endpoint and to check the health status of their sensors. This is important for ensuring that your VMs have adequate endpoint visibility and protection.