KQL Search

Query Details

HomeAI ToolsDevice Query

Azure Activity Snapshot Of Monitored Azure Resource

Query

let _MonitoredResources =
    _GetWatchlist("ResourceId-AuditAzureResources")
    | where Notes has "[Snapshot]"
    | project ResourceId, SubscriptionId, ResourceGroup, Resource
;
AzureActivity
| where OperationNameValue has_any ("/snapshots/beginGetAccess", "/snapshots/write") // "/snapshots/delete", "/snapshots/endGetAccess"
| where _ResourceId has_any (toscalar(_MonitoredResources | where isempty(Resource) | summarize make_list(ResourceId)))
    or _ResourceId has_any (toscalar(_MonitoredResources | where isnotempty(Resource) | extend ResourceId = replace_regex(ResourceId, @"^(.+?\/)[^\/]+(\/[^\/]+)$", @"\1snapshots\2") | summarize make_list(ResourceId)))
| extend PreferenceInteger = iff(ResourceProviderValue == toupper(ResourceProviderValue), 0, 1)
// Group together Start, Accept, Success... operations
| summarize hint.shufflekey=CorrelationId
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    PropertiesDynamic = make_bag(pack(ActivityStatusValue, iff(PreferenceInteger == 1, todynamic(Properties), Properties_d))),
    EventDataId = array_sort_asc(make_list(EventDataId)),
    arg_max(TimeGenerated, *)
    by CorrelationId, ResourceProviderValue, OperationNameValue, _ResourceId
// Group together two kinds of logs (where ResourceProviderValue is all caps or title - e.g. MICROSOFT.AUTHORIZATION or Microsoft.Authorization)
| summarize hint.shufflekey=CorrelationId
    PropertiesDynamic = make_bag(pack(ResourceProviderValue, PropertiesDynamic)),
    EventDataId = make_bag(pack(ResourceProviderValue, EventDataId)),
    take_any(TenantId, SourceSystem, CategoryValue, SubscriptionId, Type),
    arg_min(PreferenceInteger, CallerIpAddress, Authorization, Authorization_d, Claims_d, Properties_d, EventSubmissionTimestamp, Hierarchy),
    arg_max(PreferenceInteger, Level, OperationNameValue, Caller, HTTPRequest, OperationId, ResourceGroup, ResourceProviderValue, ActivityStatusValue, ActivitySubstatusValue, OperationName, ActivityStatus, ActivitySubstatus, Category, ResourceId, ResourceProvider, Resource)
    by CorrelationId, TimeGenerated, StartTime, EndTime, _ResourceId
| project
    TimeGenerated,
    StartTime,
    EndTime,
    ResourceProvider,
    Category,
    CategoryValue,
    ResourceProviderValue,
    Level,
    CallerIpAddress,
    Caller,
    OperationName,
    OperationNameValue,
    ActivityStatusValue,
    //ActivityStatus,
    ActivitySubstatusValue,
    ActivitySubstatus,
    SubscriptionId,
    ResourceGroup,
    Resource,
    ResourceId,
    _ResourceId,
    Authorization,
    PropertiesDynamic,
    HTTPRequest,
    Authorization_d,
    Properties_d,
    Claims_d,
    Hierarchy,
    OperationId,
    CorrelationId,
    EventSubmissionTimestamp,
    EventDataId,
    Type

Explanation

This KQL (Kusto Query Language) query is designed to analyze Azure activity logs, specifically focusing on operations related to "snapshots" within Azure resources. Here's a simplified breakdown of what the query does:

  1. Define Monitored Resources:

    • It starts by creating a list of monitored resources from a watchlist named "ResourceId-AuditAzureResources".
    • It filters these resources to include only those with notes containing "[Snapshot]".
    • The relevant fields extracted are ResourceId, SubscriptionId, ResourceGroup, and Resource.

  2. Filter Azure Activity Logs:

    • The query then looks at the AzureActivity logs, filtering for operations that involve accessing or writing snapshots (e.g., "/snapshots/beginGetAccess", "/snapshots/write").
    • It checks if the resource ID in the activity logs matches any of the monitored resources. It handles cases where the Resource field is empty or not, adjusting the ResourceId format accordingly.

  3. Process and Group Logs:

    • It extends the logs with a PreferenceInteger to differentiate between logs where the ResourceProviderValue is in uppercase versus title case.
    • The logs are grouped by CorrelationId, and various operations are summarized, such as the start and end times, properties, and event data IDs.

  4. Further Grouping and Aggregation:

    • The query further groups the logs by CorrelationId and other fields, aggregating properties and event data IDs into bags (collections).
    • It uses arg_min and arg_max functions to select specific log entries based on the PreferenceInteger and other criteria.

  5. Project Final Output:

    • Finally, it selects and projects a comprehensive set of fields for the output, including timestamps, resource details, operation names, activity statuses, and various metadata fields.

In summary, this query is designed to monitor and analyze specific snapshot-related operations on Azure resources, providing detailed insights into these activities by filtering, grouping, and summarizing relevant log data.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: April 15, 2025

Tables

AzureActivity

Keywords

MonitoredResourcesAzureActivityOperationNameValueResourceIdSubscriptionIdResourceGroupResourceResourceProviderValueTimeGeneratedPropertiesDynamicEventDataIdTenantIdSourceSystemCategoryValueTypePreferenceIntegerCallerIpAddressAuthorizationAuthorizationDClaimsDPropertiesDEventSubmissionTimestampHierarchyLevelCallerHTTPRequestOperationIdCorrelationId

Operators

lethasprojectwherehas_anytoscalarisemptysummarizemake_listisnotemptyextendreplace_regexifftouppersummarizehint.shufflekeyminmaxmake_bagpackarray_sort_ascarg_maxbytake_anyarg_minarg_maxproject

Actions