steampipe plugin install azuread

Table: azuread_user - Query Azure AD Users using SQL

Azure Active Directory (Azure AD) is Microsoft's multi-tenant, cloud-based directory, and identity management service. It combines core directory services, application access management, and identity protection into a single solution. Azure AD is the backbone of the Office 365 system, and it can sync with on-premise Active Directory.

Table Usage Guide

The azuread_user table provides insights into user profiles within Azure Active Directory. As a system administrator, explore user-specific details through this table, including user identities, user principal names, and associated metadata. Utilize it to uncover information about users, such as their display names, job titles, and the verification of user identities.

Examples

Basic info

Explore the basic information of users in your Azure Active Directory. This can be useful for understanding the user composition of your organization, including their display names, principal names, IDs, given names, and emails.

select
display_name,
user_principal_name,
id,
given_name,
mail
from
azuread_user;
select
display_name,
user_principal_name,
id,
given_name,
mail
from
azuread_user;

List guest users

Discover the segments that consist of guest users within your Azure Active Directory, allowing you to better manage and monitor these specific user accounts. This is particularly useful in maintaining security protocols and ensuring guest users have appropriate access permissions.

select
display_name,
user_principal_name,
id,
mail
from
azuread_user
where
user_type = 'Guest';
select
display_name,
user_principal_name,
id,
mail
from
azuread_user
where
user_type = 'Guest';

List disabled users

Discover the segments that consist of disabled user accounts within the Azure Active Directory. This can be useful in monitoring and managing user accessibility for security and compliance purposes.

select
display_name,
user_principal_name,
id,
mail
from
azuread_user
where
not account_enabled;
select
display_name,
user_principal_name,
id,
mail
from
azuread_user
where
account_enabled = 0;

List users with access to directory roles

Discover the segments that have access to directory roles to better manage permissions and security protocols. This is particularly useful for administrators seeking to optimize access control and understand user-role relationships.

select
u.display_name as username,
role.display_name as directory_role
from
azuread_directory_role as role,
jsonb_array_elements_text(member_ids) as m_id,
azuread_user as u
where
u.id = m_id;
select
u.display_name as username,
role.display_name as directory_role
from
azuread_directory_role as role,
json_each(role.member_ids) as m_id,
azuread_user as u
where
u.id = m_id.value;

List users with information of groups they are attached

Discover the segments that outline the association between users and groups in your Azure Active Directory. This query is useful for assessing user-group relationships, aiding in the management of access and permissions.

select
grp.display_name as group_name,
grp.id as group_id,
u.display_name as username,
u.user_principal_name as user_principal_name,
u.id as user_id
from
azuread_group as grp,
jsonb_array_elements_text(member_ids) as m_id,
azuread_user as u
where
u.id = m_id
order by
group_id,
username;
select
grp.display_name as group_name,
grp.id as group_id,
u.display_name as username,
u.user_principal_name as user_principal_name,
u.id as user_id
from
azuread_group as grp,
json_each(grp.member_ids) as m_id,
azuread_user as u
where
u.id = m_id.value
order by
group_id,
username;

Control examples

Schema for azuread_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_enabledboolean!=, =True if the account is enabled; otherwise, false.
created_date_timetimestamp with time zoneThe time at which the user was created.
departmenttextThe name of the department in which the user works.
display_nametext=The name displayed in the address book for the user. This is usually the combination of the user's first name, middle initial and last name.
filtertext=Odata query to search for resources.
given_nametextThe given name (first name) of the user.
idtext=The unique identifier for the user. Should be treated as an opaque identifier.
im_addressesjsonbThe instant message voice over IP (VOIP) session initiation protocol (SIP) addresses for the user.
mailtextThe SMTP address for the user, for example, jeff@contoso.onmicrosoft.com.
mail_nicknametextThe mail alias for the user.
member_ofjsonbA list the groups and directory roles that the user is a direct member of.
on_premises_immutable_idtextUsed to associate an on-premises Active Directory user account with their Azure AD user object.
other_mailsjsonbA list of additional email addresses for the user.
password_policiestextSpecifies password policies for the user. This value is an enumeration with one possible value being DisableStrongPassword, which allows weaker passwords than the default policy to be specified. DisablePasswordExpiration can also be specified. The two may be specified together; for example: DisablePasswordExpiration, DisableStrongPassword.
password_profilejsonbSpecifies the password profile for the user. The profile contains the user’s password. This property is required when a user is created.
sign_in_sessions_valid_from_date_timetimestamp with time zoneAny refresh tokens or sessions tokens (session cookies) issued before this time are invalid, and applications will get an error when using an invalid refresh or sessions token to acquire a delegated access token (to access APIs such as Microsoft Graph).
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
surnametext=Family name or last name of the active directory user.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
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_nametext=Principal email of the active directory user.
user_typetext=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)" -- azuread

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

steampipe_export_azuread --config '<your_config>' azuread_user