turbot/azure_compliance

Query: monitor_log_alert_sql_firewall_rule

Usage

powerpipe query azure_compliance.query.monitor_log_alert_sql_firewall_rule

SQL

with alert_rule as (
select
alert.id as alert_id,
alert.name as alert_name,
alert.enabled,
alert.location,
alert.subscription_id
from
azure_log_alert as alert,
jsonb_array_elements_text(scopes) as sc
where
alert.location = 'global'
and alert.enabled
and sc = '/subscriptions/' || alert.subscription_id
and alert.condition -> 'allOf' @> '[{"equals":"Administrative","field":"category"}]'
and alert.condition -> 'allOf' @> '[{"field": "resourceType", "equals": "microsoft.sql/servers"}]'
and jsonb_array_length(alert.condition -> 'allOf') = 2
limit 1
)
select
sub.subscription_id as resource,
case
when count(a.subscription_id) > 0 then 'ok'
else 'alarm'
end as status,
case
when count(a.subscription_id) > 0 then 'Activity log alert exists for create, update and delete SQL Server Firewall Rule event.'
else 'Activity log alert does not exists for create, update and delete SQL Server Firewall Rule event.'
end as reason
, sub.display_name as subscription
from
azure_subscription sub
left join alert_rule a on sub.subscription_id = a.subscription_id
group by
sub._ctx,
sub.subscription_id,
sub.display_name;

Controls

The query is being used by the following controls: