I need help with Kusto pivot table aggregation: I created pivot table overview with count of errors per system in last x days - example output:

|Error|System|Day-1|Day-2|Day-3
|Err1|System1|0|0|1
|Err1|System2|0|1|0
|Err2|System3|1|0|0

I need to aggregate output data by Error data column - that means Systems data should concatenate into single cell per unique error. Previous example would look like this:

|Error|System|Day-1|Day-2|Day-3
|Err1|System1,System2|0|1|1
|Err2|System3|1|0|0

My Kusto code looks like this:

<data processing>
| evaluate pivot(day, count(Error), Error)

I was not able to find a solution. Any suggestions? Thank you.

I need help with Kusto pivot table aggregation: I created pivot table overview with count of errors per system in last x days - example output:

|Error|System|Day-1|Day-2|Day-3
|Err1|System1|0|0|1
|Err1|System2|0|1|0
|Err2|System3|1|0|0

I need to aggregate output data by Error data column - that means Systems data should concatenate into single cell per unique error. Previous example would look like this:

|Error|System|Day-1|Day-2|Day-3
|Err1|System1,System2|0|1|1
|Err2|System3|1|0|0

My Kusto code looks like this:

<data processing>
| evaluate pivot(day, count(Error), Error)

I was not able to find a solution. Any suggestions? Thank you.

• azure
• kql

Share Improve this question Follow edited Jan 30 at 7:50 Jahnavi 8,23111 gold badge66 silver badges1313 bronze badges Recognized by Microsoft Azure Collective asked Jan 29 at 11:23 person.483person.483 2122 bronze badges 1

• Where are you running this query? – RithwikBojja Commented Jan 30 at 3:31

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Need to aggregate output data by Error data column - that means Systems data should concatenate into single cell per unique error:

To aggregate the data by error column and concatenate all the system data into single cell per unique error you can use the below approach as explained.

Here, I have used two Kusto functions to achieve the results which are strcat_array() and make_set().

strcat_array():

Creates a concatenated string of array values using a specified delimiter.

In the below query, it converts the system array into a comma-separated (,) string to show the system names in a single cell.

make_set():

Once the strcat_array applied to the query, now make_set functionality is to get all the unique system names for each error type in an array as shown in the below output.

let datasample = datatable(Error:string, System:string, ["Day-1"]:int, ["Day-2"]:int, ["Day-3"]:int)
[
    "Error1", "System1", 0, 0, 1,
    "Error1", "System2", 0, 1, 0,
    "Error2", "System3", 1, 0, 0
];
datasample
| summarize
    System = strcat_array(make_set(System),","), ["Day-1"] = sum(["Day-1"]), ["Day-2"] = sum(["Day-2"]),["Day-3"] = sum(["Day-3"])
    by Error

Output: