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

Mika Pitkänen 40 Reputation points
2024-10-18T10:50:14.6766667+00:00

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.

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
3,317 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Vinodh247 23,111 Reputation points MVP
    2024-10-19T11:55:16.6666667+00:00

    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.

    1. 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.

    1. 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.

    1. 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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.