Azure Monitor data collection rules: ingestion-time KQL transformations to filter, mask and split logs

Filtering after ingestion saves nothing: you have already paid to ingest the data. A transformation is a KQL statement inside a data collection rule (DCR) that runs on each record before it lands in the workspace. Done well, it cuts cost and removes data you should not be storing.

#Where transformations run

#The shape of a transformation

The input is always a virtual table called source. You return the rows and columns you want stored.

source
| where SeverityLevel != "Informational"
| project-away RawPayload

Supported KQL is a subset: where, extend, project, project-away, parse, string and datetime functions, iif, case, hashing and similar are fine. Joins, summarize, and cross-table lookups are not. Check the "supported KQL features in transformations" page for your workspace before relying on a function.

#Common patterns

Drop noisy Windows events:

source
| where EventID !in (4624, 4634, 5156)   // confirm with security first

Mask or hash personal data:

source
| extend UserPrincipalName = hash_sha256(UserPrincipalName)
| extend ClientIP = strcat(extract(@"^(\d+\.\d+\.\d+)\.", 1, ClientIP), ".0")

Parse once at ingestion instead of on every query:

source
| parse RawData with * "status=" Status:int " latency_ms=" LatencyMs:long *
| project TimeGenerated, Computer, Status, LatencyMs

Split data between tables (for example verbose rows to a Basic or Auxiliary plan table, alerts-relevant rows to Analytics) by using two data flows in the same DCR, each with its own transformKql and outputStream.

#Edit a DCR safely

The portal only exposes part of the DCR. Export it, edit the JSON, and push it back.

az monitor data-collection rule show -g rg-mon -n dcr-windows-events > dcr.json

The relevant part of dataFlows:

{
  "streams": ["Microsoft-Event"],
  "destinations": ["la-prod"],
  "transformKql": "source | where EventID !in (4624, 4634)",
  "outputStream": "Microsoft-Event"
}

Apply with the REST API (the CLI accepts a rule file on recent versions too):

az rest --method put \
  --url "https://management.azure.com/subscriptions/$SUB/resourceGroups/rg-mon/providers/Microsoft.Insights/dataCollectionRules/dcr-windows-events?api-version=2022-06-01" \
  --body @dcr.json

The workspace transformation DCR is set on the table:

az monitor log-analytics workspace table show -g rg-mon --workspace-name la-prod -n AzureDiagnostics

#Test before you deploy

You cannot run a DCR transformation directly, but you can run the same KQL against data already in the workspace, replacing source with the table:

let source = Event | where TimeGenerated > ago(1d);
source
| where EventID !in (4624, 4634)
| summarize kept = count()

Compare with the unfiltered count to see the reduction, and with your alert rules to make sure nothing they query disappears.

Warning

A transformation that drops rows silently breaks alerts, workbooks and Sentinel analytics that depended on them. List the queries that use the table before filtering it.

#Verify after rollout

Ingestion volume by table and by computer:

Usage
| where TimeGenerated > ago(7d) and IsBillable
| summarize GB = sum(Quantity) / 1024 by DataType, bin(TimeGenerated, 1d)
| order by TimeGenerated asc

Transformation errors appear in DCR metrics and, if you enable DCR error logs, in a workspace table (names vary; check the DCR monitoring docs). A KQL error in the transformation can cause records to be dropped, so watch volume closely for the first day.

#Troubleshooting

#Prevent it next time

• Keep DCRs in source control and deploy them with Bicep or Terraform, with a reviewer from security.
• Record the expected daily GB per table; alert when it moves by more than 30%.
• Prefer transformations over "turn off the diagnostic setting" so you keep the data you need.