0

The following query fails with 2 ParserFailure errors, both on line 5. At least that's where the query builder shows the red curly line.

The intention of this query is probably obvious to the Azure KQL initiates, but I'll explain nonetheless just to make sure it's clear. This query should list all NSGs that do not have either one of the rules named "AllowThis" or "AllowThat".

Resources
| where type == "microsoft.network/networksecuritygroups"
| where isnotempty(properties.securityRules)
| where not(properties.securityRules
  | where (tolower(tostring(properties.securityRules.ruleName)) =~ "allowthis|allowthat"))
| project NSGName = name
| order by NSGName asc

It would even be nicer if the table shows the actual missing rule(s) for the listed NSGs, but I have no idea where to start with that.

Does anyone have a working version of this type of query? Having to go through a lot of NSGs manually can't be the answer.

I have tried multiple variations of the query, but I couldn't find a single working version.

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
Bobtb
  • 26
  • 3
  • `=>` is not a KQL syntax and Lambda functions are not supported. Where did you take this syntax from? – David דודו Markovitz Feb 16 '23 at 09:33
  • I asked chatgpt to create a query with these specifications, this is one of the answers it came up with. – Bobtb Feb 16 '23 at 09:48
  • It looks like I shouldn't have asked chatgpt to create a KQL query, without specifying it's for Azure Resource Graph Explorer. While I have tried multiple different questions, I will update my post to be the one that looks to make the most common sense to me. – Bobtb Feb 16 '23 at 10:02

1 Answers1

0

Below are my findings and observations from the query posted in question. Lines 1 to 3 looks good and will give you list of NSG resources which has values for "securityRules" field.

  • For line number 4

| where not(properties.securityRules) I am not sure what are you trying to achieve in this step. The not() takes bool values as mentioned in the documentation.

  • For line number 5

| where (tolower(tostring(properties.securityRules.ruleName)) =~ "allowthis|allowthat") There is no need to use tolower() when you are using =~ as this supports case-insensitive match. Also under "securityRules" in NSG json object there is no field named as "ruleName", however there is a field "name". Please find the document for the same - Link. You can use the same documentation to check for the fields available to query NSG resource data.

When you are trying to write condition for "AllowThis" or "AllowThat" in Azure Resource Graph Explorer you should use the syntax properties.securityRules.name == "allowthis" or properties.securityRules.name == "allowthat" If you write anything within quotes it will be taken as single string. Hence in your query "allowthis|allowthat" will be considered as a single string.

RitikaNalwaya
  • 529
  • 1
  • 3
  • Lines 4 and 5 is actually a single where statement, with the 2nd being imbedded in the 1st.' Unfortunately I'm not familiar with the Azure Graph Explorer query syntax to know if this is valid or not. I figured that the tolower() function was redundant indeed, but it doesn't cripple the query anyway, so I just kept it there. – Bobtb Feb 20 '23 at 06:52
  • @Bobtb Kql doesn't support `where` within `where`. It supports `where` after `where` which will act as `and` operator i.e. apply where condition on already filter data. You can check [this](https://learn.microsoft.com/en-us/azure/data-explorer/kql-quick-reference) – RitikaNalwaya Feb 21 '23 at 12:28