steampipe plugin install azuread

Table: azuread_service_principal - Query Azure Active Directory Service Principals 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. Service Principals in Azure AD are the security identities that user-created apps, services, and automation tools use to access specific Azure resources.

Table Usage Guide

The azuread_service_principal table provides insights into Service Principals within Azure Active Directory. As a security analyst or a DevOps engineer, explore details about the service principals through this table, including their roles, permissions, and other related information. Utilize it to uncover details about the service principals, such as their associated applications, permissions, and the roles they play in your Azure environment.

Examples

Basic info

Explore the relationship between display names and application names in your Azure Active Directory. This can be useful in understanding how your applications are connected and organized within the directory.

select
display_name,
id,
app_display_name
from
azuread_service_principal;
select
display_name,
id,
app_display_name
from
azuread_service_principal;

List disabled service principals

Uncover the details of disabled service principals within your Azure Active Directory. This is useful in ensuring that disabled accounts are not posing a security risk or cluttering your system.

select
display_name,
id
from
azuread_service_principal
where
not account_enabled;
select
display_name,
id
from
azuread_service_principal
where
account_enabled = 0;

Explore which service principals are directly related to applications in Azure Active Directory. This can be useful to determine which applications have active accounts, aiding in both security and account management.

select
id,
app_display_name,
account_enabled
from
azuread_service_principal
where
service_principal_type = 'Application'
and tenant_id = app_owner_organization_id;
select
id,
app_display_name,
account_enabled
from
azuread_service_principal
where
service_principal_type = 'Application'
and tenant_id = app_owner_organization_id;

Schema for azuread_service_principal

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_enabledboolean!=, =true if the service principal account is enabled; otherwise, false.
add_insjsonbDefines custom behavior that a consuming service can use to call an app in specific contexts.
alternative_namesjsonbUsed to retrieve service principals by subscription, identify resource group and full resource ids for managed identities.
app_descriptiontextThe description exposed by the associated application.
app_display_nametextThe display name exposed by the associated application.
app_idtextThe unique identifier for the associated application (its appId property).
app_owner_organization_idtextContains the tenant id where the application is registered. This is applicable only to service principals backed by applications.
app_role_assignment_requiredbooleanSpecifies whether users or other service principals need to be granted an app role assignment for this service principal before users can sign in or apps can get tokens. The default value is false.
app_rolesjsonbThe roles exposed by the application which this service principal represents.
descriptiontextFree text field to provide an internal end-user facing description of the service principal.
display_nametext=The display name for the service principal.
idtext=The unique identifier for the service principal.
infojsonbBasic profile information of the acquired application such as app's marketing, support, terms of service and privacy statement URLs.
key_credentialsjsonbThe collection of key credentials associated with the service principal.
login_urltextSpecifies the URL where the service provider redirects the user to Azure AD to authenticate. Azure AD uses the URL to launch the application from Microsoft 365 or the Azure AD My Apps. When blank, Azure AD performs IdP-initiated sign-on for applications configured with SAML-based single sign-on.
logout_urltextSpecifies the URL that will be used by Microsoft's authorization service to logout an user using OpenId Connect front-channel, back-channel or SAML logout protocols.
notification_email_addressesjsonbSpecifies the list of email addresses where Azure AD sends a notification when the active certificate is near the expiration date. This is only for the certificates used to sign the SAML token issued for Azure AD Gallery applications.
oauth2_permission_scopesjsonbThe published permission scopes.
owner_idsjsonbId of the owners of the application. The owners are a set of non-admin users who are allowed to modify this object.
password_credentialsjsonbRepresents a password credential associated with a service principal.
reply_urlsjsonbThe URLs that user tokens are sent to for sign in with the associated application, or the redirect URIs that OAuth 2.0 authorization codes and access tokens are sent to for the associated application.
service_principal_namesjsonbContains the list of identifiersUris, copied over from the associated application. Additional values can be added to hybrid applications. These values can be used to identify the permissions exposed by this app within Azure AD.
service_principal_typetext=Identifies whether the service principal represents an application, a managed identity, or a legacy application. This is set by Azure AD internally.
sign_in_audiencetextSpecifies the Microsoft accounts that are supported for the current application. Supported values are: AzureADMyOrg, AzureADMultipleOrgs, AzureADandPersonalMicrosoftAccount, PersonalMicrosoftAccount.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
tags_srcjsonbCustom strings that can be used to categorize and identify the service principal.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.

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_service_principal