turbot/azure_compliance

Query: mssql_managed_instance_encryption_at_rest_using_cmk

Usage

powerpipe query azure_compliance.query.mssql_managed_instance_encryption_at_rest_using_cmk

SQL

with encryption_protector as (
select
distinct i.id as id
from
azure_mssql_managed_instance as i,
jsonb_array_elements(encryption_protectors) a
where
a ->> 'serverKeyType' = 'AzureKeyVault'
and a ->> 'uri' is not null
)
select
s.id as resource,
case
when a.id is not null then 'ok'
else 'alarm'
end as status,
case
when a.id is not null then s.title || ' encrypted with CMK.'
else s.title || ' not encrypted with CMK.'
end as reason
, s.resource_group as resource_group
, sub.display_name as subscription
from
azure_mssql_managed_instance as s
left join encryption_protector as a on s.id = a.id,
azure_subscription as sub
where
sub.subscription_id = s.subscription_id;

Controls

The query is being used by the following controls: