Query: logging_metric_alert_storage_iam_permission_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*gcs_bucket\s*AND\s*protoPayload.methodName\s*=\s*"storage.setIamPermissions"\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 Storage IAM permission changes.'
    else 'Log metric and alert do not exist for Storage IAM permission changes.'
  end as reason,
  project_id as project
from
  gcp_project;