turbot/gcp_compliance

GitHub
audit_logging_configured_for_all_servicebigquery_dataset_encrypted_with_cmkbigquery_dataset_not_publicly_accessiblebigquery_dataset_restrict_gmailbigquery_dataset_restrict_googlegroupsbigquery_table_encrypted_with_cmkcompute_disk_encrypted_with_cskcompute_firewall_allow_connections_proxied_by_iapcompute_firewall_rule_rdp_access_restrictedcompute_firewall_rule_ssh_access_restrictedcompute_https_load_balancer_logging_enabledcompute_instance_block_project_wide_ssh_enabledcompute_instance_confidential_computing_enabledcompute_instance_ip_forwarding_disabledcompute_instance_oslogin_enabledcompute_instance_serial_port_connection_disabledcompute_instance_shielded_vm_enabledcompute_instance_with_no_default_service_accountcompute_instance_with_no_default_service_account_with_full_accesscompute_instance_with_no_public_ip_addressescompute_network_contains_no_default_networkcompute_network_contains_no_legacy_networkcompute_network_dns_logging_enabledcompute_ssl_policy_with_no_weak_ciphercompute_subnetwork_flow_log_enabledcompute_subnetwork_private_ip_google_accessdataproc_cluster_encryption_with_cmekdns_managed_zone_dnssec_enableddns_managed_zone_key_signing_not_using_rsasha1dns_managed_zone_zone_signing_not_using_rsasha1iam_service_account_gcp_managed_keyiam_service_account_key_age_100iam_service_account_key_age_90iam_service_account_without_admin_privilegeiam_user_denylist_publiciam_user_not_assigned_service_account_user_role_project_leveliam_user_separation_of_duty_enforcediam_user_uses_corporate_login_credentialskms_key_not_publicly_accessiblekms_key_rotated_within_100_daykms_key_rotated_within_90_daykms_key_separation_of_duties_enforcedkubernetes_cluster_auto_repair_enabledkubernetes_cluster_auto_upgrade_enabledkubernetes_cluster_dashboard_disabledkubernetes_cluster_legacy_abac_enabledkubernetes_cluster_legacy_endpoints_disabledkubernetes_cluster_master_authorized_networks_config_enabledkubernetes_cluster_network_policy_installedkubernetes_cluster_node_config_image_cos_containerdkubernetes_cluster_private_cluster_config_enabledkubernetes_cluster_service_account_defaultkubernetes_cluster_use_ip_aliaseslogging_bucket_retention_policy_enabledlogging_metric_alert_audit_configuration_changeslogging_metric_alert_custom_role_changeslogging_metric_alert_firewall_rule_changeslogging_metric_alert_network_changeslogging_metric_alert_network_route_changeslogging_metric_alert_project_ownership_assignmentlogging_metric_alert_sql_instance_configuration_changeslogging_metric_alert_storage_iam_permission_changeslogging_sink_configured_for_all_resourcemanual_controlorganization_essential_contacts_configuredproject_access_approval_settings_enabledproject_service_cloudasset_api_enabledsql_instance_automated_backups_enabledsql_instance_mysql_local_infile_database_flag_offsql_instance_mysql_skip_show_database_flag_onsql_instance_not_open_to_internetsql_instance_not_publicly_accessiblesql_instance_postgresql_cloudsql_pgaudit_database_flag_enabledsql_instance_postgresql_log_checkpoints_database_flag_onsql_instance_postgresql_log_connections_database_flag_onsql_instance_postgresql_log_disconnections_database_flag_onsql_instance_postgresql_log_duration_database_flag_onsql_instance_postgresql_log_error_verbosity_database_flag_default_or_strictersql_instance_postgresql_log_executor_stats_database_flag_offsql_instance_postgresql_log_hostname_database_flag_configuredsql_instance_postgresql_log_lock_waits_database_flag_onsql_instance_postgresql_log_min_duration_statement_database_flag_disabledsql_instance_postgresql_log_min_error_statement_database_flag_configuredsql_instance_postgresql_log_min_messages_database_flag_errorsql_instance_postgresql_log_parser_stats_database_flag_offsql_instance_postgresql_log_planner_stats_database_flag_offsql_instance_postgresql_log_statement_database_flag_ddlsql_instance_postgresql_log_statement_stats_database_flag_offsql_instance_postgresql_log_temp_files_database_flag_0sql_instance_require_ssl_enabledsql_instance_sql_3625_trace_database_flag_offsql_instance_sql_3625_trace_database_flag_onsql_instance_sql_contained_database_authentication_database_flag_offsql_instance_sql_cross_db_ownership_chaining_database_flag_offsql_instance_sql_external_scripts_enabled_database_flag_offsql_instance_sql_remote_access_database_flag_offsql_instance_sql_user_connections_database_flag_configuredsql_instance_sql_user_options_database_flag_not_configuredsql_instance_with_no_public_ipsstorage_bucket_bucket_policy_only_enabledstorage_bucket_not_publicly_accessiblestorage_bucket_uniform_access_enabled

Query: logging_metric_alert_storage_iam_permission_changes

Usage

steampipe query gcp_compliance.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;

Controls

The query is being used by the following controls: