Resource Graph Query: Get Subscription Name of 2 Subscriptions

Question

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.

Answer 1

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