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;

Control examples

Schema for azuread_directory_audit_report

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
activity_date_time	timestamp with time zone	>, >=, =, <, <=	Indicates the date and time the activity was performed.
activity_display_name	text	=	Indicates the activity name or the operation name.
additional_details	jsonb		Indicates additional details on the activity.
category	text	=	Indicates which resource category that's targeted by the activity.
correlation_id	text	=	Indicates a unique ID that helps correlate activities that span across various services. Can be used to trace logs across services.
filter	text	=	Odata query to search for directory audit reports.
id	text	=	Indicates the unique ID for the activity.
initiated_by	jsonb		Indicates information about the user or app initiated the activity.
logged_by_service	text		Indicates 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_type	text		Indicates the type of operation that was performed. The possible values include but are not limited to the following: Add, Assign, Update, Unassign, and Delete.
result	text	=	Indicates the result of the activity. Possible values are: success, failure, timeout, unknownFutureValue.
result_reason	text		Indicates the reason for failure if the result is failure or timeout.
target_resources	jsonb		Indicates 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_id	text		The Azure Tenant ID where the resource is located.
title	text		Title 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