steampipe plugin install azure

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_enabledbooleanSpecifies the account status of the active directory user.
additional_propertiesjsonbA list of unmatched properties from the message are deserialized this collection.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
deletion_timestamptimestamp with time zone The time at which the directory object was deleted.
display_nametextA friendly name that identifies an active directory user.
given_nametextThe given name(first name) of the active directory user.
immutable_idtextUsed to associate an on-premises Active Directory user account with their Azure AD user object.
mailtextThe SMTP address for the user.
mail_nicknametextThe mail alias for the user.
object_idtextThe unique ID that identifies an active directory user.
object_typetextA string that identifies the object type.
sign_in_namesjsonbA list of sign-in names for a local account in an Azure Active Directory B2C tenant.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
surnametextFamily name or last name of the active directory user.
titletextTitle of the resource.
usage_locationtextA 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_nametextPrincipal email of the active directory user.
user_typetextA 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