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_create_update_nsg_rule

Usage

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