steampipe plugin install azuread

Table: azuread_device - Query Azure Active Directory Devices using SQL

Azure Active Directory (Azure AD) is a Microsoft cloud-based identity and access management service. It provides an identity platform with enhanced security, access management, scalability, and reliability for connecting users with all the apps they need. Azure AD Devices are the registered devices within an organization that can access resources in the directory.

Table Usage Guide

The azuread_device table provides insights into registered devices within Azure Active Directory. As a system administrator, explore device-specific details through this table, including device id, device type, and associated metadata. Utilize it to uncover information about devices, such as their operating system, physical device id, and the user who registered the device.

Examples

Basic info

Explore which devices in your Azure Active Directory are managed and compliant, as well as their group memberships. This is useful for maintaining security standards and managing device access within your organization.

select
display_name,
is_managed,
is_compliant,
member_of
from
azuread_device;
select
display_name,
is_managed,
is_compliant,
member_of
from
azuread_device;

List managed devices

Explore which devices are managed within your Azure Active Directory. This allows you to gain insights into the device profiles, including their operating system versions, for better management and security compliance.

select
display_name,
profile_type,
id,
operating_system,
operating_system_version
from
azuread_device
where
is_managed;
select
display_name,
profile_type,
id,
operating_system,
operating_system_version
from
azuread_device
where
is_managed = 1;

List non-compliant devices

Explore which devices in your Azure Active Directory are not compliant with your organization's standards. This can help you identify potential security risks and take necessary corrective actions.

select
display_name,
profile_type,
id,
operating_system,
operating_system_version
from
azuread_device
where
not is_compliant;
select
display_name,
profile_type,
id,
operating_system,
operating_system_version
from
azuread_device
where
is_compliant = 0;

Schema for azuread_device

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_enabledboolean!=, =True if the account is enabled; otherwise, false.
approximate_last_sign_in_date_timetimestamp with time zoneThe timestamp type represents date and time information using ISO 8601 format and is always in UTC time.
device_idtextUnique identifier set by Azure Device Registration Service at the time of registration.
display_nametext=The name displayed for the device.
extension_attributesjsonbContains extension attributes 1-15 for the device. The individual extension attributes are not selectable. These properties are mastered in cloud and can be set during creation or update of a device object in Azure AD.
filtertext=Odata query to search for resources.
idtext=The unique identifier for the device. Inherited from directoryObject.
is_compliantbooleanTrue if the device is compliant; otherwise, false.
is_managedbooleanTrue if the device is managed; otherwise, false.
mdm_app_idtextApplication identifier used to register device into MDM.
member_ofjsonbA list the groups and directory roles that the device is a direct member of.
operating_systemtext=The type of operating system on the device.
operating_system_versiontext=The version of the operating system on the device.
profile_typetext=A string value that can be used to classify device types.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.
trust_typetext=Type of trust for the joined device. Possible values: Workplace (indicates bring your own personal devices), AzureAd (Cloud only joined devices), ServerAd (on-premises domain joined devices joined to Azure AD).

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_device