CODEWITHSUNDEEP
azureazure-resource-graph
jkroepke
jkroepke

Reputation: 131

Resource Graph Query: Get Subscription Name of 2 Subscriptions

I have a ResouceGraph Query like this:

RecoveryServicesResources
| where (properties.startTime >= ago(7d))
| extend endTime = todatetime(properties.endTime)
| extend backupInstanceId = tolower(tostring(properties.backupInstanceId))
| extend dataSourceId = tolower(tostring(properties.dataSourceId))
| extend dataSourceSubscriptionId = extract("/subscriptions/(.*?)/", 1, dataSourceId)
| summarize arg_max(endTime, *) by backupInstanceId
| project backupInstanceId, subscriptionId, dataSourceSubscriptionId

which give me a table like this back

ID subscriptionId dataSourceSubscriptionId
ID 8749684f-50da-4acb-a8dd-203f3d23e4bf 878c7009-b12e-4b79-b1bf-572b03cf4d61
ID 8749684f-50da-4acb-a8dd-203f3d23e4bf a1aa6c3a-0571-4c78-bf4d-437cc8c2adfd

How to get the Subscription Name of subscriptionId and dataSourceSubscriptionId ?

RecoveryServicesResources
| where (properties.startTime >= ago(7d))
| extend endTime = todatetime(properties.endTime)
| extend backupInstanceId = tolower(tostring(properties.backupInstanceId))
| extend dataSourceId = tolower(tostring(properties.dataSourceId))
| extend dataSourceSubscriptionId = extract("/subscriptions/(.*?)/", 1, dataSourceId)
| summarize arg_max(endTime, *) by backupInstanceId
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name
    )
    on subscriptionId
| join kind=leftouter (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project dataSourceSubscriptionId=subscriptionId, dataSourceSubscriptionName = name
    )
    on dataSourceSubscriptionId
| project backupInstanceId, subscriptionId, dataSourceSubscriptionId, subscriptionName, dataSourceSubscriptionName

Which give me the error:

Table resourcecontainers was referenced as right table 2 times, which exceeded the limit of 1. Please see https://aka.ms/resourcegraph-tables for help. (Code:DisallowedMaxNumberOfRemoteTables)?

NOTE: Any solution which requires a shell wrapper is not acceptable. I have to integrate the query in a 3rd party tool.

Upvotes: 0

Views: 476

Answers (1)

Aswin
Aswin

Reputation: 7126

Table resourcecontainers was referenced as right table 2 times, which exceeded the limit of 1. Please see https://aka.ms/resourcegraph-tables for help. (Code:DisallowedMaxNumberOfRemoteTables)

This error indicates that RecoveryServicesResources is not allowed to join with resourcecontainers twice. Check the link in the error message. It shows resourcecontainers supports join partially only. This is the reason for the error.

It is not necessary to join with resourcecontainers to get the subscription name of subscription id. When Formatted result is toggled on in the output window, it displays the name of the subscription id. Thus, it is enough to join with resourcecontainers once only to get the name of datasourcesubscriptionId.

RecoveryServicesResources
| where (properties.startTime >= ago(7d))
| extend endTime = todatetime(properties.endTime)
| extend backupInstanceId = tolower(tostring(properties.backupInstanceId))
| extend dataSourceId = tolower(tostring(properties.dataSourceId))
| extend dataSourceSubscriptionId = extract("/subscriptions/(.*?)/", 1, dataSourceId)
| summarize arg_max(endTime, *) by backupInstanceId
| join kind=leftouter (
resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project dataSourceSubscriptionId=subscriptionId, dataSourceSubscriptionName = name
)
on dataSourceSubscriptionId
| project backupInstanceId, subscriptionId, dataSourceSubscriptionId, dataSourceSubscriptionName

img1

Upvotes: 0

Related Questions