steampipe plugin install azuread

Table: azuread_sign_in_report - Query Azure AD Sign-In Reports 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. 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;

List an user sign in details

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';

Schema for azuread_sign_in_report

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
app_display_nametextApp name displayed in the Azure Portal.
app_idtextUnique GUID representing the app ID in the Azure Active Directory.
applied_conditional_access_policiesjsonbProvides a list of conditional access policies that are triggered by the corresponding sign-in activity.
client_app_usedtextIdentifies the legacy client used for sign-in activity.
conditional_access_statustextReports status of an activated conditional access policy. Possible values are: success, failure, notApplied, and unknownFutureValue.
correlation_idtextThe request ID sent from the client when the sign-in is initiated; used to troubleshoot sign-in activity.
created_date_timetimestamp with time zoneDate and time (UTC) the sign-in was initiated.
device_detailjsonbDevice information from where the sign-in occurred; includes device ID, operating system, and browser.
idtext=Unique ID representing the sign-in activity.
ip_addresstextIP address of the client used to sign in.
is_interactivebooleanIndicates if a sign-in is interactive or not.
locationjsonbProvides the city, state, and country code where the sign-in originated.
resource_display_nametextName of the resource the user signed into.
resource_idtextID of the resource that the user signed into.
risk_detailtextProvides 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_typesjsonbRisk event types associated with the sign-in. The possible values are: unlikelyTravel, anonymizedIPAddress, maliciousIPAddress, unfamiliarFeatures, malwareInfectedIPAddress, suspiciousIPAddress, leakedCredentials, investigationsThreatIntelligence, generic, and unknownFutureValue.
risk_level_aggregatedtextAggregated risk level. The possible values are: none, low, medium, high, hidden, and unknownFutureValue.
risk_level_during_sign_intextRisk level during sign-in. The possible values are: none, low, medium, high, hidden, and unknownFutureValue.
risk_statetextReports status of the risky user, sign-in, or a risk event. The possible values are: none, confirmedSafe, remediated, dismissed, atRisk, confirmedCompromised, unknownFutureValue.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statusjsonbSign-in status. Includes the error code and description of the error (in case of a sign-in failure).
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.
user_display_nametextDisplay name of the user that initiated the sign-in.
user_idtextID of the user that initiated the sign-in.
user_principal_nametextUser 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