Create a transformation in Azure Monitor

Transformations in Azure Monitor filter or modify incoming data before storing it in a Log Analytics workspace. Implement transformations as a Kusto Query Language (KQL) statement in a data collection rule (DCR). This article provides guidance on creating and testing a transformation query and adding it to a DCR.

Basic query structure

All transformation queries start with source, which is a virtual table that represents the input stream. You can then use any supported KQL operators to filter, modify, or add columns to the data as you would with any other table. The query is applied individually to each entry sent by the data source.

The output of the query must match the schema of the target table with the following considerations:

• Omit columns that you don't want to save costs. When you omit a column, that column is empty for each record in the target table.
• Exclude any columns that aren't in the output table. Extra columns are accepted without error, but you pay for ingesting data that isn't stored.
• Include a valid timestamp in a column called TimeGenerated of type datetime. If your data source doesn't include this property, add it by using extend or project.

The following transformation is an example that performs three functions:

• Filters the incoming data by using a where statement.
• Adds a new column Properties by using the extend operator with the parse_json function to parse JSON values from the incoming properties column.
• Formats the transformation output to exactly match the columns of the target table by using the project operator.

source
| where severity == "Critical" 
| extend Properties = parse_json(properties)
| project
    TimeGenerated = todatetime(["time"]),
    Category = category,
    StatusDescription = StatusDescription,
    EventName = name,
    EventId = tostring(Properties.EventId)

For various samples of different scenarios, see Data collection rule (DCR) samples and scenarios in Azure Monitor.

Create the transformation query

Before you add a transformation to a DCR, create and test the query in Log Analytics. When your query returns the expected results, replace the table name with source and add it to your DCR as described in Add transformation to DCR.

For example, to filter Syslog events, start with this query in Log Analytics:

Syslog | where SeverityLevel != 'info'

Then replace the table name with source in your DCR:

source | where SeverityLevel != 'info'

Add transformation to DCR

After you create your transformation query, add it to a DCR by following these steps:

1. Get the current DCR definition. Either open your DCR definition in the UI and select the JSON view, or export the JSON by using the Azure CLI:

   az monitor data-collection rule show --name {dcrName} --resource-group {resourceGroupName} > dcr.json
   

   For more information, see Create and edit data collection rules (DCRs) in Azure Monitor.

2. Locate the dataFlows section of the DCR. This section pairs a data source with a destination.

3. Add the transformKql JSON property to the data flow you want to transform. Set its value to your transformation query on a single line. The transformation is applied to the incoming stream before it's sent to the destination. It only applies to that data flow, even if the same stream or destination is used in other data flows.

4. Save and deploy the updated DCR:

   az monitor data-collection rule update --name {dcrName} --resource-group {resourceGroupName} --body @dcr.json
   

   For other methods, see Create and edit data collection rules (DCRs) in Azure Monitor.

If you omit the transformKql property, or if you set its value to source, no transformation is applied. The incoming data is sent to the destination without modification.

In the following example, there's no transformKql property, so the incoming data is sent to the destination without modification.

"dataFlows": [ 
    { 
        "streams": [ 
        "Microsoft-Syslog" 
        ], 
        "destinations": [ 
        "centralWorkspace" 
        ] 
    } 
] 

In the following example, transformKql has a simple query of source, so the incoming data is sent to the destination without modification. Its functionality is identical to the previous example.

"dataFlows": [ 
    { 
        "streams": [ 
        "Microsoft-Syslog" 
        ], 
        "transformKql": "source", 
        "destinations": [ 
        "centralWorkspace" 
        ] 
    } 
] 

In the following example, transformKql has a query that filters data, so only error messages are sent to the destination.

"dataFlows": [ 
    { 
        "streams": [ 
        "Microsoft-Syslog" 
        ], 
        "transformKql": "source | where message has 'error'", 
        "destinations": [ 
        "centralWorkspace" 
        ] 
    } 
] 

Create a multi-stage transformation (preview)

Multi-stage transformations use the transformations section in a DCR to define processor-based pipelines that data sources and data flows reference by using the transform property. Unlike single-stage transformations that use transformKql, multi-stage transformations run on the client side (before data leaves the agent) or the ingestion side (before data reaches the workspace), or both.

