turbot/azure_compliance

Query: monitor_log_alert_create_update_nsg

Usage

powerpipe query azure_compliance.query.monitor_log_alert_create_update_nsg

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": "operationName", "equals": "Microsoft.Network/networkSecurityGroups/write"}]'
)
or
(
alert.condition -> 'allOf' @> '[{"equals":"Administrative","field":"category"}]'
and alert.condition -> 'allOf' @> '[{"field": "resourceType", "equals": "microsoft.network/networksecuritygroups"}]'
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 or update Network Security Group event.'
else 'Activity log alert does not exists for create or update Network Security Group 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: