steampipe plugin install azuread

Table: azuread_group - Query Azure Active Directory Groups 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 also offers a rich, standards-based platform that enables developers to deliver access control to their applications, based on centralized policy and rules.

Table Usage Guide

The azuread_group table provides insights into groups within Microsoft's Azure Active Directory. As an IT administrator, you can explore group-specific details through this table, including the group's ID, display name, security identifier, and more. Utilize it to uncover information about groups, such as their membership and associated metadata, aiding in the management and security of your organization's resources.

Examples

Basic info

Explore which Azure Active Directory groups are present in your system, along with their associated email addresses. This can be useful for understanding your group structure and managing group communication.

select
display_name,
id,
description,
mail
from
azuread_group;
select
display_name,
id,
description,
mail
from
azuread_group;

List groups with public visibility

Explore which user groups within your Azure Active Directory have been set to public visibility. This can help in managing data security and privacy by identifying potential areas of risk.

select
display_name,
id,
description,
mail
from
azuread_group
where
visibility = 'Public';
select
display_name,
id,
description,
mail
from
azuread_group
where
visibility = 'Public';

List security enabled groups

Explore which groups in your Azure Active Directory have security features enabled. This information is useful for auditing purposes and ensuring adherence to security protocols.

select
display_name,
id,
description,
mail
from
azuread_group
where
security_enabled;
select
display_name,
id,
description,
mail
from
azuread_group
where
security_enabled = 1;

List groups that can be assigned to roles

Explore which groups within your Azure Active Directory can be assigned to roles. This enables better management of access permissions, ensuring appropriate role assignments within your organization.

select
display_name,
id,
description,
mail
from
azuread_group
where
is_assignable_to_role;
select
display_name,
id,
description,
mail
from
azuread_group
where
is_assignable_to_role = 1;

Get owner details of an specific group

Discover the segments that identify the owner of a specific group in the Azure Active Directory. This could be useful in scenarios where you need to understand access control and ownership structures within your organization.

select
gr.display_name as group_name,
u.display_name as user_name,
owner_id
from
azuread_user u,
azuread_group gr,
jsonb_array_elements_text(gr.owner_ids) as owner_id
where
owner_id = u.id
and gr.display_name = 'turbot'
order by
user_name;
select
gr.display_name as group_name,
u.display_name as user_name,
owner_id.value as owner_id
from
azuread_user u,
azuread_group gr,
json_each(gr.owner_ids) as owner_id
where
owner_id.value = u.id
and gr.display_name = 'turbot'
order by
user_name;

Schema for azuread_group

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
assigned_labelsjsonbThe list of sensitivity label pairs (label ID, label name) associated with a Microsoft 365 group.
classificationtextDescribes a classification for the group (such as low, medium or high business impact).
created_date_timetimestamp with time zoneThe time at which the group was created.
descriptiontextAn optional description for the group.
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.
expiration_date_timetimestamp with time zoneTimestamp of when the group is set to expire.
filtertext=Odata query to search for groups.
group_typesjsonbSpecifies the group type and its membership. If the collection contains Unified, the group is a Microsoft 365 group; otherwise, it's either a security group or distribution group. For details, see [groups overview](https://docs.microsoft.com/en-us/graph/api/resources/groups-overview?view=graph-rest-1.0).
idtext=The unique identifier for the group.
is_assignable_to_rolebooleanIndicates whether this group can be assigned to an Azure Active Directory role or not.
is_subscribed_by_mailbooleanIndicates whether the signed-in user is subscribed to receive email conversations. Default value is true.
mailtext=The SMTP address for the group, for example, "serviceadmins@contoso.onmicrosoft.com".
mail_enabledboolean!=, =Specifies whether the group is mail-enabled.
mail_nicknametextThe mail alias for the user.
member_idsjsonbId of Users and groups that are members of this group.
membership_ruletextThe mail alias for the group, unique in the organization.
membership_rule_processing_statetextIndicates whether the dynamic membership processing is on or paused. Possible values are On or Paused.
on_premises_domain_nametextContains the on-premises Domain name synchronized from the on-premises directory.
on_premises_last_sync_date_timetimestamp with time zoneIndicates the last time at which the group was synced with the on-premises directory.
on_premises_net_bios_nametextContains the on-premises NetBiosName synchronized from the on-premises directory.
on_premises_sam_account_nametextContains the on-premises SAM account name synchronized from the on-premises directory.
on_premises_security_identifiertextContains the on-premises security identifier (SID) for the group that was synchronized from on-premises to the cloud.
on_premises_sync_enabledboolean!=, =True if this group is synced from an on-premises directory; false if this group was originally synced from an on-premises directory but is no longer synced; null if this object has never been synced from an on-premises directory (default).
owner_idsjsonbId od the owners of the group. The owners are a set of non-admin users who are allowed to modify this object.
proxy_addressesjsonbEmail addresses for the group that direct to the same group mailbox. For example: ["SMTP: bob@contoso.com", "smtp: bob@sales.contoso.com"]. The any operator is required to filter expressions on multi-valued properties.
renewed_date_timetimestamp with time zoneTimestamp of when the group was last renewed. This cannot be modified directly and is only updated via the renew service action.
resource_behavior_optionsjsonbSpecifies the group behaviors that can be set for a Microsoft 365 group during creation. Possible values are AllowOnlyMembersToPost, HideGroupInOutlook, SubscribeNewGroupMembers, WelcomeEmailDisabled.
resource_provisioning_optionsjsonbSpecifies the group resources that are provisioned as part of Microsoft 365 group creation, that are not normally part of default group creation. Possible value is Team.
security_enabledboolean!=, =Specifies whether the group is a security group.
security_identifiertextSecurity identifier of the group, used in Windows scenarios.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.
visibilitytextSpecifies the group join policy and group content visibility for groups. Possible values are: Private, Public, or Hiddenmembership.

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_group