Forum Discussion

Deleted's avatar
Deleted
Apr 21, 2023

Need assistance w generating a KQL query to identify settings in Azure

Hello,

 

I need assistance generating a KQL query to identify:

 

1. Storage accounts that have "Enabled from all networks" set under Public network access under "Firewalls and virtual networks".

2. Container registries that are set to "All networks" for Public network access.

3. Route tables that show default 0.0.0.0/0 via the Internet.

 

Any guidance is greatly appreciated!

 

  • josequintino's avatar
    josequintino
    Iron Contributor
    Hi Deleted
    Creating KQL queries for Azure Resource Graph can help you identify specific resource configurations. Here are the queries you requested:

    1. Storage accounts with "Enabled from all networks":

    ```kql
    Resources
    | where type =~ "microsoft.storage/storageaccounts"
    | extend properties = parse_json(properties)
    | extend allowAll = iif(properties.networkAcls.defaultAction == "Allow", "Enabled from all networks", "Not enabled from all networks")
    | where allowAll == "Enabled from all networks"
    | project name, resourceGroup, type, allowAll
    ```

    2. Container registries with "All networks" for Public network access:

    ```kql
    Resources
    | where type =~ "Microsoft.ContainerRegistry/registries"
    | extend properties = parse_json(properties)
    | extend publicAccess = iif(properties.publicNetworkAccess == "Enabled", "All networks", "Not all networks")
    | where publicAccess == "All networks"
    | project name, resourceGroup, type, publicAccess
    ```

    3. Route tables with default route 0.0.0.0/0 via the Internet:

    ```kql
    Resources
    | where type =~ "Microsoft.Network/routeTables"
    | extend properties = parse_json(properties)
    | mv-expand rules = properties.routes
    | extend rule = parse_json(rules)
    | where rule.addressPrefix == "0.0.0.0/0" and rule.nextHopType == "Internet"
    | project name, resourceGroup, type, ruleName = rule.name, addressPrefix = rule.addressPrefix, nextHopType = rule.nextHopType
    ```

    These KQL queries can be executed in the Azure Resource Graph Explorer or Azure Monitor Logs to help you identify the desired resources and configurations. Make sure to adjust the queries if you have specific requirements or if the property names change in the future.
    • Deleted's avatar
      Deleted

      Thank you!!! #1 & #2 work perfectly. I'm having trouble running the last query. For some reason it's not picking up the rule.addressPrefix or rule.nextHopType. When I run the above, I get nothing back at all.


      When I comment out line # 6, I get data back however the rows under addressPrefix & nextHopType columns list as "null" I don't see anything "0.0.0.0/0" or "Internet".

      For grins I changed line 6 to the following, expecting to get the same output back.
      | where rule.addressPrefix == "null" and rule.nextHopType == "null"
      I get nothing back at all. 

       

      I also tried changing line 6 to:

      where rule.addressPrefix == "0.0.0.0/0"
      To see if I get anything back for the addressPrefix.
      I get nothing back. 

       

      What am I doing wrong?
      see attached screenshot 

  • UPDATE:
    For #1 I use this query and get back a list that shows publicNetworkAccessstatus "Enabled".
    resources
    | where type == "microsoft.storage/storageaccounts"
    | project name,publicNetworkAccessstatus = "Enabled"
    However drilling down into each account, some show "Enabled from selected virtual networks & IP addresses". How can I tweak the query to show only items "Enabled from all networks"? What am I missing?

Resources