Some Azure Resource Graph examples for SQL Server

I've been using resource graph a lot recently and found it hard to find good examples for some of the more fiddly types of queries - primarily where I want to join different sets of data - or manipulate the results

The two examples below illustrate a couple of techniques that are maybe useful to know and give some example to copy/paste from.

First up is this one which retrieves details of all the SQL Server installations on IaaS we have (so SQL done in the traditional way)

The trick here is that I need data from 3 different containers within resource graph so I have to join them. What I found was tricky is that resource graph is case sensitive - and the MS data is often inconsistent between the different containers - so I'm having to toupper() some of the data to get it to match. I also have to join on multiple columns (as names can duplicated - only combination of name/rg/sub is unique). I also pull out some of the embedded properties in the tags attribute.

(Resources |where type == "microsoft.sqlvirtualmachine/sqlvirtualmachines"
|  extend lic=properties.sqlServerLicenseType,
    mgmt=properties.sqlManagement,
    image=properties.sqlImageOffer,
    sku=properties.sqlImageSku,sub=toupper(subscriptionId) | extend rg=toupper(resourceGroup) |project-away id,type,tenantId,kind)
| join kind=inner (ResourceContainers |where type=="microsoft.resources/subscriptions/resourcegroups" |project name,itera=tags.IteraplanID,own=tags.Owner,email=tags.Owner_email,sub2=toupper(subscriptionId),nm=toupper(name) ) on $left.rg==$right.nm and $left.sub==$right.sub2
| join kind=inner (ResourceContainers | where type=="microsoft.resources/subscriptions" | project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub 
|project-away subscriptionId,managedBy,plan,properties,tags,identity,zones,extendedLocation,sub,name1,sub2,sub1

That gives you something like this (you likely want to change the tag names in the query for your environment)

Next up is very similar but for Azure SQL (the full PaaS one) - most of the code is very similar - the only additional tricky part is the use of the extract function - here I'm using a regular expression match to pull the server name out of the data - I could find almost no example of someone using that in resource graph.

(Resources |where type == "microsoft.sql/servers/databases" |extend sub=toupper(subscriptionId)
| join kind=inner (ResourceContainers |where type=="microsoft.resources/subscriptions/resourcegroups" |project name,itera=tags.IteraplanID,own=tags.Owner,email=tags.Owner_email,sub2=toupper(subscriptionId) ) on $left.resourceGroup==$right.name and $left.sub==$right.sub2) 
| join kind=inner (ResourceContainers | where type=="microsoft.resources/subscriptions" | project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub 
|extend collation=properties.collation, skuname=properties.currentSku.name,capacity=properties.currentSku.capacity,tier=properties.currentSku.tier,family=properties.currentSku.family
,server=extract("servers/(([a-z]|-|[0-9])+)",1,id)
 |project-away tenantId,type,subscriptionId,managedBy,sku,plan,properties,tags,identity,zones,extendedLocation,sub,name1,sub2,apiVersion,aliases,id

That gives something like the below - same comment as the above for tags.

Hopefully that gives you a head start if you are building anything like this.