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
- Each operation appears several times (
Started, thenSucceededorFailed). Filter on the final status, or group byCorrelationIdwithsummarize arg_max(TimeGenerated, *) by CorrelationId. Calleris sometimes a UPN, sometimes an object id (GUID): a call from the portal or by a managed identity may show a GUID. Keep a GUID-to-name lookup handy, or resolve it in the directory.- Many writes for one action: a deployment produces dozens of
writerows. Search byCorrelationIdto see the whole picture. - Ingestion delay of a few minutes, and retention limited to that of the workspace: archive if you need to go further back.
- Time:
TimeGeneratedis UTC. For local display:datetime_utc_to_local(TimeGenerated, 'Europe/Brussels'). - Only the control plane is covered: what happens inside the database (T-SQL) is not there. SQL auditing covers that.
- KQL is far faster than the activity log REST API, which can take minutes for a few hours of data.
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
- Start with
AzureActivity: it answers "who?", and that is usually enough. - Move to
resourcechangeswhen the question is "from what to what?". - Save these queries as
.kqlfiles: an incident is not the moment to remember the syntax ofin~. - A weekly report of changes on pools, failover groups and firewall rules is an excellent safety net, far more useful than a thousand alerts.