Edit

Share via

Create a transformation in Azure Monitor

  • Article

Transformations in Azure Monitor allow you to filter or modify incoming data before it's stored in a Log Analytics workspace. They're implemented 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.

Note

If you're not familiar with KQL or creating log queries for Azure Monitor data, start with Overview of Log Analytics in Azure Monitor and Log queries in Azure Monitor.

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:

  • You may omit any columns that shouldn't be populated. The column will be empty for the record in the target table.
  • Make sure to exclude any columns that are not included in the output table. The data will be accepted without error, but you'll be charged for the ingestion of the extra data even though it's not stored.
  • The output of every transformation must contain a valid timestamp in a column called TimeGenerated of type datetime. If your data source doesn't include this property, you can add it in the transformation with extend or project.

Following is an example of a transformation that performs several functions:

  • Filters the incoming data with a where statement.
  • Adds a new column using the extend operator.
  • Formats the output to match the columns of the target table 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)

Note

See Data collection rule (DCR) samples and scenarios in Azure Monitor for various samples for different scenarios.

Create the transformation query

Before you create or edit the DCR that will include your transformation, you'll need to create and test the transformation query. You'll typically do this by running test queries against existing data or test data. When you get the results you want, you can replace the table name with source and paste it into your DCR as explained below in Add transformation to DCR.

Important

Transformations do not support all KQL features. See Supported KQL features in Azure Monitor transformations for supported features and limitations.

For example, if you're creating a transformation to filter Syslog events, you might start with the following query which you can run in Log Analytics.

Syslog | where SeverityLevel != 'info'

You can paste this query into your DCR and then change the table name to source.

source | where SeverityLevel != 'info'

Use one of the following strategies for data to use to test your query.

  • If you're already collecting the data that you want to transform, then you can use Log Analytics to write a query that filters or modifies the data as needed. Copy the query text and paste it into your DCR.
  • Use Log Analytics to write your query using the datatable operator to create a sample data set that represents your incoming data. Copy the query text without the datatable operator and paste it into your DCR.
  • Use the process to create a new table in the Azure portal and provide sample data. Use the included interface to create and test your transformation query. Either copy the query text and paste into your DCR, or complete the process and then edit the DCR to copy the transformation query. You can then delete the new table if you don't need it.

Add transformation to DCR

Once you have your transformation query, you can add it to a DCR. Use the guidance in Create and edit data collection rules (DCRs) in Azure Monitor to create or edit the DCR using the information in this section to include the transformation query in the DCR definition.

Note

Some data sources will provide a method using the Azure portal to add a transformation to a DCR. For example, collecting a text from a virtual machine allows you to specify a transformation query in the Azure portal. Most data collection scenarios though currently require you to work directly with the DCR definition to add a transformation. That's the process described in this section.

The transformation query is specified in the transformKql property in the Data Flows section of the DCR. This is the section that pairs a data source with a destination. The transformation is applied to the incoming stream of the data flow before it's sent to the destination. It will only apply to that data flow even if the same stream or destination is used in other data flows.

If the transformKql property is omitted, or if its value is simply source, then no transformation is applied, and the incoming data is sent to the destination without modification.

Important

The transformation query must be on a single line the DCR. If you're creating the transformation in the Azure portal, you can use multiple lines for readability, and \n will be included in the query for each new line.

In the following example, there is 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 workspace transformation DCR

The workspace transformation data collection rule (DCR) is a special DCR that's applied directly to a Log Analytics workspace. There can be only one workspace transformation DCR for each workspace, 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.

You can create a workspace transformation DCR in the Azure portal by adding a transformation to a supported table.

  1. On the Log Analytics workspaces menu in the Azure portal, select Tables. Click to the right of the table you're interested in and select Create transformation.

    Screenshot that shows the option to create a transformation for a table in the Azure portal.

  2. If the workspace transformation DCR hasn't already been created for this workspace, select the option to create one. If it has already been created, then that DCR will already be selected. Each workspaces can only have one workspace transformation DCR.

    Screenshot that shows creating a new data collection rule.

  3. Select Next to view sample data from the table. Click Transformation editor to define the transformation query.

    Screenshot that shows sample data from the log table.

  4. You can then edit and run the transformation query to see the results against actual data from the table. Keep modifying and testing the query until you get the results you want.

  5. When you're satisfied with the query, click Apply and then Next and Create to save the DCR with your new transformation.

    Screenshot that shows saving the transformation.

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 the data collection pipeline 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.

Important

You may experience data loss if a transformation takes more than 20 seconds.

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.

Guidance

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

Data collection Reference
Logs ingestion API Send data to Azure Monitor Logs by using REST API (Azure portal)
Send data to Azure Monitor Logs by using REST API (Azure Resource Manager templates)
Virtual machine with Azure Monitor agent Add transformation to Azure Monitor Log
Kubernetes cluster with Container insights Data transformations in Container insights
Azure Event Hubs Tutorial: Ingest events from Azure Event Hubs into Azure Monitor Logs (Public Preview)

Next steps