How to override column value on log ingestion

Question

Hi,

I'm trying to modify incoming log data before ingestion into the Log Analytics Workspace's AppServiceConsoleLogs table. Logs are coming in JSON format, and I'm trying to re-use existing columns in that table. Here is my KQL modification query:

source

| where ResultDescription startswith "{" and ResultDescription endswith "}"

| project Json = parse_json(ResultDescription)

| extend

    TimeGenerated = todatetime(Json.Timestamp),

    Host = strcat(split(Json.Category, ".")[0], ".", split(Json.Category, ".")[1]),

    Level = tostring(Json.LogLevel),

    Category = tostring(Json.Category),

    ResultDescription = tostring(Json.Message)

| project-away

    Json

Most of the columns are updated with my custom values. However, the Category column is not overriden with my value. It's empty or missing all the time. Any help is welcome!

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

1. Verify the JSON structure

Ensure the Category field exists in the JSON payload (ResultDescription) and is properly formatted. If it’s missing or malformed, the parse_json function will not populate it. For example:

{     "Timestamp": "2025-01-26T12:00:00Z",     "Category": "App.Service",     "LogLevel": "Info",     "Message": "Sample log message" } Use the following to inspect the parsed JSON: source | where ResultDescription startswith "{" and ResultDescription endswith "}" | project Json = parse_json(ResultDescription) | extend CategoryCheck = Json.Category

Confirm that CategoryCheck contains valid values.

2. Check for Data Type Compatibility

Use tostring(Json.Category) in your extend clause. If the field isn't a string or if there's a type mismatch, it will result in an empty value.

Example modification:

extend Category = tostring(Json.Category)

3. Validate Column Overrides

Ensure the project-away clause is not inadvertently removing or hiding the Category column. Modify the query to inspect intermediate steps:

source | where ResultDescription startswith "{" and ResultDescription endswith "}" | project Json = parse_json(ResultDescription) | extend     TimeGenerated = todatetime(Json.Timestamp),     Host = strcat(split(Json.Category, ".")[0], ".", split(Json.Category, ".")[1]),     Level = tostring(Json.LogLevel),     Category = tostring(Json.Category),     ResultDescription = tostring(Json.Message)

 Validate that the Category column is populated before using project-away.

4. Debugging Common Issues

• Empty or Null Values: Add a default value to handle missing categories:

extend Category = iff(isnull(tostring(Json.Category)), "Unknown", tostring(Json.Category))

• Check for Reserved Words: Ensure Category is not treated as a reserved word or system column in the destination table. If it is, rename it during transformation:

extend CustomCategory = tostring(Json.Category)

5. Finalize Query

After verifying all intermediate steps, your query should look like this:

source | where ResultDescription startswith "{" and ResultDescription endswith "}" | project Json = parse_json(ResultDescription) | extend     TimeGenerated = todatetime(Json.Timestamp),     Host = strcat(split(Json.Category, ".")[0], ".", split(Json.Category, ".")[1]),     Level = tostring(Json.LogLevel),     Category = iff(isnull(tostring(Json.Category)), "Unknown", tostring(Json.Category)),     ResultDescription = tostring(Json.Message) | project-away Json

 6. Testing and Validation

• Run the query and confirm that the Category column is correctly populated.
• If the AppServiceConsoleLogs table still does not reflect the value, verify any ingestion or schema enforcement rules applied in the Log Analytics Workspace.

If the problem persists, check for workspace-specific restrictions or configurations affecting the Category column.

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.