0

I am new the KQL on Azure resource graph explorer kql queries, I have a requirement to get the SQLVM with associated disk. I wrote this query and getting below error.

Any help is highly appreciated.

Query:

resources | where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines" | project id, vmid=properties.virtualMachineResourceId, name, img=properties.sqlImageOffer, sqlImgOffered=properties.sqlImageSku, sqlLicense=properties.sqlServerLicenseType | join (resources | where type == "microsoft.compute/disks" | project managedBy, diskSizeGB=properties.diskSizeGB, tier=properties.tier, diskIOPSRW=properties.diskIOPSReadWrite, diskMBpsReadWrite=properties.diskMBpsReadWrite, diskState=properties.diskState) on $left.vmid == $right.managedBy

Output:

Details:

Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) join key 'vmid' is of a 'dynamic' type. Please use an explicit cast using extend operator in the join legs (for example, '... | extend vmid = tostring(vmid) | join (... | extend vmid = tostring(vmid)) on vmid') as join on a 'dynamic' type is not supported. (Code:Default)

Please help in solving this error.

Saideep Arikontham
  • 5,558
  • 2
  • 3
  • 11
Shamsul Hassan
  • 1
  • 1

1 Answers1

0

Query posted in question has issue with properties.virtualMachineResourceId as properties contains Json data. When getting virtualMachineResourceId from properties properties.virtualMachineResourceId is evaluating as object.

I have used tostring() to convert the returned value to string and it worked for me.

Below is modified query:-

resources
| where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines"
| project id,
//used tostring() to convert properties.virtualMachineResourceId value to string 
vmid=tostring(properties.virtualMachineResourceId), name,
 img=properties.sqlImageOffer,
 sqlImgOffered=properties.sqlImageSku,
 sqlLicense=properties.sqlServerLicenseType
| join (resources | where type == "microsoft.compute/disks"
| project managedBy,
diskSizeGB=properties.diskSizeGB,
tier=properties.tier,
diskIOPSRW=properties.diskIOPSReadWrite,diskMBpsReadWrite=properties.diskMBpsReadWrite,
diskState=properties.diskState) 
on $left.vmid == $right.managedBy
RitikaNalwaya
  • 529
  • 1
  • 3