Azure resource graph - extractjson example

 

After another long gap I'm finally putting pen to paper (well fingers to keyboard). This is just a short note to hope fully get me back in the saddle. This one is on resource graph query - one of the most useful additions to the Azure space in recent times.

In this example i just want to do a simple report to do the following:

Show me a complete list of all resource groups, including a specific tag value, the subscription name as well as the chain of management group hierarchy the resource group is in.

The first part of that is simple and doesn't really warrant even writing down - however the second part needs a little bit of extra work - it's still fairly simple but there are not too many examples of how to do this so hopefully this is useful for someone.

To create the report the data is just held in 2 resource graph locations (well actually it's the same one twice...)

I'll post the query here first and then explain the 'tricky' parts afterwards.

xx

resourcecontainers
|where type== "microsoft.resources/subscriptions/resourcegroups"
| project name,sub=toupper(subscriptionId),coalesce(tags.Owner,tags.owner),coalesce(tags.Owner_Email,tags.Owner_email,tags.owner_email),tags.EAM_ID
|join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' 
| project SubName=name,sub=toupper(subscriptionId),parent=tostring(properties.managementGroupAncestorsChain)) on $left.sub==$right.sub
| project-away sub1 
|extend parent1=extractjson("$.[0].displayName",parent)
|extend parent2=extractjson("$.[1].displayName",parent)
|extend parent3=extractjson("$.[2].displayName",parent)
|project-away parent    

So to pick out the points of note:

1) The 'coalesce' function returns the first non null value from a series of inputs - this is very useful as for our tags there is some mixed case of the tag names (which needs correcting) and resource graph is case sensitive - using coaesce with the 3 variations of tag name means we will always have a value displayed.

2) You'll notice i have toupper() around subscription id's - again this is due to case sensitivity - without this the data won't match - this is a major gotcha when working with resource graph....

3) And this is the bit that was new to me the 'properties.managementGroupAncestorsChain' value of a subscription object is a json object with an embedded array - this then becomes tricky to access data out of. So in this case i explicitly make it a string with tostring() (this has to be done to avoid an error later on) and name that string 'parent'. Once i have that string I then use the extractjson function to pull values out of it. In the case above the syntax $.[0].displayName is saying :

Start from the root ($) , for the first element in the array ([0]) show me the displayName value - in this case the immediate parent management group of the subscription.

I then repeat that for the second and third element in the array to give the grand parent and great grand parent values.

Once you've got your head round the syntax it's actually quite easy.

There you go - short and sweet - hope that's useful....

Picture is of a 'take out' (like extract the json out is take out) - that was obvious right?