How to parse json string using kql

Question

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.

Answer 1

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:

So like @Bhargava-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".