turbot/azure_compliance

ad_guest_user_reviewed_monthlyad_manual_controlappservice_authentication_enabledappservice_ftp_deployment_disabledappservice_web_app_incoming_client_cert_onappservice_web_app_latest_http_versionappservice_web_app_latest_tls_versionappservice_web_app_register_with_active_directory_enabledappservice_web_app_use_httpscompute_os_and_data_disk_encrypted_with_cmkcompute_unattached_disk_encrypted_with_cmkcompute_vm_utilizing_managed_diskiam_no_custom_subscription_owner_roles_createdkeyvault_key_expiration_setkeyvault_logging_enabledkeyvault_secret_expiration_setkeyvault_vault_recoverablekubernetes_instance_rbac_enabledmanual_controlmonitor_diagnostic_settings_captures_proper_categoriesmonitor_log_alert_create_policy_assignmentmonitor_log_alert_create_update_nsgmonitor_log_alert_create_update_nsg_rulemonitor_log_alert_create_update_security_solutionmonitor_log_alert_delete_nsgmonitor_log_alert_delete_nsg_rulemonitor_log_alert_delete_policy_assignmentmonitor_log_alert_delete_security_solutionmonitor_log_alert_sql_firewall_rulemonitor_logs_storage_container_encryptes_with_byokmonitor_logs_storage_container_not_public_accessiblemysql_ssl_enablednetwork_security_group_rdp_access_restrictednetwork_security_group_ssh_access_restrictednetwork_security_group_udp_service_restrictednetwork_sg_flowlog_retention_period_greater_than_90network_watcher_enabledpostgres_db_server_connection_throttling_onpostgres_db_server_log_checkpoints_onpostgres_db_server_log_connections_onpostgres_db_server_log_disconnections_onpostgres_db_server_log_retention_days_3postgres_sql_ssl_enabledsecuritycenter_additional_email_configuredsecuritycenter_asc_default_setting_not_disabledsecuritycenter_automatic_provisioning_monitoring_agent_onsecuritycenter_azure_defender_on_for_appservicesecuritycenter_azure_defender_on_for_containerregistrysecuritycenter_azure_defender_on_for_k8ssecuritycenter_azure_defender_on_for_keyvaultsecuritycenter_azure_defender_on_for_serversecuritycenter_azure_defender_on_for_sqldbsecuritycenter_azure_defender_on_for_sqlservervmsecuritycenter_azure_defender_on_for_storagesecuritycenter_mcas_integrationsecuritycenter_notify_alerts_configuredsecuritycenter_security_alerts_to_owner_enabledsecuritycenter_wdatp_integrationsql_database_allow_internet_accesssql_db_active_directory_admin_configuredsql_server_and_databases_va_enabledsql_server_atp_enabledsql_server_auditing_onsql_server_audting_retention_period_90sql_server_tde_protector_cmk_encryptedsql_server_transparent_data_encryption_enabledsql_server_va_setting_periodic_scan_enabledsql_server_va_setting_reports_notify_adminssql_server_va_setting_scan_reports_configuredstorage_account_blob_containers_public_access_privatestorage_account_blob_service_logging_enabledstorage_account_default_network_access_rule_deniedstorage_account_encryption_at_rest_using_cmkstorage_account_queue_services_logging_enabledstorage_account_secure_transfer_required_enabledstorage_account_soft_delete_enabledstorage_account_trusted_microsoft_services_enabled

Query: monitor_log_alert_delete_nsg

Usage

steampipe query azure_compliance.query.monitor_log_alert_delete_nsg

SQL

with alert_rule as (
select
alert.id as alert_id,
alert.name as alert_name,
alert.enabled,
alert.location,
alert.subscription_id,
jsonb_array_length(alert.condition -> 'allOf')
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.network/networksecuritygroups"}]'
and alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Network/networkSecurityGroups/delete"}]'
)
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
-- Required Columns
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 delete Network Security Group event.'
else 'Activity log alert does not exists for delete Network Security Group event.'
end as reason,
-- Additional Dimensions
sub.display_name as subscription
from
azure_subscription sub
left join alert_rule a on sub.subscription_id = a.subscription_id
group by
sub.subscription_id,
sub.display_name;