CODEWITHSUNDEEP
azurekql
BobbyS
BobbyS

Reputation: 49

Azure Kusto Query output format

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

enter image description here

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

enter image description here

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!

Upvotes: 0

Views: 2504

Answers (1)

Bhargavi Annadevara
Bhargavi Annadevara

Reputation: 5512

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:

Upvotes: 0

Related Questions