6

When using the Monitor, Log feature in Azure Portal you can group rows by dragging and dropping a column header into a certain box, the problem is that this is not saved when pressing the save button.

How do I write KQL that generates the same result? Summarize by Column_Name doesn't yield the same result.

Yoni L.
  • 22,627
  • 2
  • 29
  • 48
DarkWizard96582
  • 63
  • 1
  • 1
  • 5

2 Answers2

3

Save functionality only saves the query editor content and doesn't save anything within the results pane. I believe that using pack and make-set operators can generate a very close output to what you are looking for (if I understood it correctly). See below an example for grouping by client_OS and an internal breakdown by name and count

customEvents
| summarize count() by name, client_OS
| extend p =pack('name', name, 'count', count_) 
| summarize names = make_set(p)  by client_OS
Dan Hadari
  • 244
  • 1
  • 1
  • This kind of works, though the resulting nest is 3-4 layers deep rather than the concise single layer result when using the GUI and `Group columns`. Really wish there was an easy way to display this natively with KQL. – ericOnline May 24 '21 at 22:24
1

I believe that the closest to what you expect can be achieved this way:

exceptions
| summarize entries = make_list(pack_all()) by operation_Name

Scalar function pack_all creates an object from all available columns and aggregation function make_list returns JSON array of all the values provided as its arguments.

Edit: The makelist function is deprecated, use make_list instead.

Tholdrim
  • 31
  • 4
  • This seems to max out at 127 results per `entries` value. – ericOnline May 24 '21 at 22:27
  • 2
    @ericOnline If you use `make_list` instead of the deprecated `makelist` there will be no such a limit. I have edited my answer. You can read more about this in the [official documentation](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/makelist-aggfunction). – Tholdrim May 26 '21 at 07:15