Azure Application Insights | KQL | customDimensions column containing array of objects

user_5276949 0
Viewed 106

We are using Azure application Insights for error logging. I am new to KQL and trying to fetch custom properties from inbuilt "customDimensions" column in the following format,

Value as is from "customDimensions" column

exceptions
| project customDimensions


{
"File Name":"Sample File 1",
"Correlation ID":"e33a8d45-1234-1234-1223-54a6fec30356",
"Error List":"[
{\"Function Name\":\"Sample Function 1\",\"Code\":\"12345\"},
{\"Function Name\":\"Sample-Function-2\",\"Code\":\"12343\"}]"
}

Expected Output

File Name Correlation ID Function Name Code
Sample File 1 e33a8d45-1234-1234-1223-54a6fec30356 Sample Function 1 12345
Sample File 1 e33a8d45-1234-1234-1223-54a6fec30356 Sample-Function-2 12343

How can I achieve the above output using KQL?

Thank You.

kusto-explorer azure-monitoring kql azure-application-insights azure
1 Answers

This might seem a little bit tricky, but bear with me :-)

  • Every sub-element extracted from a dynamic element, is dynamic.
  • parse_json() / todynamic() when given a dynamic argument, returns it, As Is.
    So first, we use tostring() and only then we use todynamic() so the string would be parsed as json, to dynamic type.
datatable(ErrorDetails:dynamic)
[
    dynamic({
        "File Name":"Sample File 1",
        "Correlation ID":"e33a8d45-0566-4bf2-94f8-54a6fec29bff",
        "Error List":"[{\"Function Name\":\"Sample Function 1\",\"Code\":\"12345\"},{\"Function Name\":\"Sample-Function-2\",\"Code\":\"12343\"}]"
    })
]
| mv-expand EL = todynamic(tostring(ErrorDetails["Error List"]))
| project ["File Name"] = ErrorDetails["File Name"], ["Correlation ID"] = ErrorDetails["Correlation ID"], ["Function Name"] = EL["Function Name"], ["Code"] = EL["Code"]
File Name Correlation ID Function Name Code
Sample File 1 e33a8d45-0566-4bf2-94f8-54a6fec29bff Sample Function 1 12345
Sample File 1 e33a8d45-0566-4bf2-94f8-54a6fec29bff Sample-Function-2 12343

Fiddle

user_63364790
Related