Azure Resource Graph Explorer - Query Azure VM descriptions, OS, sku - I need to join to columns (OS and sku in one)

2.2k views Asked by At

I have a issue. I want to know how can I join two columns in one.

I want to join the "OS" and "sku" columns in one with the name "OS"

This is my KQL: Kusto Query on Azure Resource Graph

Resources
| where type == "microsoft.compute/virtualmachines"
| extend OS = properties.storageProfile.imageReference.offer
| extend sku = properties.storageProfile.imageReference.sku
| project OS, sku, name, nic = (properties.networkProfile.networkInterfaces)
| mvexpand nic
| project OS, sku, name, nic_id = tostring(nic.id)
| join (
    Resources 
    | where type == "microsoft.network/networkinterfaces" 
    | project nic_id = tostring(id), properties) on nic_id
    | mvexpand ipconfig = (properties.ipConfigurations)
    | extend subnet_resource_id = split(tostring(ipconfig.properties.subnet.id), '/'), ipAddress = ipconfig.properties.privateIPAddress
    | order by name desc
| project vmName=(name), OS, sku, vnetName=subnet_resource_id[8], subnetName=subnet_resource_id[10], ipAddress

This is my result:

the result image

I need like this:

the desired output img

Can anyone help me, thanks so much.

I've tried to use the "union" operator, but I can't make it work.

I have used these reference link:

Azure Docs Link 1

Azure Docs Link 2

Azure Docs Link 3

1

There are 1 answers

1
Alexander Sloutsky On

If you want to combine two strings - you can use strcat() function:

 Resources
| where type == "microsoft.compute/virtualmachines"
| extend OS = properties.storageProfile.imageReference.offer
| extend sku = properties.storageProfile.imageReference.sku
| project OS, sku, name, nic = (properties.networkProfile.networkInterfaces)
| mvexpand nic
| project OS, sku, name, nic_id = tostring(nic.id)
| join (
    Resources 
    | where type == "microsoft.network/networkinterfaces" 
    | project nic_id = tostring(id), properties) on nic_id
    | mvexpand ipconfig = (properties.ipConfigurations)
    | extend subnet_resource_id = split(tostring(ipconfig.properties.subnet.id), '/'), ipAddress = ipconfig.properties.privateIPAddress
    | order by name desc
| project vmName=(name), OS = strcat(OS, ' ', sku), vnetName=subnet_resource_id[8], subnetName=subnet_resource_id[10], ipAddress