Query: logging_metric_alert_firewall_rule_changes

SQL

with filter_data as (
  select
    display_name alert_name,
    m.name metric_name
  from
    gcp_monitoring_alert_policy,
    jsonb_array_elements(conditions) as filter_condition
    join gcp_logging_metric m on m.filter ~ '\s*resource.type\s*=\s*"gce_firewall_rule"\s*AND\s*protoPayload.methodName\s*=\s*"v1.compute.firewalls.patch"\s*OR\s*protoPayload.methodName\s*=\s*"v1.compute.firewalls.insert"\s*'
    and filter_condition -> 'conditionThreshold' ->> 'filter' like '%metric.type="' || m.metric_descriptor_type || '"%'
  where
    enabled
)
select
  'https://cloudresourcemanager.googleapis.com/v1/projects/' || project_id resource,
  case
    when (
      select
        count(metric_name)
      from
        filter_data
    ) > 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when (
      select
        count(metric_name)
      from
        filter_data
    ) > 0 then 'Log metric and alert exist for network firewall rule changes.'
    else 'Log metric and alert do not exist network for firewall rule changes.'
  end as reason,
  name as project
from
  gcp_project;