Search code examples
azure-resource-graphazure-advisor

trying to get the resources.name joined to advisorresources

Using Azure Resource Graph Queries and a kusto query, trying to get resources.name of the advisorresources properties field in json of resourceMetadata.resourceId, joined on the resource id.

Kusto Query

advisorresources 
  | extend jProp = parse_json(properties)
  | extend iResourceId = tostring(jProp.resourceMetadata.resourceId)
  | join kind=inner (resources | project id, rname=name) on iResourceId
  | project rname

Error Message

  • Please provide below info when asking for support: timestamp = 2022-10-18T15:57:41.3840226Z, correlationId = 285bf458-6caa-41e5-891e-646166764ada. (Code:BadRequest)
  • Details: Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) 'project' operator: Failed to resolve scalar expression named 'iResourceId' (Code:Operator_FailedToResolveEntity) Failed to resolve join attribute on the right side (Code:Default)
Solution

  • the right side of the join doesn't include a column named iResourceId - it only includes the columns id and rname:

    resources | project id, rname=name

    try replacing the above with:

    resources | project iResourceId=id, rname=name