turbot/azure_compliance

Query: mssql_managed_instance_vulnerability_assessment_enabled

Usage

powerpipe query azure_compliance.query.mssql_managed_instance_vulnerability_assessment_enabled

SQL

with vulnerability_assessments as (
select
distinct i.id as id
from
azure_mssql_managed_instance as i,
jsonb_array_elements(vulnerability_assessments) a
where
a -> 'recurringScans' ->> 'isEnabled' = 'true'
and a ->> 'name' = 'Default'
)
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 || ' vulnerability assessment enabled.'
else s.title || ' vulnerability assessment disabled.'
end as reason
, s.resource_group as resource_group
, sub.display_name as subscription
from
azure_mssql_managed_instance as s
left join vulnerability_assessments 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: