Clube 2 or more KQL query into single query.

Ameet Desai 0

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

Sina Salam 4,546

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.

Ameet Desai 0 Reputation points

2024-05-20T20:39:44.0666667+00:00

Thanks quick response,

its not working still.

Sina Salam 4,546

Hi Ameet,

I initially have this code below, but it was the same as above and rewriting for your use:

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

Try the above.

Hopefully it work.

Rg.

Ameet Desai 0 Reputation points

2024-05-21T19:01:18.8833333+00:00

still not getting any results by this query.
Sina Salam 4,546 Reputation points

2024-05-21T21:59:53.27+00:00

Hi Ameet,

Thank you for posting your feedback.

Simply means something is there you are not sharing with us.

Check what you are doing and possibly if you have error code, we might get clue of what is going on at your end. Also, I will advise you use Azure support from your Azure Portal.

Best,

Sina
Ameet Desai 0 Reputation points

2024-05-22T01:02:26.17+00:00

Details: Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) ParserFailure (Code:ParserFailure) ParserFailure (Code:ParserFailure) ParserFailure (Code:ParserFailure) ParserFailure (Code:ParserFailure) ParserFailure (Code:ParserFailure) ParserFailure (Code:ParserFailure)

I tried to fix some Parserfailure errors but not all, above is the error message for the query.
KapilAnanth-MSFT 37,646 Reputation points Microsoft Employee

2024-05-22T06:51:12.59+00:00

@Ameet Desai ,

That is strange. When I run the same query in Resource Graph Explorer, I was able to get the results.

Can you please share a screenshot of the entire screen (along with both the query and the Results tab)?

Like below (the highlighted parts being visible):

Cheers,

Kapil
Ameet Desai 0 Reputation points

2024-05-22T17:12:06.8733333+00:00

Hi Kapil,

Thanks for sharing your idea,

Here is how am running this query, please let me know if am missing anything here.
KapilAnanth-MSFT 37,646 Reputation points Microsoft Employee

2024-05-22T17:21:02.51+00:00

@Ameet Desai ,

From the error message, it appears you have misspelt the "resourceGroup" as "resourcesGroup" in line 5

i.e., No Plural.

Can you please fix it and run the query again?
Ameet Desai 0 Reputation points

2024-05-22T18:25:37.8733333+00:00

please suggest
KapilAnanth-MSFT 37,646 Reputation points Microsoft Employee

2024-05-23T05:18:58.0766667+00:00
@Ameet Desai ,

I am afraid there is still a spelling mistake.

From the error message, you can see you have used "resourcesGroups" instead of "resourcesGroup"

From my previous comment, you can see I mentioned we **should use "**resourcesGroup" - No plural

I think you are editing the query, I would instead suggest you directly copy paste from my answer to avoid such typos.

Once again, the query is ,

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']
KapilAnanth-MSFT 37,646 Reputation points Microsoft Employee

2024-05-24T04:19:18.5366667+00:00

@Ameet Desai ,

Did you get a chance to correct the spelling of "resourceGroup" ?

I checked this myself and the query works as expected, please copy the code and paste it in your explorer to avoid such issues.

I have converted my original comment to a new answer.

I would highly appreciate if you could Accept the answer and close this thread

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

Thanks,

Kapil

1 answer

KapilAnanth-MSFT 37,646 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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Clube 2 or more KQL query into single query.

1 answer