steampipe plugin install azure

Table: azure_monitor_activity_log_event - Query Azure Monitor Activity Log Events using SQL

Azure Monitor Activity Log Events is a feature within Microsoft Azure that provides insights into the operational activities within your Azure resources. It enables you to categorize and analyze data about the status, event severity, and operations of your Azure resources. Azure Monitor Activity Log Events helps you stay informed about the activities and operations happening in your Azure environment.

Table Usage Guide

The azure_monitor_activity_log_event table provides insights into the operational activities within Azure Monitor. As a system administrator or a DevOps engineer, explore event-specific details through this table, including event category, event initiation, and associated metadata. Utilize it to uncover information about events, such as those related to service health, resource health, and administrative operations.

Important notes:

  • This table can provide event details for the previous 90 days.
  • For improved performance, it is advised that you use the optional qual event_timestamp to limit the result set to a specific time period.
  • This table supports optional quals. Queries with optional quals are optimized to use Monitor Activity Log filters. Optional quals are supported for the following columns:
    • event_timestamp
    • resource_group
    • correlation_id
    • resource_id
    • resource_provider_name

Examples

Basic info

Explore the sequence and timing of events in your Azure Monitor Activity Log. This query can be used to gain insights into patterns of activity, identify potential issues, and track changes over time.

select
event_name,
event_data_id,
id,
correlation_id,
level,
resource_id,
event_timestamp
from
azure_monitor_activity_log_event;
select
event_name,
event_data_id,
id,
correlation_id,
level,
resource_id,
event_timestamp
from
azure_monitor_activity_log_event;

List events with event-level critical

This example helps identify critical events in your Azure activity log. By doing so, it allows you to promptly respond to potential issues or security threats.

select
event_name,
id,
operation_name,
event_timestamp,
level,
caller
from
azure_monitor_activity_log_event
where
level = 'EventLevelCritical';
select
event_name,
id,
operation_name,
event_timestamp,
level,
caller
from
azure_monitor_activity_log_event
where
level = 'EventLevelCritical';

List events that occurred over the last five minutes

Track recent activities in your Azure environment by identifying events that have taken place within the last five minutes. This is useful for real-time monitoring and immediate response to changes or incidents.

select
event_name,
event_timestamp,
operation_name,
resource_id,
resource_type,
status
from
azure_monitor_activity_log_event
where
event_timestamp >= now() - interval '5 minutes';
select
event_name,
event_timestamp,
operation_name,
resource_id,
resource_type,
status
from
azure_monitor_activity_log_event
where
event_timestamp >= datetime('now', '-5 minutes');

List ordered events that occurred in the past five to ten minutes

Determine the sequence of events that transpired in the recent past. This can be useful to track and analyze real-time activities, helping to identify patterns or anomalies for prompt action.

select
event_name,
id,
submission_timestamp,
event_timestamp,
category,
sub_status
from
azure_monitor_activity_log_event
where
event_timestamp between (now() - interval '10 minutes')
and (now() - interval '5 minutes')
order by
event_timestamp asc;
select
event_name,
id,
submission_timestamp,
event_timestamp,
category,
sub_status
from
azure_monitor_activity_log_event
where
event_timestamp between (datetime('now', '-10 minutes'))
and (datetime('now', '-5 minutes'))
order by
event_timestamp asc;

Get authorization details for events

Determine the authorization details associated with various events to help manage permissions and access control in your Azure environment. This can help in identifying any unauthorized activities or potential security risks.

select
event_name,
authorization_info ->> 'Action' as authorization_action,
authorization_info ->> 'Role' as authorization_role,
authorization_info ->> 'Scope' as authorization_scope
from
azure_monitor_activity_log_event;
select
event_name,
json_extract(authorization_info, '$.Action') as authorization_action,
json_extract(authorization_info, '$.Role') as authorization_role,
json_extract(authorization_info, '$.Scope') as authorization_scope
from
azure_monitor_activity_log_event;

Get HTTP request details of events

Explore the specifics of HTTP requests in event logs to identify potential security threats or unusual activity. This could be useful in troubleshooting, security audits, or monitoring network traffic.

select
event_name,
operation_name,
event_timestamp,
http_request ->> 'ClientRequestID' as client_request_id,
http_request ->> 'ClientIPAddress' as ClientIPAddress,
http_request ->> 'Method' as method,
http_request ->> 'URI' as uri
from
azure_monitor_activity_log_event;
select
event_name,
operation_name,
event_timestamp,
json_extract(http_request, '$.ClientRequestID') as client_request_id,
json_extract(http_request, '$.ClientIPAddress') as ClientIPAddress,
json_extract(http_request, '$.Method') as method,
json_extract(http_request, '$.URI') as uri
from
azure_monitor_activity_log_event;

