0

I am trying to list all my Azure virtual machines which have a Standard_LRS disk type and I wrote this KQL query in Azure Resource Graph Explorer to get this:

resources
| where type == "microsoft.compute/virtualmachines" and subscriptionId == "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx"
| extend id = tostring(properties.storageProfile.osDisk.managedDisk.id)
| project vm_name=name, id
| join (resources 
| where type == "microsoft.compute/disks" and subscriptionId == "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx" and sku.name == "Standard_LRS"
| project name, id, type=sku.name) on id
| project vm_name, disk_id=id, type

I have 9 VMs and 7 out of 9 are running Standard_LRS managed disks but the result from this query shows me only 4 of them. Not sure what I am doing wrong. If I run the two queries combined above independently, each gives me expected results but joining them is giving me trouble.

any clue?

esa
  • 87
  • 3
  • 9

1 Answers1

0

found the root cause. The disk id needs to be lower-cased because its formatting is never consistent and this messes up the join operation. here is the same query with some change which makes it work:

resources
| where type == "microsoft.compute/virtualmachines" 
| extend id = tolower(tostring(properties.storageProfile.osDisk.managedDisk.id))
| project vm_name=name, id
| join (resources | extend id = tolower(id)
| where type == "microsoft.compute/disks" and sku.name == "Standard_LRS"
| project name, id, type=sku.name) on id
| project vm_name, disk_id=id, type
esa
  • 87
  • 3
  • 9