Joins in Azure Resource Graph KQL

Joins in Azure Resource Graph KQL

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!