Filter examples

List evens by resource group

Discover the segments that are active within a specific resource group in Azure Monitor's activity log. This can be particularly useful for tracking and managing operations, resources, and statuses associated with specific events.

select
event_name,
id,
resource_id,
operation_name,
resource_type,
status
from
azure_monitor_activity_log_event
where
resource_group = 'my_rg';
select
event_name,
id,
resource_id,
operation_name,
resource_type,
status
from
azure_monitor_activity_log_event
where
resource_group = 'my_rg';

List events for a resource provider

Explore the activities associated with a specific resource provider on Azure. This query is useful for tracking operations, event names, and statuses related to a particular network resource provider, helping you understand its activity and performance.

select
event_name,
id,
resource_id,
operation_name,
resource_provider_name,
resource_type,
status
from
azure_monitor_activity_log_event
where
resource_provider_name = 'Microsoft.Network';
select
event_name,
id,
resource_id,
operation_name,
resource_provider_name,
resource_type,
status
from
azure_monitor_activity_log_event
where
resource_provider_name = 'Microsoft.Network';

List events for a particular resource

Discover the segments that have undergone recent changes in a specific resource within your Azure environment. This is particularly useful for tracking changes and maintaining security compliance.

select
event_name,
id,
resource_id,
event_timestamp,
correlation_id,
resource_provider_name
from
azure_monitor_activity_log_event
where
resource_id = '/subscriptions/hsjekr16-f95f-4771-bbb5-8237jsa349sl/resourceGroups/my_rg/providers/Microsoft.Network/publicIPAddresses/test-backup-ip';
select
event_name,
id,
resource_id,
event_timestamp,
correlation_id,
resource_provider_name
from
azure_monitor_activity_log_event
where
resource_id = '/subscriptions/hsjekr16-f95f-4771-bbb5-8237jsa349sl/resourceGroups/my_rg/providers/Microsoft.Network/publicIPAddresses/test-backup-ip';

Schema for azure_monitor_activity_log_event

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
authorization_infojsonbThe sender authorization information.
callertextThe email address of the user who has performed the operation, the UPN claim or SPN claim based on availability.
categorytextThe event category.
claimsjsonbKey value pairs to identify ARM permissions.
cloud_environmenttextThe Azure Cloud Environment.
correlation_idtext=The correlation ID, usually a GUID in the string format. The correlation ID is shared among the events that belong to the same Uber operation.
descriptiontextThe description of the event.
event_data_idtextThe event data ID. This is a unique identifier for an event.
event_nametextThe event name. This value should not be confused with OperationName. For practical purposes, OperationName might be more appealing to end users.
event_timestamptimestamp with time zone>, <, >=, <=The timestamp of when the event was generated by the Azure service processing the request corresponding the event.
http_requestjsonbThe HTTP request info. Usually includes the 'clientRequestId', 'clientIpAddress' (IP address of the user who initiated the event) and 'method' (HTTP method e.g. PUT).
idtextThe ID of this event as required by ARM for RBAC. It contains the EventDataID and a timestamp information.
leveltextThe event level. Possible values include: 'EventLevelCritical', 'EventLevelError', 'EventLevelWarning', 'EventLevelInformational', 'EventLevelVerbose'.
operation_idtextIt is usually a GUID shared among the events corresponding to single operation. This value should not be confused with EventName.
operation_nametextThe operation name.
propertiesjsonbThe set of <Key, Value> pairs (usually a Dictionary<String, String>) that includes details about the event.
resource_grouptext=The resource group which holds this resource.
resource_idtext=The resource URI that uniquely identifies the resource that caused this event.
resource_provider_nametext=The resource provider name of the impacted resource.
resource_typetextThe resource type.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustextA string describing the status of the operation. Some typical values are: Started, In progress, Succeeded, Failed, Resolved.
sub_statustextThe event sub status. Most of the time, when included, this captures the HTTP status code of the REST call. Common values are: OK (HTTP Status Code: 200), Created (HTTP Status Code: 201), Accepted (HTTP Status Code: 202), No Content (HTTP Status Code: 204), Bad Request(HTTP Status Code: 400), Not Found (HTTP Status Code: 404), Conflict (HTTP Status Code: 409), Internal Server Error (HTTP Status Code: 500), Service Unavailable (HTTP Status Code:503), Gateway Timeout (HTTP Status Code: 504).
submission_timestamptimestamp with time zoneThe timestamp of when the event became available for querying via this API.
subscription_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Subscription ID in which 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)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_monitor_activity_log_event