Table: azure_ad_user - Query Azure Active Directory Users using SQL

Azure Active Directory (Azure AD) is Microsoft's cloud-based identity and access management service. It helps your employees sign in and access resources in external resources, such as Microsoft Office 365, the Azure portal, and thousands of other SaaS applications. Azure AD also includes a full suite of identity management capabilities including multi-factor authentication, device registration, role-based access control, user provisioning, and more.

Table Usage Guide

The azure_ad_user table provides insights into user profiles within Azure Active Directory. As a system administrator, explore user-specific details through this table, including user details, email addresses, and department information. Utilize it to uncover information about users, such as their roles, access controls, and associated metadata.

Examples

Basic active directory user info

Determine the areas in which active directory users are currently active within the Azure environment. This query is beneficial in managing user access and maintaining security standards.

select
  display_name,
  user_principal_name,
  given_name,
  mail,
  account_enabled,
  object_id
from
  azure_ad_user;

select
  display_name,
  user_principal_name,
  given_name,
  mail,
  account_enabled,
  object_id
from
  azure_ad_user;

List of guest users in the active directory

Identify instances where guest users are present in the active directory to maintain security and access control. This query is useful in managing permissions and keeping track of external users in your system.

select
  display_name,
  user_principal_name,
  mail,
  user_type,
  usage_location
from
  azure_ad_user
where
  user_type = 'Guest';

select
  display_name,
  user_principal_name,
  mail,
  user_type,
  usage_location
from
  azure_ad_user
where
  user_type = 'Guest';

Password profile info of each user

This example helps in understanding the password policies applied to each user within the Azure Active Directory. It aids in determining whether users are required to change their passwords at their next login or if the password change policy is enforced, thereby assisting in maintaining security standards.

select
  display_name,
  user_principal_name,
  additional_properties -> 'passwordProfile' -> 'enforceChangePasswordPolicy' as enforce_change_password_policy,
  additional_properties -> 'passwordProfile' -> 'forceChangePasswordNextLogin' as change_password_next_login
from
  azure_ad_user;

select
  display_name,
  user_principal_name,
  json_extract(
    additional_properties,
    '$.passwordProfile.enforceChangePasswordPolicy'
  ) as enforce_change_password_policy,
  json_extract(
    additional_properties,
    '$.passwordProfile.forceChangePasswordNextLogin'
  ) as change_password_next_login
from
  azure_ad_user;

Schema for azure_ad_user

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_enabled	boolean		Specifies the account status of the active directory user.
additional_properties	jsonb		A list of unmatched properties from the message are deserialized this collection.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
deletion_timestamp	timestamp with time zone		The time at which the directory object was deleted.
display_name	text		A friendly name that identifies an active directory user.
given_name	text		The given name(first name) of the active directory user.
immutable_id	text		Used to associate an on-premises Active Directory user account with their Azure AD user object.
mail	text		The SMTP address for the user.
mail_nickname	text		The mail alias for the user.
object_id	text		The unique ID that identifies an active directory user.
object_type	text		A string that identifies the object type.
sign_in_names	jsonb		A list of sign-in names for a local account in an Azure Active Directory B2C tenant.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
surname	text		Family name or last name of the active directory user.
title	text		Title of the resource.
usage_location	text		A two letter country code (ISO standard 3166), required for users that will be assigned licenses due to legal requirement to check for availability of services in countries.
user_principal_name	text		Principal email of the active directory user.
user_type	text		A string value that can be used to classify user types in your directory.

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_ad_user