Azure Kusto Query output format

Question

I have the follwoing query which runs fine but I'm not happy with the output format in the body of the e-mail for the 'parsedstack' column.

exceptions | project timestamp, type, method, outerMessage, customDimensions.RequestPath, customDimensions.user, customDimensions.aisAuditId, details[0].parsedStack

Running this within Azure it all looks fine as per the below screenshot

However, when the alert triggers and sends an e-mail it looks like the below which is a bit messy ...

Does anyone have any ideas on how I'd add formatting to the kusto query, or another way of improving the presentation in the alert e-mail (if it's possible)?

Thanks!

Share a sample output you'd like to reduce it to. What columns would you want to include/exclude from the result? — Bhargavi Annadevara
Sorry I've been away so just seeing these comments. I think the results I'm extracting are fine, and the way the results are presented when I run the query from within Azure is perfectly fine. It's the visual presentation in the e-mail (2nd screenshot) that I'd like to improve. Ideally I'd like it to list each level on it's own line (like it displays in Azure) rather than all jumbled up into one long line. — BobbyS
I also looked at bag_unpack (by adding 'evaluate bag_unpack(details[0].parsedStack)' to my query, and mv-expand (mv-expand details[0].parsedStack) but I just get a 'Ensure that expression: details.[0].parsedStack is indeed a simple name' error message. — BobbyS

Bhargavi Annadevara Bhargavi Annadevara · Accepted Answer · 2021-05-02T20:07:52

There are two super useful operators in the Kusto Query Language that help working with JSON data:

The bag_unpack plugin is used with the evaluate operator, and unpacks a single column of type dynamic by treating each property bag top-level slot as a column.

Example:

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20}),
    dynamic({"Name": "Dave", "Age":40}),
    dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d)

Output:

| Name       | Age            |
|------------|----------------|
| John       | 20             |
| Dave       | 40             |
| Jasmine    | 30             |

mv-expand on the other hand, expands multi-value dynamic arrays or property bags into multiple records.

Example:

datatable (a:int, b:dynamic)[1,dynamic({"prop1":"a", "prop2":"b"})]
| mv-expand b

Output:

| a          | b              |
|------------|----------------|
| 1          | {"prop1":"a"}  |
| 1          | {"prop2":"b"}  |

I've found these two utilities incredibly useful to format query results. Since data like logs, exception stack traces etc. are more likely to contain nested objects and fields, you can use a mix of these two to unpack/extract relevant fields to any level.

Here are some other posts that explain this further:

Azure Kusto Query output format

1 Answers