merge rows with the same timestamp into new column values

Question

I've got a bunch of log data from a KQL Table that I want to plot. Here's the simplified query:

MyDataTable
| where ['TimeGenerated'] >= ago(30m)
| summarize count=count() by bin(TimeGenerated, 15m), log_level

That gets me a table like this:

"TimeGenerated [UTC]"	"log_level"	count
"10/19/2022, 11:00:00.000 PM"	info	3527
"10/19/2022, 11:00:00.000 PM"	warn	33
"10/19/2022, 11:00:00.000 PM"	error	2
"10/19/2022, 11:15:00.000 PM"	info	5274
"10/19/2022, 11:15:00.000 PM"	warn	42
"10/19/2022, 11:15:00.000 PM"	error	5
"10/19/2022, 11:30:00.000 PM"	info	1553
"10/19/2022, 11:30:00.000 PM"	warn	15
"10/19/2022, 11:30:00.000 PM"	error	1

But I want to combine the entries with the same timestamp and put the count into different columns based on log_level. Essentially, I want to end up with this:

"TimeGenerated [UTC]"	info	warn	error
"10/19/2022, 11:00:00.000 PM"	3527	33	2
"10/19/2022, 11:15:00.000 PM"	5274	42	5
"10/19/2022, 11:30:00.000 PM"	1533	15	1

Any tips on how to do that transformation?

PS: An ideal solution would create new columns dynamically depending on the different values of log_level but if I have to use info/warn/error in the query that's better than the current situation.

Answer 1

Seems like you want a pivot

datatable(TimeGenerated:datetime, LogLevel:string, Count:long)
[
    datetime(10/19/2022 11:00:00.000 PM), "info", 3527,
    datetime(10/19/2022 11:00:00.000 PM), "warn", 33,
    datetime(10/19/2022 11:00:00.000 PM), "error", 2,
    datetime(10/19/2022 11:15:00.000 PM), "info", 5274,
    datetime(10/19/2022 11:15:00.000 PM), "warn", 42,
    datetime(10/19/2022 11:15:00.000 PM), "error", 5,
    datetime(10/19/2022 11:30:00.000 PM), "info", 1553,
    datetime(10/19/2022 11:30:00.000 PM), "warn", 15,
    datetime(10/19/2022 11:30:00.000 PM), "error", 1,
]
| evaluate pivot(LogLevel, sum(Count))