steampipe plugin install azuread

Table: azuread_directory_audit_report - Query Azure Active Directory Audit Reports using SQL

Azure Active Directory (Azure AD) is Microsoft's multi-tenant, cloud-based directory, and identity management service. It combines core directory services, application access management, and identity protection into a single solution. Azure AD also offers a rich, standards-based platform that enables developers to deliver access control to their applications, based on centralized policy and rules.

Table Usage Guide

The azuread_directory_audit_report table provides insights into the audit reports within Azure Active Directory. As a security analyst, explore audit-specific details through this table, including activity data, changes made, and the entities affected. Utilize it to uncover information about user activities, such as login attempts, password changes, and the creation of new entities, aiding in the detection of unusual or potentially harmful behavior.

Examples

Basic info

Analyze the settings to understand the activities within your Azure Active Directory. This query allows you to identify who initiated specific operations, what those operations were, and when they occurred, helping you maintain security and compliance.

select
activity_display_name,
activity_date_time,
category,
operation_type,
initiated_by -> 'user' ->> 'userPrincipalName' as initiated_user,
result
from
azuread_directory_audit_report;
select
activity_display_name,
activity_date_time,
category,
operation_type,
json_extract(
json_extract(initiated_by, '$.user'),
'$.userPrincipalName'
) as initiated_user,
result
from
azuread_directory_audit_report;

Determine the areas in which policy-related activities have occurred within your Azure Active Directory. This can help you gain insights into the operations and users involved, as well as the results of these activities, enhancing your understanding and management of policy-related actions.

select
activity_display_name,
activity_date_time,
category,
operation_type,
initiated_by -> 'user' ->> 'userPrincipalName' as initiated_user,
result
from
azuread_directory_audit_report
where
category = 'Policy';
select
activity_display_name,
activity_date_time,
category,
operation_type,
json_extract(
json_extract(initiated_by, '$.user'),
'$.userPrincipalName'
) as initiated_user,
result
from
azuread_directory_audit_report
where
category = 'Policy';

List all activities initiated by a specific user

Explore the types of activities initiated by a particular user within an organization. This is useful for auditing purposes, allowing you to monitor user actions and identify any unusual or suspicious activities.

select
activity_display_name,
activity_date_time,
category,
operation_type,
initiated_by -> 'user' ->> 'userPrincipalName' as initiated_user,
result
from
azuread_directory_audit_report
where
filter = 'initiatedBy/user/userPrincipalName eq ''test@org.onmicrosoft.com''';
select
activity_display_name,
activity_date_time,
category,
operation_type,
json_extract(
json_extract(initiated_by, '$.user'),
'$.userPrincipalName'
) as initiated_user,
result
from
azuread_directory_audit_report
where
filter = 'initiatedBy/user/userPrincipalName eq ''test@org.onmicrosoft.com''';

Explore recent user creation activities within the past week. This allows you to identify who initiated the creation and the username of the new user, providing insights into your user management activities.

select
activity_date_time,
category,
operation_type,
initiated_by -> 'user' ->> 'userPrincipalName' as initiated_user,
t ->> 'userPrincipalName' as new_user_name
from
azuread_directory_audit_report,
jsonb_array_elements(target_resources) as t
where
activity_display_name = 'Add user'
and activity_date_time >= (current_date - interval '7 days')
order by
activity_date_time;
select
activity_date_time,
category,
operation_type,
json_extract(initiated_by, '$.user.userPrincipalName') as initiated_user,
json_extract(t.value, '$.userPrincipalName') as new_user_name
from
azuread_directory_audit_report,
json_each(target_resources) as t
where
activity_display_name = 'Add user'
and activity_date_time >= date('now', '-7 days')
order by
activity_date_time;

List users who have reset their passwords in last 7 days

This query lets you track recent password resets in your organization, helping you monitor account security. It's useful for identifying any unusual activity, such as an unexpected surge in password resets, that may indicate a security issue.

select
activity_date_time,
category,
operation_type,
initiated_by -> 'user' ->> 'userPrincipalName' as initiated_user,
t ->> 'userPrincipalName' as target_user
from
azuread_directory_audit_report,
jsonb_array_elements(target_resources) as t
where
t ->> 'displayName' = 'Microsoft password reset service'
and activity_date_time >= (current_date - interval '7 days')
order by
activity_date_time;
select
activity_date_time,
category,
operation_type,
json_extract(initiated_by, '$.user.userPrincipalName') as initiated_user,
json_extract(t.value, '$.userPrincipalName') as target_user
from
azuread_directory_audit_report,
json_each(target_resources) as t
where
json_extract(t.value, '$.displayName') = 'Microsoft password reset service'
and activity_date_time >= date('now', '-7 days')
order by
activity_date_time;

Schema for azuread_directory_audit_report

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
activity_date_timetimestamp with time zone>, >=, =, <, <=Indicates the date and time the activity was performed.
activity_display_nametext=Indicates the activity name or the operation name.
additional_detailsjsonbIndicates additional details on the activity.
categorytext=Indicates which resource category that's targeted by the activity.
correlation_idtext=Indicates a unique ID that helps correlate activities that span across various services. Can be used to trace logs across services.
filtertext=Odata query to search for directory audit reports.
idtext=Indicates the unique ID for the activity.
initiated_byjsonbIndicates information about the user or app initiated the activity.
logged_by_servicetextIndicates information on which service initiated the activity (For example: Self-service Password Management, Core Directory, B2C, Invited Users, Microsoft Identity Manager, Privileged Identity Management.
operation_typetextIndicates the type of operation that was performed. The possible values include but are not limited to the following: Add, Assign, Update, Unassign, and Delete.
resulttext=Indicates the result of the activity. Possible values are: success, failure, timeout, unknownFutureValue.
result_reasontextIndicates the reason for failure if the result is failure or timeout.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
target_resourcesjsonbIndicates information on which resource was changed due to the activity. Target Resource Type can be User, Device, Directory, App, Role, Group, Policy or Other.
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_directory_audit_report