I’ve recently had to review and identify Storage Account, Key Vault, and Azure SQL Database Networking settings where Enabled for all networks was enabled so I could get the team to remediate and secure them with, preferably Disabled, or at the very least Enabled from selected virtual networks and IP addresses. There are plenty of ways to retrieve the settings but my preferred way is by using Azure Resource Graph Explorer to query the resources and I wanted to share the query for others as well as have a blog post I can refer to in the future when/if I have to do this again.
The KQL code below can be found at my following GitHub repo: https://github.com/terenceluk/Azure/blob/main/Kusto%20KQL/Query-Storage-Account-Key-Vault-Azure-SQL-Database-Networking-Settings.kusto
List all Storage Accounts with Firewall Settings
resources
| where type == “microsoft.storage/storageaccounts”
| where tolower(resourceGroup) contains “front-end”
or tolower(resourceGroup) contains “back-end”
| extend firewallSettings =
case(
properties.publicNetworkAccess == “Disabled”,
“Disabled”,
isempty(properties.publicNetworkAccess) or (properties.publicNetworkAccess == “Enabled” and properties.networkAcls.defaultAction == “Allow”),
“Enabled for all networks”,
“Enabled from selected virtual networks and IP addresses”)
| extend pep_review = properties.privateEndpointConnections
| extend pep_status = iif(isempty(pep_review) or pep_review == “[]”, “No”, “Yes”)
| project name, location, resourceGroup, type, kind, firewallSettings, pep_status, properties.minimumTlsVersion, properties
| order by [‘firewallSettings’] asc
List all Key Vault with Firewall Settings
resources
| where type == “microsoft.keyvault/vaults”
//| where tolower(resourceGroup) contains “front-end”
// or tolower(resourceGroup) contains “back-end”
| extend firewallSettings =
case(
properties.publicNetworkAccess == “Disabled”,
“Disabled”,
isempty(properties.publicNetworkAccess) or (properties.publicNetworkAccess == “Enabled” and properties.networkAcls.defaultAction == “Allow”),
“Enabled for all networks”,
“Enabled from selected virtual networks and IP addresses”)
| extend pep_review = properties.privateEndpointConnections
| extend pep_status = iif(isempty(pep_review) or pep_review == “[]”, “No”, “Yes”)
| project name, location, resourceGroup, type, firewallSettings, pep_status, properties
| order by [‘firewallSettings’] asc
List all SQL Server with Firewall Settings
resources
| where type == “microsoft.sql/servers”
| extend firewallSettings =
case(
properties.publicNetworkAccess == “Disabled”,
“Disabled”,
isempty(properties.publicNetworkAccess) or (properties.publicNetworkAccess == “Enabled” and properties.networkAcls.defaultAction == “Allow”),
“Enabled for all networks”,
“Enabled from selected virtual networks and IP addresses”)
| extend pep_review = properties.privateEndpointConnections
| extend pep_status = iif(isempty(pep_review) or pep_review == “[]”, “No”, “Yes”)
| project name, location, resourceGroup, type, kind, firewallSettings, pep_status, properties
| order by [‘firewallSettings’] asc
Hope this helps anyone who might be looking for this information.
