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,
  name as project
from
  gcp_project;