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,

Data in "customDimensions" column

{
  "File Name":"Sample File 1",
  "Correlation ID":"e33a8d45-0566-4bf2-94f8-54a6fec29bff",
  "Error List":"[
      {
        "Function Name":"Sample Function 1",
        "Code":"#231256#"
      },
      {
        "Function Name":"Sample-Function-2",
        "Code":"#231258#"
      },
   ]"
}

Expected Output

How can I achieve the above output using KQL?

Thank You.

Update: Adding a sample datatable

datatable(ErrorDetails:dynamic)
[
    dynamic({
        "File Name":"Sample File 1",
        "Correlation ID":"e33a8d45-0566-4bf2-94f8-54a6fec29bff",
        "Error List": [{
                "Function Name":"Sample Function 1",
                "Code":"#231256#"
            },
            {
                "Function Name":"Sample-Function-2",
                "Code":"#231258#"
            }
        ]
    })
]

CodePudding user response：

How can I achieve the above output using KQL?

• Beow is the sample command which can be used to get the correlation id and function names in column.

customEvents
 | extend Org = tostring(customDimensions.correlationID)

• Refer this document which has related discussion.

CodePudding user response：

mv-expand operator

datatable(ErrorDetails:dynamic)
[
    dynamic({
        "File Name":"Sample File 1",
        "Correlation ID":"e33a8d45-0566-4bf2-94f8-54a6fec29bff",
        "Error List": [{
                "Function Name":"Sample Function 1",
                "Code":"#231256#"
            },
            {
                "Function Name":"Sample-Function-2",
                "Code":"#231258#"
            }
        ]
    })
]
| mv-expand EL = ErrorDetails.["Error List"]
| project ["File Name"] = ErrorDetails["File Name"], ["Correlation ID"] = ErrorDetails["Correlation ID"], ["Function Name"] = EL["Function Name"], ["Code"] = EL["Code"]

Fiddle