Clube 2 or more KQL query into single query.

Ameet Desai 0 Reputation points
2024-05-17T17:49:48.99+00:00

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

 

 

Azure Virtual Network
Azure Virtual Network
An Azure networking service that is used to provision private networks and optionally to connect to on-premises datacenters.
2,229 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Sina Salam 5,551 Reputation points
    2024-05-17T20:46:06.82+00:00

    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.

    0 comments No comments

  2. KapilAnanth-MSFT 38,201 Reputation points Microsoft Employee
    2024-05-21T06:27:27.4633333+00:00

    @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.

    0 comments No comments