How to parse json string using kql

Ashwin Venkatesha 165 Reputation points
2024-04-24T07:36:45.9766667+00:00

Log line looks something like this

[QueueTrigger] Event Stats {'total_events': 8388, 'file_count': 2, 'aggregated_file_size': 573292}

and kql is

traces
| where  operation_Name has "QueueTriggerFuncApp" and message has 'Event stats'     
| extend json_string = parse_json(extract("\\{[^{}]*\\}", 0, message))
| mv-expand json_string
| project json_string.total_events

This leads to null values.
I don't know why it is not able to extract values for the given keys.

I did check 'json_string', it is

{'total_events': 8388, 'file_count': 2, 'aggregated_file_size': 573292}

Here, is my answer to fix this

                    file_stats = {"Trigger":"Timer", "Type":"FileStats", "file_count": len(file_arr), "azure_queue_size": getStringSize(file_arr), "aggregated_file_size": accumulated_file_size}
                    logging.info(json.dumps(file_stats))

In this way, I can just run a parse_json once I filter for FileStats for a specific trigger type.

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,850 questions
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,353 questions
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
487 questions
{count} votes

Accepted answer
  1. Wilko van de Velde 2,226 Reputation points
    2024-04-25T06:25:29.2033333+00:00

    Hi @Ashwin Venkatesha ,

    When I run your query it indeed returns null. (BTW I replaced the traces table to a datatable so that I could run the test)

    datatable (operation_Name:string, message :string )
    ["QueueTriggerFuncApp","[QueueTrigger] Event Stats {'total_events': 8388, 'file_count': 2, 'aggregated_file_size': 573292}"]
    | where  operation_Name has "QueueTriggerFuncApp" and message has 'Event stats'     
    | extend json_string = parse_json(extract("\\{[^{}]*\\}", 0, message))
    | mv-expand json_string
    | project json_string.total_events
    

    But if I replace the single quote with double quotes, it worked:

    datatable (operation_Name:string, message :string )
    ["QueueTriggerFuncApp","[QueueTrigger] Event Stats {\"total_events\": 8388, \"file_count\": 2, \"aggregated_file_size\": 573292}"]
    | where  operation_Name has "QueueTriggerFuncApp" and message has 'Event stats'     
    | extend json_string = parse_json(extract("\\{[^{}]*\\}", 0, message))
    | mv-expand json_string
    | project json_string.total_events
    

    Result:
    afbeelding

    So like @BhargavaGunnam-MSFT mentioned, the input should be a properly formatted JSON string.

    But I see you have already improved the logging and that's also a solution.

    Kind Regards,

    Wilko


    Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful