Clube 2 or more KQL query into single query.

Question

Hi Team,

 

I would like to club two or more KQL queries into one single query so that I can get consolidated result, please help me on the same.

Query-1: List out Vnets, subnets, location and resource group.

Resources

| where type == 'microsoft.network/virtualnetworks'

| extend subnets = properties.subnets

| mv-expand subnets

| project name, subnets.name, subnets.properties.addressPrefix, location, resourceGroup

 

Query-2: List out NSGs and associated resource groups.

Resources

| where type =~ "microsoft.network/networksecuritygroups"

| project name, resourceGroup, subscriptionId

| sort by name asc

 

 

Answer 1

Hi Ameet Desai

Thank you for reaching out.

The problem is to combine the two queries into one.

You can use union to combine them for the same result as expected.

union Query1, Query2

let Query1 = Resources
| where type == 'microsoft.network/virtualnetworks'
| extend subnets = properties.subnets
| mv-expand subnets
| project name, subnets.name, subnets.properties.addressPrefix, location, resourceGroup;
let Query2 = Resources
| where type =~ "microsoft.network/networksecuritygroups"
| project name, resourceGroup, subscriptionId
| sort by name asc;
union Query1, Query2

Kindly let me know if this work for you.

Rg.

Answer 2

@Ameet Desai ,

Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.

I am not sure why you would like to query NSGs and Subnets together, as this would mean "subnets.name" and "subnets.properties.addressPrefix" would be empty (null) for the NSGs

However, the below query should do the trick

Resources
| where type == 'microsoft.network/virtualnetworks' or type == 'microsoft.network/networksecuritygroups'
| extend subnets = properties.subnets
| mv-expand subnets
| project name, subnets.name, subnets.properties.addressPrefix, location, resourceGroup, ['type'], subscriptionId
| sort by ['type'], name asc
| project-away ['type']

P.S :

• I am sorting based on Resource Type so that NSGs appear together and the Subnets appear together.
• The NSGs and Subnets themselves are sorted in Ascending order withing their group

Please let us know if we can be of any further assistance here.

Thanks,

Kapil

---

Please Accept an answer if correct.

Original posters help the community find answers faster by identifying the correct answer.