fix: Update Azure Storage Queries (#5908)

jsonpr · web-flow · commit 38690c617ddf · 2022-12-22T12:23:26.000Z
Update 2 Azure Storage Queries to reflect new Azure Tables
diff --git a/plugins/source/azure/policies/queries/storage/accounts_with_unrestricted_access.sql b/plugins/source/azure/policies/queries/storage/accounts_with_unrestricted_access.sql
@@ -4,10 +4,12 @@ SELECT
   :'framework',
   :'check_id',
   'Storage accounts should restrict network access',
-  subscription_id,
-  id,
+  az_sub.subscription_id,
+  az_stor.id,
   case
-    when network_acls->>'defaultAction' IS DISTINCT FROM 'Deny'
+    when az_stor.properties -> 'networkAcls' ->>'defaultAction' IS DISTINCT FROM 'Deny'
       then 'fail' else 'pass'
   end
-FROM azure_storage_accounts
+FROM azure_storage_accounts as az_stor
+LEFT JOIN azure_subscription_subscriptions as az_sub
+ON az_sub.subscription_id = SUBSTRING(az_stor.id,16,36)
diff --git a/plugins/source/azure/policies/queries/storage/secure_transfer_to_storage_accounts_should_be_enabled.sql b/plugins/source/azure/policies/queries/storage/secure_transfer_to_storage_accounts_should_be_enabled.sql
@@ -4,10 +4,12 @@ SELECT
   :'framework',
   :'check_id',
   'Secure transfer to storage accounts should be enabled',
-  subscription_id,
-  id,
+  az_sub.subscription_id,
+  az_stor.id,
   case
-    when supports_https_traffic_only IS NOT TRUE
+    when az_stor.properties ->> 'supportsHttpsTrafficOnly' IS DISTINCT FROM 'true'
       then 'fail' else 'pass'
   end
-FROM azure_storage_accounts
+FROM azure_storage_accounts as az_stor
+LEFT JOIN azure_subscription_subscriptions as az_sub
+ON az_sub.subscription_id = SUBSTRING(az_stor.id,16,36)