• Client-side transformations - run on the agent before data is sent. These transformations reduce data volume at the source and can filter, parse, or enrich data before transmission.
• Ingestion-side transformations - run on the service after data arrives but before storage. These transformations can apply KQL expressions and further enrich data.

In a multi-stage pipeline, a processor is a named processing step that runs in sequence, such as filtering, parsing, enriching, or applying KQL. The header processor declares the input format for the pipeline (for example, header.Syslog for syslog data).

For conceptual details, see Multi-stage transformations. For the complete schema reference, see DCR structure - Transformations.

Create a multi-stage transformation

Create a multi-stage transformation by using the Azure portal or programmatically.

The following example creates a DCR with a client-side transformation that filters syslog data to keep only auth events, and an ingestion-side transformation that applies KQL to the stream before it's stored.

The DCR definition includes these key sections:

• streamDeclarations - defines the schema for custom streams used between processing stages
• dataSources - configures the data source with a transform property referencing the client-side transformation by name
• destinations - where data is sent
• dataFlows - maps streams to destinations with an optional transform property for ingestion-side processing
• transformations - defines the named processor pipelines referenced by data sources and data flows

Verify DCRs by checking them in the Azure portal under Monitor > Data Collection Rules.

{
    "___location": "eastus",
    "properties": {
        "streamDeclarations": {
            "Custom-FilteredSyslog": {
                "columns": [
                    { "name": "TimeGenerated", "type": "datetime" },
                    { "name": "Facility", "type": "string" },
                    { "name": "Message", "type": "string" },
                    { "name": "Host", "type": "string" },
                    { "name": "SeverityNumber", "type": "int" }
                ]
            }
        },
        "dataSources": {
            "syslog": [
                {
                    "name": "syslogAuth",
                    "facilityNames": ["auth", "authpriv"],
                    "logLevels": ["*"],
                    "transform": "client_filter_auth",
                    "streams": ["Custom-FilteredSyslog"]
                }
            ]
        },
        "destinations": {
            "logAnalytics": [
                {
                    "name": "myWorkspace",
                    "workspaceResourceId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.OperationalInsights/workspaces/{workspaceName}",
                    "workspaceId": "{workspaceId}"
                }
            ]
        },
        "dataFlows": [
            {
                "streams": ["Custom-FilteredSyslog"],
                "transform": "ingestion_enrich_syslog",
                "destinations": ["myWorkspace"],
                "outputStream": "Microsoft-Syslog"
            }
        ],
        "transformations": [
            {
                "name": "client_filter_auth",
                "headerProcessor": {
                    "processor": "header.Syslog",
                    "configuration": {}
                },
                "processors": [
                    {
                        "processor": "filter.Basic",
                        "configuration": {
                            "any": [
                                {
                                    "all": [
                                        {
                                            "columnName": "Facility",
                                            "operator": "==",
                                            "value": "auth"
                                        }
                                    ]
                                }
                            ]
                        }
                    }
                ]
            },
            {
                "name": "ingestion_enrich_syslog",
                "headerProcessor": {
                    "processor": "header.CustomStream",
                    "configuration": {
                        "streamId": "Custom-FilteredSyslog"
                    }
                },
                "processors": [
                    {
                        "processor": "transform.KQL",
                        "configuration": {
                            "expression": "source | extend EnrichedMsg = strcat(Host, ': ', Message)"
                        }
                    }
                ]
            }
        ]
    }
}

