query in resource graph

Question

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

Answer 1

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.

Answer 2

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!

Answer 3

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!

Answer 4

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!