Data collection rule transformations in Azure Monitor (preview)

  • 08/18/2022

Transformations in a data collection rule (DCR) allow you to filter or modify incoming data before it's stored in a Log Analytics workspace. This article describes how to build transformations in a DCR, including details and limitations of the Kusto Query Language (KQL) used for the transform statement.

Basic concepts

Data transformations are defined using a Kusto Query Language (KQL) statement that is applied individually to each entry in the data source. It must understand the format of the incoming data and create output in the structure of the target table.

Transformation structure

The input stream is represented by a virtual table named source with columns matching the input data stream definition. Following is a typical example of a transformation. This example includes the following functionality:

  • 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)

KQL limitations

Since the transformation is applied to each record individually, it can't use any KQL operators that act on multiple records. Only operators that take a single row as input and return no more than one row are supported. For example, summarize isn't supported since it summarizes multiple records. See Supported KQL features for a complete list of supported features.

Inline reference table

The datatable operator isn't supported in the subset of KQL available to use in transformations. This would normally be used in KQL to define an inline query-time table. Use dynamic literals instead to work around this limitation.

For example, the following isn't supported in a transformation:

let galaxy = datatable (country:string,entity:string)['ES','Spain','US','United States'];
source
| join kind=inner (galaxy) on $left.Location == $right.country
| extend Galaxy_CF = ['entity']

You can instead use the following statement which is supported and performs the same functionality:

let galaxyDictionary = parsejson('{"ES": "Spain","US": "United States"}');
source
| extend Galaxy_CF = galaxyDictionary[Location]

has operator

Transformations don't currently support has. Use contains which is supported and performs similar functionality.

Handling dynamic data

Consider the following input with dynamic data:

{
    "TimeGenerated" : "2021-11-07T09:13:06.570354Z",
    "Message": "Houston, we have a problem",
    "AdditionalContext": {
        "Level": 2,
        "DeviceID": "apollo13"
    }
}

In order to access the properties in AdditionalContext, define it as dynamic-typed column in the input stream:

"columns": [
    {
        "name": "TimeGenerated",
        "type": "datetime"
    },
    {
        "name": "Message",
        "type": "string"
    }, 
    {
        "name": "AdditionalContext",
        "type": "dynamic"
    }
]

The content of AdditionalContext column can now be parsed and used in the KQL transformation:

source
| extend parsedAdditionalContext = parse_json(AdditionalContext)
| extend Level = toint (parsedAdditionalContext.Level)
| extend DeviceId = tostring(parsedAdditionalContext.DeviceID)

Dynamic literals

Use the parse_json function to handle dynamic literals.

For example, the following queries provide the same functionality:

print d=dynamic({"a":123, "b":"hello", "c":[1,2,3], "d":{}})

print d=parse_json('{"a":123, "b":"hello", "c":[1,2,3], "d":{}}')

Supported KQL features

Supported statements

let statement

The right-hand side of let can be a scalar expression, a tabular expression or a user-defined function. Only user-defined functions with scalar arguments are supported.

tabular expression statements

The only supported data sources for the KQL statement are as follows:

  • source, which represents the source data. For example:
source
| where ActivityId == "383112e4-a7a8-4b94-a701-4266dfc18e41"
| project PreciseTimeStamp, Message
  • print operator, which always produces a single row. For example:
print x = 2 + 2, y = 5 | extend z = exp2(x) + exp2(y)

Tabular operators

Scalar operators

Numerical operators

All Numerical operators are supported.

Datetime and Timespan arithmetic operators

All Datetime and Timespan arithmetic operators are supported.

String operators

The following String operators are supported.

  • ==
  • !=
  • =~
  • !~
  • contains
  • !contains
  • contains_cs
  • !contains_cs
  • startswith
  • !startswith
  • startswith_cs
  • !startswith_cs
  • endswith
  • !endswith
  • endswith_cs
  • !endswith_cs
  • matches regex
  • in
  • !in

Bitwise operators

The following Bitwise operators are supported.

  • binary_and()
  • binary_or()
  • binary_xor()
  • binary_not()
  • binary_shift_left()
  • binary_shift_right()

Scalar functions

Bitwise functions

Conversion functions

DateTime and TimeSpan functions

Dynamic and array functions

Mathematical functions

Conditional functions

String functions

Type functions

Identifier quoting

Use Identifier quoting as required.

Next steps