KQL query join error

Question

Hi All,

The below query works in Azure Graph Explorer.

However, when I include the code snippet in PowerShell, I receive the following error.

search-azgraph : {

  "error": {
**"code": "BadRequest",**

**"message": "Please provide below info when asking for support: timestamp =** 
2025-05-17T00:04:53.2184660Z, correlationId = 5e32b11b-dce0-47f3-9a11-7311a0e6b2ba.",
**"details": [**

  **{**

    **"code": "InvalidQuery",**

    **"message": "Query is invalid. Please refer to the documentation for the Azure** 
  **},**

  **{**

    **"code": "ParserFailure",**

    **"message": "ParserFailure",**

    **"line": 17,**

    **"characterPositionInLine": 8,**

    **"token": "."**
At line:23 char:21
Resource Graph service and fix the error before retrying."
 

This KQL query works ok in Azure Portal.

resourcechanges  
| extend resourceType = tostring(properties.targetResourceType),  targetResourceId = tostring(properties.targetResourceId),  changeType = tostring(properties.changeType)
| where resourceType == "microsoft.compute/virtualmachines" and changeType == "Create"
| project targetResourceId
| join kind=leftouter (  
	resources   
	| where type == "microsoft.compute/virtualmachines"   
	| extend OsName = tostring(properties.extended.instanceView.osName),   
	offer = tostring(properties.storageProfile.imageReference.offer),   
	publisher = tostring(properties.storageProfile.imageReference.publisher),   
	osType = tostring(properties.storageProfile.osDisk.osType),   
	licenseType = tostring(properties.licenseType),   
	extendedOsName = tostring(properties.extended.instanceView.osName)   
| project resId = ['id'], name, offer, OsName, subscriptionId, resourceGroup, extendedOsName, licenseType, publisher, osType   ) 
on $left.targetResourceId == $right.resId

Powershell Script.

$vmQuery = @"
resourcechanges  
| extend resourceType = tostring(properties.targetResourceType),  
targetResourceId = tostring(properties.targetResourceId),  
changeType = tostring(properties.changeType)
| where resourceType == "microsoft.compute/virtualmachines" and changeType == "Create"
| project targetResourceId
| join kind=leftouter (
  resources
   | where type == "microsoft.compute/virtualmachines"
   | extend OsName = tostring(properties.extended.instanceView.osName),
   offer = tostring(properties.storageProfile.imageReference.offer),
   publisher = tostring(properties.storageProfile.imageReference.publisher),
   osType = tostring(properties.storageProfile.osDisk.osType),
   licenseType = tostring(properties.licenseType),
   extendedOsName = tostring(properties.extended.instanceView.osName)
   | project resId = ['id'], name, offer, OsName, subscriptionId, resourceGroup, extendedOsName, licenseType, publisher, osType
   ) on $left.targetResourceId == $right.resId
"@


     $vm_Col = do{
        $outputvm = search-azgraph -query $vmQuery -skiptoken $outputvm.skiptoken 
        $outputvm
    }until($null -eq $outputvm.skiptoken)

Answer 1

Hello Madhu Rao,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having KQL query join error.

This is a syntax issue that PowerShell doesn't handle well, even though the same query works in Azure Resource Graph Explorer. Check the below cleaned for PowerShell-ready script:

$vmQuery = @"
resourcechanges 
| extend resourceType = tostring(properties.targetResourceType), 
         targetResourceId = tostring(properties.targetResourceId), 
         changeType = tostring(properties.changeType)
| where resourceType == "microsoft.compute/virtualmachines" and changeType == "Create"
| project targetResourceId
| join kind=leftouter (
    resources
    | where type == "microsoft.compute/virtualmachines"
    | extend OsName = tostring(properties.extended.instanceView.osName),
             offer = tostring(properties.storageProfile.imageReference.offer),
             publisher = tostring(properties.storageProfile.imageReference.publisher),
             osType = tostring(properties.storageProfile.osDisk.osType),
             licenseType = tostring(properties.licenseType),
             extendedOsName = tostring(properties.extended.instanceView.osName)
    | project resId = ['id'], name, offer, OsName, subscriptionId, resourceGroup, extendedOsName, licenseType, publisher, osType
) on `$left.targetResourceId == `$right.resId
"@
$vm_Col = do{
    $outputvm = search-azgraph -query $vmQuery -skiptoken $outputvm.skiptoken 
    $outputvm
}until($null -eq $outputvm.skiptoken)

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.