turbot/oci_compliance

Query: kms_cmk_rotation_365

Usage

powerpipe query oci_compliance.query.kms_cmk_rotation_365

SQL

with active_key_table as (
select
k.name as key_name,
k.id,
k.compartment_id,
k.vault_name,
k.lifecycle_state,
k._ctx,
k.tenant_id,
k.tenant_name,
k.region,
k.tags,
max(v.time_created) as last_version_created_date
from
oci_kms_key k,
oci_kms_key_version v
where
v.key_id = k.id
and v.management_endpoint = k.management_endpoint
and v.region = k.region
group by
key_name, k.region, k.id, k.vault_name, k.lifecycle_state, k.tenant_id, k._ctx, k.compartment_id, k.tenant_name, k.tags
)
select
a.id as resource,
case
when a.lifecycle_state != 'ENABLED' then 'skip'
when last_version_created_date <= (current_date - interval '365' day) then 'alarm'
else 'ok'
end as status,
case
when a.lifecycle_state = 'PENDING_DELETION' then a.key_name || ' in ' || a.vault_name || ' vault scheduled for deletion.'
when a.lifecycle_state != 'ENABLED' then a.key_name || ' of ' || a.vault_name || ' in ' || lower(a.lifecycle_state) || ' state.'
when last_version_created_date <= (current_date - interval '365' day)
then a.key_name || ' in ' || a.vault_name || ' vault not rotated since ' || (date(current_timestamp) - date(last_version_created_date)) || ' days.'
else a.key_name || ' in ' || a.vault_name || ' vault last rotation age ' || (date(current_timestamp) - date(last_version_created_date)) || ' days.'
end as reason
, a.region as region, a.tenant_name as tenant
, coalesce(c.name, 'root') as compartment
from
active_key_table a
left join oci_identity_compartment c on c.id = a.compartment_id;

Controls

The query is being used by the following controls: