7

How do I perform a custom sort order in Kusto?

Example query:

//==================================================//
// Assign variables
//==================================================//
let varStart = ago(2d);
let varEnd = now();
let varStorageAccount = 'stgacctname';
//==================================================//
// Filter table
//==================================================//
StorageBlobLogs
| where TimeGenerated between (varStart .. varEnd)
  and AccountName == varStorageAccount
| sort by OperationName

Need:

  • I want to put the various OperationNames (GetBlob, AppendFile, etc.) into a custom order.
  • Something like: | sort by OperationName['GetBlob'], OperationName['AppendFile'], OperationName asc
  • Ideally I'd like to specify values to sort by then allow Kusto to order the remaining using asc/desc.

Is this possible?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
ericOnline
  • 1,586
  • 1
  • 19
  • 54
  • 1
    Thanks for your vote in another question. And about this case, kusto doesn't provide such kind of 'sort', so I think you may use union all the subquery result so that they can be custom sorted, I mean that `let a = Your Query|where OperationName='GetBlob' sort by OperationName; let b = Your Query|where OperationName='AppendFile' sort by OperationName; union a,b` – Tiny Wang May 27 '21 at 06:31

1 Answers1

8

Use an aux column, like this:

datatable(OperationName:string, SomethingElse:string)
[
    "AppendFile", "3",
    "GetBlob", "1",
    "AppendFile", "4",
    "GetBlob", "2"
]
| extend OrderPriority =
    case(OperationName == "GetBlob", 1,
         OperationName == "AppendFile", 2,
         3)
| order by OrderPriority asc, SomethingElse asc 
| project-away OrderPriority

Output:

OperationName SomethingElse
GetBlob 1
GetBlob 2
AppendFile 3
AppendFile 4
Slavik N
  • 4,705
  • 17
  • 23
  • 1
    Sounds like this is not possible using raw Kusto. This is a good work around albeit a bit complex. Thank you. – ericOnline May 27 '21 at 06:38