Log Analytics keep order from variable when using in()

Question

I'm kind of new to Log Analytics and I'm trying to get top 10 computers with "% Free Space" orderer by "_Total" and all disks grouped by computer ordered by "_Total" like this:

let top_10_free =
Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space" and InstanceName == "_Total" and TimeGenerated > todatetime("2020-01-01 00:00:00")
| summarize arg_max(TimeGenerated, *) by Computer
| top 10 by CounterValue desc nulls last
| project Computer;
Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space" and Computer in(top_10_free)
| summarize arg_max(TimeGenerated, *) by InstanceName, Computer 
| project-rename Ultimo_Check = TimeGenerated, Instancia = Computer, Particion = InstanceName, Porcentaje_Disponible = CounterValue
| project  Ultimo_Check, Instancia, Particion, Porcentaje_Disponible

In this query, the computers are ordered in "top_10_free" but not in the final output.

The final output (this is almost what I want, except that computers aren't in the desired order):

To summarize, I want the group of servers of the second img (under the column of Instancia) ordered like the servers in the first img (under the column of Computer).

Expected output:

+---------------+-----------+-----------+-----------------------+
|Ultimo_Check   |Instancia  |Particion  |Porcentaje_Disponible  |
+---------------+-----------+-----------+-----------------------+
|somedate       |server10   |C:         |97,402                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |server10   |D:         |83,363                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |server10   |_Total     |90,383                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |server     |C:         |83,849                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |server     |D:         |91,185                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |server     |_Total     |87,617                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |AK         |C:         |67,599                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |AK         |HarddiskVol|30,461                 |
+---------------+-----------+-----------+-----------------------+
|somedate       |AK         |_Total     |67,735                 |
+---------------+-----------+-----------+-----------------------+
Then AC with _Total = 66,281
Then CU with _Total = 63,249
Then CO with _Total = 37,563
Then GR with _Total = 36,19

Thanks in advance.

Yoni L. Yoni L. · Accepted Answer · 2020-04-27T16:27:48

does adding an order by at the end help? for example:

...
| project  Ultimo_Check, Instancia, Particion, Porcentaje_Disponible
| order by Instancia asc, Porcentaje_Disponible desc

Edited to add the comment that solved the issue

order by orders records according to sort keys. it doesn't order "groups of records", as there's no such notion in the query language, nor in the underlying data layer. you may want to restructure your data (at query time) such that you end up with a record per "group" (e.g. have _Total as one column and the rest of the drives within a property bag like {"C:":1234, "D:":3456}. then, you could order the records (now "groups") by the column _Total

Log Analytics keep order from variable when using in()

1 Answers