azuread_sign_in_report table | Azure Active Directory plugin

v1.0 for Steampipe, Powerpipe, Flowpipe, 116 plugins, and 44 mods →

Hub

Plugins Mods Docs Home

turbot/azuread

steampipe plugin install azureadsteampipe plugin install azuread

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. Azure AD Sign-In Reports provide details about the usage of managed applications and user sign-in activities.

Table Usage Guide

The azuread_sign_in_report table provides insights into sign-in activities within Microsoft's Azure Active Directory. As a security analyst, explore sign-in specific details through this table, including the location, device, and application used for sign-in. Utilize it to uncover information about sign-in activities, such as failed sign-ins, sign-ins from risky locations or devices, and the verification of user identities.

Examples

Basic info

Discover the segments that highlight user sign-in activities in AzureAD by analyzing the date, user details, and location. This practical application can be used to monitor user activities and track sign-in locations for security purposes.

select
  id,
  created_date_time,
  user_display_name,
  user_principal_name,
  ip_address,
  location ->> 'city' as city
from
  azuread_sign_in_report;

select
  id,
  created_date_time,
  user_display_name,
  user_principal_name,
  ip_address,
  json_extract(location, '$.city') as city
from
  azuread_sign_in_report;

Explore which applications a specific user has accessed within your Azure Active Directory. This can help you monitor user activity, ensuring they're only accessing appropriate resources.

select
  user_display_name,
  id,
  app_display_name,
  user_principal_name
from
  azuread_sign_in_report
where
  user_principal_name = 'abc@myacc.onmicrosoft.com';

select
  user_display_name,
  id,
  app_display_name,
  user_principal_name
from
  azuread_sign_in_report
where
  user_principal_name = 'abc@myacc.onmicrosoft.com';

Query examples

activedirectory_user_sign_in_report

Control examples

Schema for azuread_sign_in_report

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
app_display_name	text		App name displayed in the Azure Portal.
app_id	text		Unique GUID representing the app ID in the Azure Active Directory.
applied_conditional_access_policies	jsonb		Provides a list of conditional access policies that are triggered by the corresponding sign-in activity.
client_app_used	text		Identifies the legacy client used for sign-in activity.
conditional_access_status	text		Reports status of an activated conditional access policy. Possible values are: success, failure, notApplied, and unknownFutureValue.
correlation_id	text		The request ID sent from the client when the sign-in is initiated; used to troubleshoot sign-in activity.
created_date_time	timestamp with time zone		Date and time (UTC) the sign-in was initiated.
device_detail	jsonb		Device information from where the sign-in occurred; includes device ID, operating system, and browser.
id	text	=	Unique ID representing the sign-in activity.
ip_address	text		IP address of the client used to sign in.
is_interactive	boolean		Indicates if a sign-in is interactive or not.
location	jsonb		Provides the city, state, and country code where the sign-in originated.
resource_display_name	text		Name of the resource the user signed into.
resource_id	text		ID of the resource that the user signed into.
risk_detail	text		Provides the 'reason' behind a specific state of a risky user, sign-in or a risk event. The possible values are: none, adminGeneratedTemporaryPassword, userPerformedSecuredPasswordChange, userPerformedSecuredPasswordReset, adminConfirmedSigninSafe, aiConfirmedSigninSafe, userPassedMFADrivenByRiskBasedPolicy, adminDismissedAllRiskForUser, adminConfirmedSigninCompromised, unknownFutureValue.
risk_event_types	jsonb		Risk event types associated with the sign-in. The possible values are: unlikelyTravel, anonymizedIPAddress, maliciousIPAddress, unfamiliarFeatures, malwareInfectedIPAddress, suspiciousIPAddress, leakedCredentials, investigationsThreatIntelligence, generic, and unknownFutureValue.
risk_level_aggregated	text		Aggregated risk level. The possible values are: none, low, medium, high, hidden, and unknownFutureValue.
risk_level_during_sign_in	text		Risk level during sign-in. The possible values are: none, low, medium, high, hidden, and unknownFutureValue.
risk_state	text		Reports status of the risky user, sign-in, or a risk event. The possible values are: none, confirmedSafe, remediated, dismissed, atRisk, confirmedCompromised, unknownFutureValue.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	jsonb		Sign-in status. Includes the error code and description of the error (in case of a sign-in failure).
tenant_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Tenant ID where the resource is located.
title	text		Title of the resource.
user_display_name	text		Display name of the user that initiated the sign-in.
user_id	text		ID of the user that initiated the sign-in.
user_principal_name	text		User principal name of the user that initiated the sign-in.

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_sign_in_report

Table: azuread_sign_in_report - Query Azure AD Sign-In Reports using SQL