turbot/azure_insights

Query: sql_servers_for_key_vault_key

Usage

powerpipe query azure_insights.query.sql_servers_for_key_vault_key

SQL

with sql_server as (
select
ep ->> 'uri' as uri,
id,
title,
name,
type,
region,
resource_group,
subscription_id
from
azure_sql_server,
jsonb_array_elements(encryption_protector) as ep
where
ep ->> 'kind' = 'azurekeyvault'
)
select
lower(s.id) as sql_server_id
from
azure_key_vault_key_version as v
left join sql_server as s on v.key_uri_with_version = s.uri
where
s.uri is not null
and lower(split_part(v.id, '/versions', 1)) = $1
and v.subscription_id = split_part($1, '/', 3);