Control: Ensure that Activity Log Alert exists for Create or Update SQL Server Firewall Rule

powerpipe control run azure_compliance.control.monitor_log_alert_create_update_sql_servers_firewall_rule

powerpipe login
powerpipe control run azure_compliance.control.monitor_log_alert_create_update_sql_servers_firewall_rule --share

with alert_rule as
(
  select
    alert.id as alert_id,
    alert.name as alert_name,
    alert.enabled,
    alert.location,
    alert.subscription_id
  from
    azure_log_alert as alert,
    jsonb_array_elements_text(scopes) as sc
  where
    alert.location = 'global'
    and alert.enabled
    and sc = '/subscriptions/' || alert.subscription_id
    and
    (
      ( alert.condition -> 'allOf' @> '[{"equals":"Administrative","field":"category"}]'
      and alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Sql/servers/firewallRules/write"}]'
      )
      or
      (
        alert.condition -> 'allOf' @> '[{"equals":"Administrative","field":"category"}]'
        and alert.condition -> 'allOf' @> '[{"field": "resourceType", "equals": "microsoft.sql/servers/firewallrules"}]'
        and jsonb_array_length(alert.condition -> 'allOf') = 2
      )
    )
    limit 1
)
select
  sub.subscription_id as resource,
  case
    when count(a.subscription_id) > 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when count(a.subscription_id) > 0 then 'Activity Log Alert exists for Create or Update SQL Server Firewall Rule.'
    else 'Activity Log Alert does not exists for Create or Update SQL Server Firewall Rule.'
  end as reason
  
  , sub.display_name as subscription
from
  azure_subscription sub
  left join alert_rule a on sub.subscription_id = a.subscription_id
group by
  sub._ctx,
  sub.subscription_id,
  sub.display_name;