Run individual configuration, compliance and security controls or full compliance benchmarks for CIS across all of your Oracle Cloud Infrastructure accounts using Powerpipe and Steampipe.
Results
```
with active_key_table as (
select
k.name as key_name,
k.id,
k.compartment_id,
k.vault_name,
k.lifecycle_state,
k.region,
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.compartment_id
)
select
-- Required Columns
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 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 reason,
-- Additional Dimensions
region,
coalesce(c.name, 'root') as compartment
from
active_key_table a
left join oci_identity_compartment c on c.id = a.compartment_id
+------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------
| resource | status | reason
+------------------------------------------------------------------------------------------------------+--------+---------------------------------------------------------------------------------------------------
| ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrlgvsmqmgimta65rfzkjm6tq62advflwusnqn3eq4h7jgyuyvh7tq | skip | cmk-1 in keyvault1 vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrulnecvwaogob6vsho4v5p6vlgojv25ptcfahb6ipuayjmltqrmsq | skip | ert in keyvault1 vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljr7pe564lb4jnhdfttnahim27bbpyu246pxfi5237rdbemv3kdui7q | skip | hsmbased in raj-jun21-vault vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljrt6b2q4e2d42eq3iq3vxxmqyp6v6y5uiwxmytod7nys2dsvhl2aiq | ok | software-cmk-raj in raj-jun21-vault vault last rotation age 1 days.
| ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljrgpcyylilosatvrvlajeboplc4ih6kwuarmoebi35aj352gbvk6kq | skip | something in raj-jun21-vault vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qm4daqaabm6.abrg6ljr2luum35fmg2p3getikl3ptajjaagi3t7xovd6hyz7h22soxnjcoa | skip | steampipetest2145 in steampipetest2145 vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qm4cuaaabm6.abrg6ljrz6y7uq3pk3phazeb4vtvmfoqgvkzrlvxorwdjx5d6krtxao3pktq | skip | steampipetest5614 in steampipetest5614 vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qh7uqnaadbg.abrg6ljrz6klztlznivgugu3qirfcihxhgxeri4lwzapdb32pgi2ui5rtara | skip | tes-vis-key in turbot-test-vault vault scheduled for deletion.
| ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrwmis6ahhmfotktqnvk4tddbgcoutd6juixw5x3jxyzy55k54iptq | ok | test-sw-jun28 in keyvault1 vault last rotation age 1 days.
| ocid1.key.oc1.iad.bfqh752yaaeug.abuwcljtf7sw7pwwbz5y6age2vn6yvoqrigfyi5ajpyjpghjvsoyqkbzrbna | ok | test_cmk in vis-turbot-vault vault last rotation age 12 days.
| ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrh4za7324vk6sxaie7bahd6qvd65mgsrbo6w5bxqbmtqoecgaaumq | ok | test_rajesh in keyvault1 vault last rotation age 1 days.
| ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljrrdiozpg5k3thul6etkorj7spsi4bhq6mv2sjzmu3diyip54ypfsq | ok | testsw in raj-jun21-vault vault last rotation age 1 days.
| ocid1.key.oc1.ap-mumbai-1.b5qm4ef7aabm6.abrg6ljri3wjpw4ht4zf4c6lt57zk4yj5uyicyiqcgtpeuxvfwo5ovw2so5a | ok | turbot-test-20200125-create-update in turbot-test-20200125-create-update vault last rotation age 7
| ocid1.key.oc1.iad.bfqh752yaaeug.abuwcljro3p57c6qupmmhp7ph2cqlrd5rua2zwmawe7nczvv6f7moqckjefa | ok | vishal-key in vis-turbot-vault vault last rotation age 7 days.
| ocid1.key.oc1.ap-mumbai-1.b5qk7cvraahnc.abrg6ljrcoihedjwtswuxry3w3rjsuvkjkrkuwsymctx4bb54jawzpsdjcla | ok | wewrt in ssew vault last rotation age 6 days.
+------------------------------------------------------------------------------------------------------+--------+-----------
```
Checklist
Example query results
Results
``` with active_key_table as ( select k.name as key_name, k.id, k.compartment_id, k.vault_name, k.lifecycle_state, k.region, 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.compartment_id ) select -- Required Columns 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 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 reason, -- Additional Dimensions region, coalesce(c.name, 'root') as compartment from active_key_table a left join oci_identity_compartment c on c.id = a.compartment_id +------------------------------------------------------------------------------------------------------+--------+--------------------------------------------------------------------------------------------------- | resource | status | reason +------------------------------------------------------------------------------------------------------+--------+--------------------------------------------------------------------------------------------------- | ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrlgvsmqmgimta65rfzkjm6tq62advflwusnqn3eq4h7jgyuyvh7tq | skip | cmk-1 in keyvault1 vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrulnecvwaogob6vsho4v5p6vlgojv25ptcfahb6ipuayjmltqrmsq | skip | ert in keyvault1 vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljr7pe564lb4jnhdfttnahim27bbpyu246pxfi5237rdbemv3kdui7q | skip | hsmbased in raj-jun21-vault vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljrt6b2q4e2d42eq3iq3vxxmqyp6v6y5uiwxmytod7nys2dsvhl2aiq | ok | software-cmk-raj in raj-jun21-vault vault last rotation age 1 days. | ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljrgpcyylilosatvrvlajeboplc4ih6kwuarmoebi35aj352gbvk6kq | skip | something in raj-jun21-vault vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qm4daqaabm6.abrg6ljr2luum35fmg2p3getikl3ptajjaagi3t7xovd6hyz7h22soxnjcoa | skip | steampipetest2145 in steampipetest2145 vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qm4cuaaabm6.abrg6ljrz6y7uq3pk3phazeb4vtvmfoqgvkzrlvxorwdjx5d6krtxao3pktq | skip | steampipetest5614 in steampipetest5614 vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qh7uqnaadbg.abrg6ljrz6klztlznivgugu3qirfcihxhgxeri4lwzapdb32pgi2ui5rtara | skip | tes-vis-key in turbot-test-vault vault scheduled for deletion. | ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrwmis6ahhmfotktqnvk4tddbgcoutd6juixw5x3jxyzy55k54iptq | ok | test-sw-jun28 in keyvault1 vault last rotation age 1 days. | ocid1.key.oc1.iad.bfqh752yaaeug.abuwcljtf7sw7pwwbz5y6age2vn6yvoqrigfyi5ajpyjpghjvsoyqkbzrbna | ok | test_cmk in vis-turbot-vault vault last rotation age 12 days. | ocid1.key.oc1.ap-mumbai-1.b5qgyadgaabm6.abrg6ljrh4za7324vk6sxaie7bahd6qvd65mgsrbo6w5bxqbmtqoecgaaumq | ok | test_rajesh in keyvault1 vault last rotation age 1 days. | ocid1.key.oc1.ap-mumbai-1.b5qmwhquaabm6.abrg6ljrrdiozpg5k3thul6etkorj7spsi4bhq6mv2sjzmu3diyip54ypfsq | ok | testsw in raj-jun21-vault vault last rotation age 1 days. | ocid1.key.oc1.ap-mumbai-1.b5qm4ef7aabm6.abrg6ljri3wjpw4ht4zf4c6lt57zk4yj5uyicyiqcgtpeuxvfwo5ovw2so5a | ok | turbot-test-20200125-create-update in turbot-test-20200125-create-update vault last rotation age 7 | ocid1.key.oc1.iad.bfqh752yaaeug.abuwcljro3p57c6qupmmhp7ph2cqlrd5rua2zwmawe7nczvv6f7moqckjefa | ok | vishal-key in vis-turbot-vault vault last rotation age 7 days. | ocid1.key.oc1.ap-mumbai-1.b5qk7cvraahnc.abrg6ljrcoihedjwtswuxry3w3rjsuvkjkrkuwsymctx4bb54jawzpsdjcla | ok | wewrt in ssew vault last rotation age 6 days. +------------------------------------------------------------------------------------------------------+--------+----------- ```