Table: azuread_conditional_access_policy - Query Azure AD Conditional Access Policies using SQL

Azure AD Conditional Access is a feature in Azure Active Directory that allows administrators to define policies that control access to applications and resources based on conditions. These conditions can include user role, location, device status, and risk level. This feature is crucial for managing security and compliance in organizations.

Table Usage Guide

The azuread_conditional_access_policy table provides insights into Conditional Access Policies within Azure Active Directory. As a security administrator, you can explore policy-specific details through this table, including conditions, grant controls, and associated metadata. Utilize it to uncover information about policies, such as those with specific conditions and controls, helping you to maintain security and compliance within your organization.

Examples

Basic info

Analyze the settings to understand the status and creation date of the built-in controls in your Azure Active Directory conditional access policy. This can help you assess the elements within your policy and make necessary adjustments.

select
  id,
  display_name,
  state,
  created_date_time,
  built_in_controls
from
  azuread_conditional_access_policy;

select
  id,
  display_name,
  state,
  created_date_time,
  built_in_controls
from
  azuread_conditional_access_policy;

List conditional access policies with mfa enabled

Uncover the details of conditional access policies that have multi-factor authentication enabled. This is useful for enhancing security by identifying policies that require an additional layer of verification.

select
  id,
  display_name,
  built_in_controls
from
  azuread_conditional_access_policy
where
  built_in_controls ? & array [ 'mfa' ];

Error: SQLite does not support array operations
and '?&' operator.

Control examples

Schema for azuread_conditional_access_policy

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
application_enforced_restrictions	jsonb		Session control to enforce application restrictions. Only Exchange Online and Sharepoint Online support this session control.
applications	jsonb		Applications and user actions included in and excluded from the policy.
authentication_strength	jsonb		List combinations of authentication methods allowed by the policy. For example: password, Federated Multi-Factor, FIDO2 security key
built_in_controls	jsonb		List of values of built-in controls required by the policy. Possible values: block, mfa, compliantDevice, domainJoinedDevice, approvedApplication, compliantApplication, passwordChange, unknownFutureValue.
client_app_types	jsonb		Client application types included in the policy. Possible values are: all, browser, mobileAppsAndDesktopClients, exchangeActiveSync, easSupported, other.
cloud_app_security	jsonb		Session control to apply cloud app security.
created_date_time	timestamp with time zone		The create date of the conditional access policy.
custom_authentication_factors	jsonb		List of custom controls IDs required by the policy.
display_name	text	=	Specifies a display name for the conditionalAccessPolicy object.
id	text	=	Specifies the identifier of a conditionalAccessPolicy object.
locations	jsonb		Locations included in and excluded from the policy.
modified_date_time	timestamp with time zone		The modification date of the conditional access policy.
operator	text		Defines the relationship of the grant controls. Possible values: AND, OR.
persistent_browser	jsonb		Session control to define whether to persist cookies or not. All apps should be selected for this session control to work correctly.
platforms	jsonb		Platforms included in and excluded from the policy.
sign_in_frequency	jsonb		Session control to enforce signin frequency.
sign_in_risk_levels	jsonb		Sign-in risk levels included in the policy. Possible values are: low, medium, high, hidden, none, unknownFutureValue.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text	=	Specifies the state of the conditionalAccessPolicy object. Possible values are: enabled, disabled, enabledForReportingButNotEnforced.
tenant_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Tenant ID where the resource is located.
terms_of_use	jsonb		List of terms of use IDs required by the policy.
title	text		Title of the resource.
user_risk_levels	jsonb		User risk levels included in the policy. Possible values are: low, medium, high, hidden, none, unknownFutureValue.
users	jsonb		Users, groups, and roles included in and excluded from the policy.

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)" -- azuread

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

steampipe_export_azuread --config '<your_config>' azuread_conditional_access_policy