Question on KQL: any way to call resourcecontainers two times using Azure Resource Graph Explorer???

Senior Member

Is there any way to call resourcecontainers table more than two times, in a query, using Azure Resource Graph Explorer? I would like to know workarounds to avoid these known limitations.

 

What I eventually want to do is to extract names, tags, and properties attributes from 2 of the following types, AT THE SAME TIME:

microsoft.management/managementgroups

microsoft.resources/subscriptions

microsoft.resources/subscriptions/resourcegroups

 

I would like to grasp and leverage/utilize all the tags and properties of these 3 levels, on top of each individual resource's portions.

 

I am usually writing queries like this:

example: securityresources. not limiting but showing just as an example of this query pattern.

 

----

securityresources

|where type= 'type and other conditions go here; such as recommendations, alerts, security posture, etc.'
| extend resId=properties.resouceDetails.Id

join kind=leftouter(resourcecontainers

| where type IN ("microsoft.management/managementgroups","microsoft.resources/subscriptions","microsoft.resources/subscriptions/resourcegroups")

| extend SubName = iff(type == "microsoft.resources/subscriptions",name,'N/A'), subTags = iff(type == "microsoft.resources/subscriptions",tags,'N/A'), SubProperties = iff(type == "microsoft.resources/subscriptions",properties,'N/A')

) on subscriptionId

join kind = inner (resources

| where type IN (array; vm, apps, keyvaults, storage, etc.)

| extend vmlocation = location, VMName = name

) on $left.resId=tolower ($right.Id)

project id, VMName, alertDisplayName,sresourceGroup, subName,subProperties,subTags,properties,tags

----

 

In this pattern of query, resourcecontainers can appear only once like this and I cannot have it appear two or more times, such as individually calling in the same join or nesting/cascading resourcecontainers table, using different types.

 

resourcecontainers table also cannot be the right position more than one time, so just nesting the same table at the current position just emits errors. So, currently we need to choose which level of the three, meaning management groups, subscriptions, or resourcegroups to grain the properties and tags. I often select subscriptions here, because there is no other way I found, to attach subscription name to the main query contents.

 

So, is there any way to avoid these restrictions and leverage "resourcecontainers" table, at different types/levels against subscription, resource groups, management groups, within the same query, to have all of the name, properties, and tags attributes of all these 3 levels AT THE SAME TIME?

Maybe managementgroups table needs to be differently queried, since it does not have a key, other than tenantId, that can be seen in other tables such as securityresources or resources.

Probably even if possible, only two of these three, meaning resourcegroups and subscriptons can be queried at the same time, with one key, such as Id or subscriptionId.

Any other workarouds for that, not using let to MANUALLY define arrays, writing down corresponding pairs, since, to me, there are hundreds of resource groups to tackle on.

 

I am utilizing Microsoft Defender to query against functionalities, such as endpoint, cloud, etc, so not capable of utilizing other UIs so far.

 

Hoping someone can find a workaround to avoid limitations with the "resourcecontainers" table.

 

P.S. Please do not mingle the table name "resourcecontainers" with "resources" or "securityresources".

I know that even in this context resources and securityresources table can be nested 4 times at maximum if necessary, and the current limit of the number of tables to JOIN is 4.

However, I am not talking about that. I am talking about specific limitations with "resourcecontainers" table only.

 

Thank you,

 

Kenji

0 Replies