Table: azure_policy_assignment - Query Azure Policy Assignments using SQL

A Policy Assignment in Azure is a security tool that enables operators to apply a policy definition to a resource or a set of resources. The assignment is the process of binding a policy definition to a specific scope. This scope could range from a management group to a resource group.

Table Usage Guide

The azure_policy_assignment table provides insights into Policy Assignments within Azure Policy. As a Security Analyst, explore specific details through this table, including policy definitions, scopes, and compliance statuses. Utilize it to uncover information about policy assignments, such as those associated with specific resources, the scope of these assignments, and their compliance status.

Examples

Basic info

Explore the policies assigned within your Azure environment to ensure adherence to your organization's governance and compliance requirements. This can help identify any instances where policies may not be correctly applied, potentially exposing your environment to risks.

select
  id,
  policy_definition_id,
  name,
  type
from
  azure_policy_assignment;

select
  id,
  policy_definition_id,
  name,
  type
from
  azure_policy_assignment;

Get SQL auditing and threat detection monitoring status for the subscription

Explore the status of SQL auditing and threat detection monitoring for your subscription. This query helps you assess whether these important security measures are active, promoting better risk management and data protection.

select
  id,
  policy_definition_id,
  display_name,
  parameters -> 'sqlAuditingMonitoringEffect' -> 'value' as sqlAuditingMonitoringEffect
from
  azure_policy_assignment;

select
  id,
  policy_definition_id,
  display_name,
  json_extract(
    json_extract(parameters, '$.sqlAuditingMonitoringEffect'),
    '$.value'
  ) as sqlAuditingMonitoringEffect
from
  azure_policy_assignment;

Get SQL encryption monitoring status for the subscription

Explore the status of SQL encryption monitoring for your subscription. This can help in maintaining the security of your data by keeping an eye on the encryption status.

select
  id,
  policy_definition_id,
  display_name,
  parameters -> 'sqlEncryptionMonitoringEffect' -> 'value' as sqlEncryptionMonitoringEffect
from
  azure_policy_assignment;

select
  id,
  policy_definition_id,
  display_name,
  json_extract(
    json_extract(parameters, '$.sqlEncryptionMonitoringEffect'),
    '$.value'
  ) as sqlEncryptionMonitoringEffect
from
  azure_policy_assignment;

Control examples

Schema for azure_policy_assignment

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
cloud_environment	text		The Azure Cloud Environment.
description	text		This message will be part of response in case of policy violation.
display_name	text		The display name of the policy assignment.
enforcement_mode	text		The policy assignment enforcement mode. Possible values are Default and DoNotEnforce.
id	text	=	The ID of the policy assignment.
identity	jsonb		The managed identity associated with the policy assignment.
metadata	jsonb		The policy assignment metadata.
name	text		The name of the policy assignment.
not_scopes	jsonb		The policy's excluded scopes.
parameters	jsonb		The parameter values for the assigned policy rule.
policy_definition_id	text		The ID of the policy definition or policy set definition being assigned.
scope	text		The scope for the policy assignment.
sku_name	text		The name of the policy sku.
sku_tier	text		The policy sku tier.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
subscription_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Subscription ID in which the resource is located.
title	text		Title of the resource.
type	text		The type of the policy assignment.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_policy_assignment