$dcr = Get-Content -Path "dcr.json" -Raw
Invoke-AzRestMethod `
    -Path "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Insights/dataCollectionRules/{dcrName}?api-version=2025-05-11" `
    -Method PUT `
    -Payload $dcr

Create workspace transformation DCR

The workspace transformation data collection rule (DCR) is a special DCR that you apply directly to a Log Analytics workspace. Each workspace can have only one workspace transformation DCR, but it can include transformations for any number of tables.

Use one of the following methods to create a workspace transformation DCR for your workspace and add one or more transformations to it.

{
    "kind": "WorkspaceTransforms",
    "___location": "eastus",
    "properties": {
        "dataSources": {},
        "destinations": {
            "logAnalytics": [
                {
                    "workspaceResourceId": "/subscriptions/71b36fb6-4fe4-4664-9a7b-245dc62f2930/resourcegroups/my-resource-group/providers/microsoft.operationalinsights/workspaces/my-workspace",
                    "name": "MyWorkspace"
                }
            ]
        },
        "dataFlows": [
            {
                "streams": [
                    "Microsoft-Table-LAQueryLogs"
                ],
                "destinations": [
                    "MyWorkspace"
                ],
                "transformKql": "source | where QueryText !contains 'LAQueryLogs'"
            },
            {
                "streams": [
                    "Microsoft-Table-Event"
                ],
                "destinations": [
                    "MyWorkspace"
                ],
                "transformKql": "source | project-away ParameterXml"
            }
        ]
    }
}

az monitor data-collection rule create \
    --name {dcrName} \
    --resource-group {resourceGroupName} \
    --body @dcr.json

Optimize and monitor transformations

Transformations run a KQL query against every record collected with the DCR, so it's important that they run efficiently. Transformation execution time contributes to overall data ingestion latency, and transformations that take excessive time to run can impact the performance of data collection and result in data loss. Optimal transformations should take no more than 1 second to run. See Optimize log queries in Azure Monitor for guidance on testing your query before you implement it as a transformation and for recommendations on optimizing queries that don't run efficiently.

Because transformations don't run interactively, it's important to continuously monitor them to ensure that they're running properly and not taking excessive time to process data. See Monitor and troubleshoot DCR data collection in Azure Monitor for details on logs and metrics that monitor the health and performance of transformations. This includes identifying any errors that occur in the KQL and metrics to track their running duration.

The following metrics are automatically collected for transformations and should be reviewed regularly to verify that your transformations are still running as expected. Create metric alert rules to be automatically notified when one of these metrics exceeds a threshold.

• Logs Transformation Duration per Min
• Logs Transformation Errors per Min

Enable DCR error logs to track any errors that occur in your transformations or other queries. Create a log alert rule to be automatically notified when an entry is written to this table.

Troubleshoot transformations

The following tables list common transformation errors and their resolutions.

Transformation guides by data source

There are multiple methods to create transformations depending on the data collection method. The following table lists guidance for different methods for creating transformations.

Limitations and considerations

• Transformations run at ingestion time and contribute to data processing costs. However, filtering data by using transformations can reduce ingestion volume and storage costs. For more information, see Cost optimization and Azure Monitor.
• Not all tables in a Log Analytics workspace support transformations. For a list of supported tables, see Tables that support transformations in Azure Monitor Logs.
• Not all KQL operators are supported in transformation queries. For more information, see Supported KQL features in Azure Monitor transformations.
• While a transformation can send a single data source to multiple tables, it can't send data to multiple workspaces. To send data from a single data source to multiple workspaces, create multiple DCRs.
• The workspace transformation DCR can't send a single data source to multiple tables since the transformation is applied to the table itself.
• Transformations in the workspace transformation DCR are applied to all data sent to the table, regardless of the data source. If you need to apply different transformations to different data sources, use a where statement in the transformation query to apply different logic to data from different sources.
• For multi-stage transformations, transform and transformKql are mutually exclusive per data flow. A DCR can mix old-style (transformKql) and new-style (transform) data flows across different streams.
• Multi-stage transformations require API version 2025-05-11 or later.
• Performance counter data requires separate DCRs for Windows and Linux machines when using multi-stage transformations. A DCR of kind All can't use both header.WindowsPerformanceCounters and header.LinuxPerformanceCounters in a single client-side transformation.

Related content

• Monitor and troubleshoot DCR data collection in Azure Monitor - Track transformation performance and diagnose errors.
• Supported KQL features in Azure Monitor transformations - Verify which KQL operators work in transformations.
• Structure of a data collection rule (DCR) in Azure Monitor - Full JSON schema reference for DCR definitions.
• Transformation samples for Azure Monitor - Example transformation queries for common scenarios.
• Create a data collection rule and an association to it from a virtual machine by using the Azure Monitor agent.
• Cost optimization and Azure Monitor - Understand how transformations affect ingestion costs.