turbot/gcp_compliance

Query: logging_metric_alert_project_ownership_assignment

Usage

powerpipe query gcp_compliance.query.logging_metric_alert_project_ownership_assignment

SQL

with filter_data as (
select
m.project as project,
display_name alert_name,
count(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*\(protoPayload.serviceName\s*=\s*"cloudresourcemanager.googleapis.com"\s*\)\s*AND\s*\(\s*ProjectOwnership\s*OR\s*projectOwnerInvitee\s*\)\s*OR\s*\(\s*protoPayload.serviceData.policyDelta.bindingDeltas.action\s*=\s*"REMOVE"\s*AND\s*protoPayload.serviceData.policyDelta.bindingDeltas.role\s*=\s*"roles/owner"\s*\)\s*OR\s*\(\s*protoPayload.serviceData.policyDelta.bindingDeltas.action\s*=\s*"ADD"\s*AND\s*protoPayload.serviceData.policyDelta.bindingDeltas.role\s*=\s*"roles/owner"\s*\)\s*'
and filter_condition -> 'conditionThreshold' ->> 'filter' like '%metric.type="' || m.metric_descriptor_type || '"%'
where
enabled
group by
m.project,
display_name,
m.name
)
select
'https://cloudresourcemanager.googleapis.com/v1/projects/' || project_id resource,
case
when d.metric_name > 0 then 'ok'
else 'alarm'
end as status,
case
when d.metric_name > 0 then 'Log metric and alert exist for project ownership assignments/changes.'
else 'Log metric and alert do not exist exist for project ownership assignments/changes.'
end as reason,
project_id as project
from
gcp_project as p
left join filter_data as d on d.project = p.name;

Controls

The query is being used by the following controls: