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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_enabled	boolean	!=, =	True if the account is enabled; otherwise, false.
approximate_last_sign_in_date_time	timestamp with time zone		The timestamp type represents date and time information using ISO 8601 format and is always in UTC time.
device_id	text		Unique identifier set by Azure Device Registration Service at the time of registration.
display_name	text	=	The name displayed for the device.
extension_attributes	jsonb		Contains 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.
filter	text	=	Odata query to search for resources.
id	text	=	The unique identifier for the device. Inherited from directoryObject.
is_compliant	boolean		True if the device is compliant; otherwise, false.
is_managed	boolean		True if the device is managed; otherwise, false.
mdm_app_id	text		Application identifier used to register device into MDM.
member_of	jsonb		A list the groups and directory roles that the device is a direct member of.
operating_system	text	=	The type of operating system on the device.
operating_system_version	text	=	The version of the operating system on the device.
profile_type	text	=	A string value that can be used to classify device types.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tenant_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Tenant ID where the resource is located.
title	text		Title of the resource.
trust_type	text	=	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