Table: azure_storage_accounts

SELECT
  'Ensure default network access rule for Storage Accounts is set to deny'
    AS title,
  subscription_id AS subscription_id,
  id AS resource_id,
  CASE
  WHEN properties->'networkAcls'->>'defaultAction' = 'Allow' THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  azure_storage_accounts;

SELECT
  'Ensure storage for critical data are encrypted with Customer Managed Key'
    AS title,
  subscription_id AS subscription_id,
  id AS resource_id,
  CASE
  WHEN properties->'encryption'->>'keySource' = 'Microsoft.Keyvault'
  AND properties->'encryption'->'keyvaultproperties' IS DISTINCT FROM NULL
  THEN 'pass'
  ELSE 'fail'
  END
    AS status
FROM
  azure_storage_accounts;

SELECT
  'Ensure the storage account containing the container with activity logs is encrypted with BYOK (Use Your Own Key)'
    AS title,
  asa.subscription_id AS subscription_id,
  asa.id AS resource_id,
  CASE
  WHEN asa.properties->'encryption'->>'keySource' = 'Microsoft.Keyvault'
  AND asa.properties->'encryption'->'keyvaultproperties' IS DISTINCT FROM NULL
  THEN 'pass'
  ELSE 'fail'
  END
    AS status
FROM
  azure_storage_accounts AS asa
  JOIN azure_monitor_diagnostic_settings AS amds ON
      asa.id = amds.properties->>'storageAccountId'
WHERE
  (amds.properties->>'storageAccountId') IS NOT NULL;

SELECT
  e'Ensure that \'Public access level\' is set to Private for blob containers'
    AS title,
  azsc.subscription_id AS subscription_id,
  azsc.id AS resrouce_id,
  CASE
  WHEN (asa.properties->>'allowBlobPublicAccess')::BOOL = true
  AND (azsc.properties->>'publicAccess') != 'None'
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  azure_storage_containers AS azsc
  JOIN azure_storage_accounts AS asa ON azsc._cq_parent_id = asa._cq_id;