LLI Wiki — My Digital Knowledge Base

Who changed what? The Azure Activity Log in KQL

Find who scaled a pool or changed a failover group or firewall rule with AzureActivity, plus Resource Graph change analysis.

The elastic pool went from 8 to 16 vCores overnight, a firewall rule appeared, a failover group changed configuration. Nobody knows anything. The Azure Activity Log does: it records every control-plane (ARM) operation with the caller, the time and the result. Once sent to Log Analytics, it can be queried in KQL in a few seconds.

The activity log in Log Analytics

You need a subscription diagnostic setting that sends the activity log to a Log Analytics workspace. Events land in the AzureActivity table. Columns used here (checked against the Microsoft documentation):

Column Content
TimeGenerated event time (UTC)
OperationNameValue operation identifier, for example Microsoft.Sql/servers/elasticPools/write
Caller identity that made the call
ActivityStatusValue Started, Succeeded, Failed...
CategoryValue Administrative, Policy, Security...
ResourceGroup, ResourceId, SubscriptionId the target
CorrelationId groups the events of one action
CallerIpAddress, Properties_d, Authorization_d details
flowchart LR
    A["Portal, CLI, pipeline, T-SQL"] --> B["Azure Resource Manager"]
    B --> C["Activity log"]
    C --> D["Diagnostic setting"]
    D --> E["Log Analytics: AzureActivity"]
    E --> F["KQL query"]
    B --> G["Resource Graph: resourcechanges"]
    G --> H["Property diff"]

Who changed what: the queries

The three classic questions, with the matching ARM operation names:

AzureActivity
| where TimeGenerated > ago(7d)
| where CategoryValue == "Administrative"
| where ActivityStatusValue == "Succeeded"
| where OperationNameValue in~ (
    "Microsoft.Sql/servers/elasticPools/write",      // pool resize
    "Microsoft.Sql/servers/failoverGroups/write",    // failover group
    "Microsoft.Sql/servers/firewallRules/write",     // SQL firewall rule
    "Microsoft.Sql/servers/firewallRules/delete")
| project TimeGenerated, OperationNameValue, Caller, CallerIpAddress, ResourceGroup, ResourceId
| order by TimeGenerated desc

To retrace the actions of one person or service account:

AzureActivity
| where TimeGenerated > ago(24h)
| where Caller == "admin@contoso.com"
| where ActivityStatusValue != "Started"
| summarize Operations = count() by OperationNameValue, ActivityStatusValue
| order by Operations desc

And for failures (permission denied, quota):

AzureActivity
| where TimeGenerated > ago(7d) and ActivityStatusValue == "Failed"
| where OperationNameValue startswith "Microsoft.Sql/"
| project TimeGenerated, OperationNameValue, Caller, ActivitySubstatusValue, ResourceId

Activity log pitfalls

Option: Resource Graph change analysis

The activity log tells you who called what. To know which property changed from which value to which, Azure Resource Graph exposes the resourcechanges table:

resourcechanges
| extend changeTime = todatetime(properties.changeAttributes.timestamp),
         target     = tostring(properties.targetResourceId),
         changeType = tostring(properties.changeType),
         changedBy  = tostring(properties.changeAttributes.changedBy),
         clientType = tostring(properties.changeAttributes.clientType)
| where changeTime > ago(7d) and target contains "/providers/microsoft.sql/"
| project changeTime, changeType, changedBy, clientType, target, properties.changes
| order by changeTime desc

properties.changes holds, for each property, the previous and the new value. Records are normally available in under five minutes. Limits to know: a Create or Delete record has no property detail, and changedBy can be Unspecified or System. Resource Graph is a good source of truth for state, but it does not index every resource type: check before relying on it.

Lessons learned

Mastodon LLIMastodon SocialFosstodon