Problem with DRC transformation KQL when column name starts with special character

Question

I'm trying to send custom JSON logs from VM to Log Analytics workspace using Azure Monitor Agent.

Custom JSON log has couple of fields with name starting with @ character (@t, @mt). Those fields are generated by Serilog library the application is using for logging.

I'm trying to create new custom Log Analytics table in portal using example JSON log file. In portal's transformation editor I'm able to create a KQL query which seems to work.

source

| extend TimeGenerated = now()

| extend TimeLogged = ['@t']

| project-away ['@t']

| project-away ['@mt']

But when I'm trying to Create custom log, I get an error message:

Error: Data collection rule is invalid '@t' is not a valid column name. It can start with a letter or one of @, #, $, %, &, _ and then any combination of alphanumeric characters and '.', '-', '_'. It can have also have one space in between.

Transformation editor inserts the column name in the format ['@t'] when building the query. Finally I should be able to insert the KQL into transformKql field in Bicep code when defining Data Collection Rule.

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

In Azure Monitor, creating custom tables with columns that start with special characters like @ can lead to issues when defining Data Collection Rules (DCRs). While the transformation editor allows you to query these fields using the format ['@t'], it doesn't directly support column names starting with special characters during the DCR creation process.

Here's how you can approach :

1. Modify Field Names in Serilog (Preferred Option):

If possible, configure Serilog to generate fields without special characters at the beginning. This will make it easier to work with Azure Monitor and Log Analytics without hitting such restrictions. In Serilog, you can customize the output template or enrich the log data to remove or modify special characters.

2. Transform the Field Names in KQL:

If modifying Serilog is not feasible, you can rename the fields in the transformation before creating the custom table. Use the following query structure to create a new column with a valid name and remove the original column with the special character:

##KQL 

source
| extend TimeGenerated = now()
| extend TimeLogged = ['@t'] // Access the original field with special character
| extend TimeLogged_Valid = TimeLogged // Create a new column with a valid name
| project-away ['@t'], ['@mt'] // Remove the original columns

This ensures that the custom table uses valid column names, allowing the data collection rule to be created without errors.

3. Inserting KQL into transformKql Field in Bicep:

In your Bicep template for the Data Collection Rule, use the valid KQL transformation. After transforming the field names, you can set the transformKql field like this:

##bicep code

resource dcr 'Microsoft.Insights/dataCollectionRules@2021-04-01' = {
  name: 'myDCR'
  location: 'West Europe'
  properties: {
    dataSources: [
      {
        type: 'CustomLogs'
        format: 'json'
        transformKql: '''
          source
          | extend TimeLogged_Valid = ['@t']
          | project-away ['@t'], ['@mt']
        '''
      }
    ]
  }
}

The key is to use valid column names in the final transformation to comply with the Azure Monitor schema.

4. Validate DCR Creation:

Ensure that the Data Collection Rule is valid by running a test to check if the JSON logs are correctly ingested and transformed in Log Analytics with the new field names.

By transforming the fields into valid names within the KQL query, you can bypass the restriction imposed by Azure Monitor on special characters in column names, allowing the DCR creation to succeed.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.