Remap column names in Data Collection Rules (DCR)

Table of Contents

Auxliliary Logs - This article is part of a series.

Part 1: Auxiliary Logs in Azure Log Analytics

Part 2: This Article

Part 3: Split log streams into Analytics and Auxiliary Table

In real world scenarios you have a data source with field names which are not identical to those in your table. Beside of renaming the fields in your agent which sending logs also Data Collection Rules provide the ability to map fields with transformkql.

Introduction

In the previous article of this series we saw how to create a table and ingest data to it. There we defined a custom table TestDataAuxiliary_CL with following fields:

TimeGenerated, type datetime
Message, type string
Info, type string

Table field definition does not reflect best practise nameing convention.

In the last article we already learned how the Data Collection Rule will deal with the data which are sent to it. Here the important points:

If field TimeGenerated is missing the whole entry get ignored
Additional fields do not harm (are ignored)
Missing fields do not harm (corresponding table fields are set to null; can be queried by isnull())
Fields are case sensitive; if they do not match (eg. TiMeGeNeRatEd vs. TimeGenerated) row will be ignored

Now this article will focus on transforming fields; eg. you have often the type of the field attached to the field name: for field Message and type string it will be Message_s.

Transforming field names

A Data Collection Rule (DCR) has the ability to apply transformation with KQL. Unfortunatly in this context only limited KQL commands are supported. A detailed list can be found here: https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/data-collection-transformations-structure#supported-kql-features

So our goal is clear

graph LR; A[Log Source]-->B[Data Collection Rule] B-->C[TimeGenerated]; B-->D[Message]; B-->E[Info]; subgraph transformKQL C-->F[TimeGenerated]; D-->G[Message_s]; E-->H[Info_s]; end F-->I[Log Analytics Table]; G-->I H-->I

This is super simple to achieve with a simple KQL statement. You can specify in the Data Collection Rule (DCR) a transformKql key with a corresponding KQL query.

source | extend Message_s=Message, Info_s=Info | project-away Message, Info

source is the special keyword to suck the data from the input pipeline. With the extend statement we add the new fields with the postfix _s and then we remove the not needed fields with project-away. Easy, right?

The final definition looks like:

$dcrParams = @'
{
  "location": "switzerlandnorth",
  "kind": "Direct",
  "properties": {
    "description": "A direct ingestion rule for TestData logs",
    "streamDeclarations": {
      "Custom-TestDataAuxiliary": {
        "columns": [
          { "name": "TimeGenerated", "type": "datetime" },
          { "name": "Message", "type": "string" },
          { "name": "Info", "type": "string" }
        ]
      }
    },
    "destinations": {
      "logAnalytics": [
        {
          "workspaceResourceId": "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.OperationalInsights/workspaces/log-security-prod-001",
          "name": "log-security-prod-001"
        }
      ]
    },
    "dataFlows": [
      {
        "streams": [
          "Custom-TestDataAuxiliary"
        ],
        "destinations": [
          "log-security-prod-001"
        ],
        "outputStream": "Custom-TestDataAuxiliary_CL",
        "transformKql": "source | extend Message_s=Message, Info_s=Info | project-away Message, Info"
      }
    ]
  }
}
'@

Then sending the definition to Azure with following command:

Invoke-AzRestMethod -Path "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.Insights/dataCollectionRules/DCR-Generic-CollectionRule?api-version=2023-03-11" -Method PUT -payload $dcrParams

Unfortunatly we get following message back from the API:

StatusCode : 400
Content    : {
               "error": {
                 "code": "InvalidPayload",
                 "message": "Data collection rule is invalid",
                 "details": [
                   {
                     "code": "InvalidDataFlow",
                     "message": "Stream (Custom-TestDataAuxiliary) cannot be transformed.Transformation not supported
                                 for Log analytics Auxiliary table destination log-security-prod-001",
                     "target": "properties.dataFlows[0]"
                   }
                 ]
               }
             }

Long story short: As it looks like KQL transformation is not supported by Auxiliary tables.

Verification with Analytics table

To verify that DCR is correct we do the check with a corresponding Analytics table. So first we create the analytics table and change our DCR so it sends data to the new defined table.

$analyticTableParams = @'
{
  "properties": {
    "totalRetentionInDays": 90,
    "plan": "Analytics",
    "schema": {
      "name": "TestDataAnalytics_CL",
      "columns": [
          { "name": "TimeGenerated", "type": "datetime" },
          { "name": "Message_s", "type": "string" },
          { "name": "Info_s", "type": "string" }
      ]
    },
  },
}
'@

Invoke-AzRestMethod -Path "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.OperationalInsights/workspaces/log-security-prod-001/tables/TestDataAnalytics_CL?api-version=2023-01-01-preview" -Method PUT -payload $analyticTableParams

Now we update our DCR to reflect the new table:

$dcrParams = @'
{
  "location": "switzerlandnorth",
  "kind": "Direct",
  "properties": {
    "description": "A direct ingestion rule for TestData logs",
    "streamDeclarations": {
      "Custom-TestDataAnalytics": {
        "columns": [
          { "name": "TimeGenerated", "type": "datetime" },
          { "name": "Message", "type": "string" },
          { "name": "Info", "type": "string" }
        ]
      }
    },
    "destinations": {
      "logAnalytics": [
        {
          "workspaceResourceId": "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.OperationalInsights/workspaces/log-security-prod-001",
          "name": "log-security-prod-001"
        }
      ]
    },
    "dataFlows": [
      {
        "streams": [
          "Custom-TestDataAnalytics"
        ],
        "destinations": [
          "log-security-prod-001"
        ],
        "outputStream": "Custom-TestDataAnalytics_CL",
        "transformKql": "source | extend Message_s=Message, Info_s=Info | project-away Message, Info"
      }
    ]
  }
}
'@

Invoke-AzRestMethod -Path "/subscriptions/a2aeb284-51bd-4807-adbe-94095a10b175/resourceGroups/rg-security-logs-prod-001/providers/Microsoft.Insights/dataCollectionRules/DCR-Generic-CollectionRule?api-version=2023-03-11" -Method PUT -payload $dcrParams

This time we get StatusCode : 200 - the API accepted our DCR. After sending test data (documented here ) we can query our Analytics table:

Our Analytics table with the ingested data.

FAQ

What happens if you overwrite an existing DCR?

The interesting part from operational point of view is to know if endpoint URL changes. The short answer is NO; it does not change (but it takes some minutes until the new DCR is active).

Summary

Data Collection Rules (DCR) provide the possibility to transform incoming data before they get send to the Log Analytics table. But here is the catch: This works only for Analytics and Basic Logs. Auxiliary Logs do not support transformation.

Introduction

Transforming field names

Verification with Analytics table

FAQ

What happens if you overwrite an existing DCR?

Summary

Further Reading