Table: azure_monitor_diagnostic_settings

SELECT
  'Ensure that Diagnostic Logs are enabled for all services which support it.'
    AS title,
  amr.subscription_id AS subscription_id,
  amr.id AS resource_id,
  CASE WHEN amds.id IS DISTINCT FROM NULL THEN 'pass' ELSE 'fail' END AS status
FROM
  azure_monitor_resources AS amr
  LEFT JOIN azure_monitor_diagnostic_settings AS amds ON
      amr._cq_id = amds._cq_parent_id;

WITH
  diagnosis_logs
    AS (
      SELECT
        amds.subscription_id,
        amds.id
        || '/'
        || (COALESCE(logs->>'category', logs->>'categoryGroup'))::STRING
          AS id,
        logs->>'category' IS DISTINCT FROM NULL AS hascategory,
        (logs->'retentionPolicy'->>'days')::INT8 >= 180 AS satisfyretentiondays
      FROM
        azure_monitor_resources AS amr
        LEFT JOIN azure_monitor_diagnostic_settings AS amds ON
            amr._cq_id = amds._cq_parent_id,
        jsonb_array_elements(amds.properties->'logs') AS logs
      WHERE
        amr.type = 'Microsoft.KeyVault/vaults'
    )
SELECT
  e'Ensure that logging for Azure Key Vault is \'Enabled\'' AS title,
  subscription_id AS subscription_id,
  id AS resource_id,
  CASE
  WHEN hascategory AND satisfyretentiondays THEN 'pass'
  ELSE 'fail'
  END
    AS status
FROM
  diagnosis_logs;

SELECT
  e'Ensure that a \'Diagnostics Setting\' exists' AS title,
  amr.subscription_id AS subscription_id,
  amr.id AS resource_id,
  CASE WHEN amds.properties IS NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  azure_monitor_resources AS amr
  LEFT JOIN azure_monitor_diagnostic_settings AS amds ON
      amr._cq_id = amds._cq_parent_id;

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;