query in resource graph

Yue Ma 20 Reputation points
2024-03-13T00:06:10.9266667+00:00

I'd like to update the following query, so that instead of listing all the extensions of the VM, I'd like to list only VMs that include extension 1, but not extension 2

Resources
| where type == 'microsoft.compute/virtualmachines'
| extend
    JoinID = toupper(id),
    OSName = tostring(properties.osProfile.computerName),
    OSType = tostring(properties.storageProfile.osDisk.osType),
    VMSize = tostring(properties.hardwareProfile.vmSize),
    departmentTag = tostring(tags['Department'])
| join kind=leftouter(
    Resources
    | where type == 'microsoft.compute/virtualmachines/extensions'
    | extend 
        VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
        ExtensionName = name
) on $left.JoinID == $right.VMId
| summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize, name, resourceGroup, departmentTag
| order by tolower(OSName) asc
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,092 questions
{count} votes

Accepted answer
  1. Stanislav Zhelyazkov 20,781 Reputation points MVP
    2024-03-13T07:03:19.9833333+00:00

    Hi,

    I think this is the query you are looking for. Example is to list all VMs that have BGInfo and do not have IaaSAntimalware extensions.

    
    resources
    | where type == 'microsoft.compute/virtualmachines'
    | extend
        JoinID = toupper(id),
        OSName = tostring(properties.osProfile.computerName),
        OSType = tostring(properties.storageProfile.osDisk.osType),
        VMSize = tostring(properties.hardwareProfile.vmSize),
        departmentTag = tostring(tags['Department'])
    | join kind=inner(
        resources
        | where type == 'microsoft.compute/virtualmachines/extensions'
        | where name =~ 'BGInfo' and name !~ 'IaaSAntimalware'
        | extend 
            VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
            ExtensionName = name
    ) on $left.JoinID == $right.VMId
    | summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize, name, resourceGroup, departmentTag
    | order by tolower(OSName) asc
    

    Updated query:

    resources
    | where type == 'microsoft.compute/virtualmachines'
    | extend
        JoinID = toupper(id),
        OSName = tostring(properties.osProfile.computerName),
        OSType = tostring(properties.storageProfile.osDisk.osType),
        VMSize = tostring(properties.hardwareProfile.vmSize),
        departmentTag = tostring(tags['Department'])
    | join kind=inner(
        resources
        | where type == 'microsoft.compute/virtualmachines/extensions'
        | where name =~ 'BGInfo' or name =~ 'IaaSAntimalware'
        | extend 
            VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
            ExtensionName = name
    ) on $left.JoinID == $right.VMId
    | summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize, name, resourceGroup, departmentTag
    | where Extensions notcontains 'IaaSAntimalware'
    | order by tolower(OSName) asc
    

    Third and last update:

    resources
    | where type == 'microsoft.compute/virtualmachines'
    | extend
        JoinID = toupper(id),
        OSName = tostring(properties.osProfile.computerName),
        OSType = tostring(properties.storageProfile.osDisk.osType),
        VMSize = tostring(properties.hardwareProfile.vmSize),
        departmentTag = tostring(tags['Department'])
    | join kind=inner(
        resources
        | where type == 'microsoft.compute/virtualmachines/extensions'
        | extend 
            VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
            ExtensionName = name
    ) on $left.JoinID == $right.VMId
    | summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize, name, resourceGroup, departmentTag
    | where Extensions notcontains 'IaaSAntimalware'
    | where Extensions contains 'BGInfo'
    | order by tolower(OSName) asc
    

    Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Yue Ma 20 Reputation points
    2024-03-13T18:21:23.52+00:00

    thank you for the quick response! with your example, the EXTENSIONS column does list VMs that include extension 1, but not extension 2. HOWEVER, the column now only list partial information, for example, if a VM has extension 1, no extension 2, but extension 3, the EXTENSIONS column only lists extension 1, BUT NOT extension 3. Can help with an updated query that can include extension 3? Thank you!

    0 comments No comments

  2. Yue Ma 20 Reputation points
    2024-03-13T19:26:39.84+00:00

    I just double checked your updated code and found it has a more serious problem: it will list VMs that have both extension 1 AND extension 2 as well, but under the EXTENSIONS column: it just list extension 1. Let me explain my requirement in more details:

    VM1 has extension 1, extension 2 and extension 3

    VM2 has extension 2 and extension 4

    VM3 has extension 1 and extension 5

    VM4 has extension 3 and extension 4

    VM5 has extension 1 and extension 7

    with this query, the result should be VM3 and VM5 with all the extension names, OSName, OSType, VMSize, name, resourceGroup, departmentTag ...(list only VMs that include extension 1, but not extension 2)

    Thank you!

    0 comments No comments

  3. Yue Ma 20 Reputation points
    2024-03-15T16:37:50.8366667+00:00

    Thank you for getting back to me. I used the code I posted on March 12th as the benchmark. It lists all the VMs with all extensions. I use the following command in excel to count the # of VMs that include extension 1, but not extension 2

     =COUNTIFS(H2:H700, "extension1", H2:H700, "<>extension2")

    column H is the output column for EXTENSIONS, in this example, there are 699 VMs.

    Your updated query DOES NOT list only VMs with just extension 1, but no extension 2. Again, here are my requirements - list only VMs that include extension 1, but not extension 2, for example, assuming there are 5 total VMs:

    VM1 has extension 1, extension 2 and extension 3

    VM2 has extension 2 and extension 4

    VM3 has extension 1 and extension 5

    VM4 has extension 3 and extension 4

    VM5 has extension 1 and extension 7

    with the updated query (still looking for MSFT for help), the result should be VM3 and VM5 with all the extension names, OSName, OSType, VMSize, name, resourceGroup, departmentTag …

    Thank you and look forward to hearing from you!