Resource Graph and Log Analytics Join March 25, 2025 by admin

This query correlates Azure Virtual Machines (VMs) with their security protection status, focusing on VMs with a ProtectionStatusRank of 450 (indicating a potential security concern).

    resourceGroup,
    identity_principalId=identity.principalId,
    ManagedBy=tags.ManagedBy,
    test=tolower(id)
| join kind=rightouter (
    ProtectionStatus 
    | where ProtectionStatusRank == '450' 
    | project
        _ResourceId,
        DeviceName,
        OSName,
        ThreatStatusRank,
        ThreatStatus,
        ProtectionStatusRank,
        ProtectionStatus,
        TypeofProtection,
        Computer,
        test=tolower(_ResourceId)
    )
    on $left.test == $right.test
| where ManagedBy != "NotMonitored"
| project
    _ResourceId,
    DeviceName,
    OSName,
    ThreatStatusRank,
    ThreatStatus,
    ProtectionStatusRank,
    ProtectionStatus,
    TypeofProtection,
    Computer,
    ManagedBy

Key Steps:

1. Retrieve Azure VMs → Filters resources of type microsoft.compute/virtualmachines.
2. Extract Relevant VM Data → Captures VM name, resource group, managed identity, and tags.
3. Join with Security Protection Data → Matches VMs with security data from ProtectionStatus, focusing on rank 450.
4. Filter Out Unmonitored VMs → Excludes VMs labeled as "NotMonitored".
5. Project Final Output → Displays VM details, security risk levels, and protection status.

Purpose:

This query helps in identifying security-vulnerable VMs while ensuring only actively monitored resources are included. 🚀