turbot/azure_compliance

Query: monitor_log_alert_for_administrative_operations

Usage

powerpipe query azure_compliance.query.monitor_log_alert_for_administrative_operations

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.Sql/servers/firewallRules/write"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Sql/servers/firewallRules/delete"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Network/networkSecurityGroups/write"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Network/networkSecurityGroups/delete"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.ClassicNetwork/networkSecurityGroups/write"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.ClassicNetwork/networkSecurityGroups/delete"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Network/networkSecurityGroups/securityRules/write"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Network/networkSecurityGroups/securityRules/delete"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.ClassicNetwork/networkSecurityGroups/securityRules/write"}]'
or alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.ClassicNetwork/networkSecurityGroups/securityRules/delete"}]'
)
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 administrative operations.'
else 'Activity log alert does not exists for administrative operations.'
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: