Joins in Azure Resource Graph KQL
Omar McIver is a seasoned software architect turned AI transformation leader, with a passion for helping organizations and individuals harness the power of artificial intelligence. With years of experience in the tech industry and deep expertise in cloud-native technologies, Omar has evolved his focus to address the most critical challenge of our time: preparing the workforce for the AI revolution. As the Founder of Mode3 AI Training, Omar is pioneering a new approach to AI education that goes beyond simple tool usage. His company's innovative framework teaches professionals to partner with AI as "cybernetic teammates," achieving team-level performance as individuals while breaking down organizational silos.
Despite the high number of KQL queries I write to interrogate the Azure Resource Graph, I mostly manage to avoid joins!
Call me strange, but learning them is a big commitment to a query language - once you JOIN, you're on the precipice of assimilation. Anyway, let's do it...
Step 1 - Our base query
Let's start with a simple query to get all my Virtual Machines along with some basic information - like OS and Region:
resources
| where type == "microsoft.compute/virtualmachines"
| project name, osType=properties.storageProfile.osDisk.osType, osName=properties.extended.instanceView.osName, osVersion=properties.extended.instanceView.osVersion, location, state=properties.provisioningState, firstNetCardId=tostring(properties.networkProfile.networkInterfaces[0].id)
Now in this query, you'll notice I've selected the first Network Interface ID. There must always be at least one (with a valid public or private IP) through which to interact with it.
In the data returned, we cannot see any details about the attached Network Interface as it is a different resource object. All we can get is its Azure unique resource identifier.
Step 2 - Get the Network Interfaces
Similar to our base query - let's write another query that retrieves Network Interfaces, keyed by their Id and selecting their IP Address and IP Allocation Method. We'll also filter out any that are not 'primary' interfaces. We don't want them polluting our join.
resources
| where type == "microsoft.network/networkinterfaces" and properties.primary == true
| project id, name, primary=properties.primary, IP=properties.ipConfigurations[0].properties.privateIPAddress,IPMethod=properties.ipConfigurations[0].properties.privateIPAllocationMethod)
Step 3 - Make the join
We are going to join using the format:
(*1st Query*)
| join ([*2nd Query*]) on $left.[*leftIdColumn*] == $right.[*rightIdColumn*]
Which evaluates to:
resources
| where type == "microsoft.compute/virtualmachines"
| project name, osType=properties.storageProfile.osDisk.osType, osName=properties.extended.instanceView.osName, osVersion=properties.extended.instanceView.osVersion, location, state=properties.provisioningState, firstNetCardId=tostring(properties.networkProfile.networkInterfaces[0].id)
| join (resources
| where type == "microsoft.network/networkinterfaces" and properties.primary == true
| project id, name, primary=properties.primary, IP=properties.ipConfigurations[0].properties.privateIPAddress,IPMethod=properties.ipConfigurations[0].properties.privateIPAllocationMethod)
on $left.firstNetCardId == $right.id
Step 4 - Clean it up
Our query will (by default) project all the columns together. However, I don't really care about that Network Interface Id. It was just a means to an end (for the join relationship) but now I have 2 copies of it in my result set!
We can use the project keyword again to return only the columns we want across the two joined queries:
resources
| where type == "microsoft.compute/virtualmachines"
| project name, osType=properties.storageProfile.osDisk.osType, osName=properties.extended.instanceView.osName, osVersion=properties.extended.instanceView.osVersion, location, state=properties.provisioningState, firstNetCardId=tostring(properties.networkProfile.networkInterfaces[0].id)
| join (resources
| where type == "microsoft.network/networkinterfaces" and properties.primary == true
| project id, name, primary=properties.primary, IP=properties.ipConfigurations[0].properties.privateIPAddress,IPMethod=properties.ipConfigurations[0].properties.privateIPAllocationMethod)
on $left.firstNetCardId == $right.id
| project name, osType, osName, osVersion, location, state, IP, IPMethod
Well, that's it. Pretty simple really! Now we can take our Resource Graph queries to the next level!
