1

I'm trying to generate a list of account names (attempted, failed, and successful) via Kusto/KQL aggregation.

Intended results are simple-- a single column of string values, sorted alphabetically in ascending order.

As it is cutting me off after 10k results, I am now looking at ways to chunk/paginate this result set.

For each page request, I thought I'd grab the last name in the list and append that to the next query (| where AccountName > "bob.saget").

Kusto won't let me do this; it yields a Cannot compare values of types string and string. Try adding explicit casts error.

Ivan
  • 1,427
  • 1
  • 16
  • 26

2 Answers2

5

While the answer to your original question (of how to compare strings lexicographically) is to use the strcmp() function, what you actually want is Pagination, and that's another story :)

The right path to do pagination in Kusto is to use Stored query results:

Retrieve the first page like this:

.set stored_query_result GiveItSomeName with (previewCount = 100) <|
// Your query comes here
DeviceLogonEvents
| where isnotempty(AccountName)
| summarize by AccountName
| order by AccountName asc
// Add a row number
| project RowNum = row_number()

Retrieve the next page like this:

stored_query_result("GiveItSomeName")
| where RowNum between (100 .. 200)

Etc.

Slavik N
  • 4,705
  • 17
  • 23
  • Defender ATP does not appear to support the full set of KQL commands :\ – Ivan May 24 '21 at 21:02
  • Thanks @SlavikN! – Saggie Haim May 25 '21 at 17:01
  • @SlavikN any way to use .set-or-replace instead of set ? Is there any way to skip the creation if it is already exist ? – JeyJ Dec 14 '21 at 16:54
  • Hi @JeyJ, currently there's no .set-or-replace command for Stored Query Result, you'll need to drop the existing one and create a new one. We've added your suggestion to our backlog. – Slavik N Dec 16 '21 at 14:49
  • @SlavikN Thanks for the answer ! Is there any query besides show that can return count(*) of the stored queries ? (Something that can be handled in code without catching exceptions (java sdk). – JeyJ Dec 16 '21 at 15:52
  • Hi @JeyJ, I don't understand the question. In any case, since it's unrelated to the original question, please create a new question (with more details), and we'll gladly help. Thanks. – Slavik N Dec 16 '21 at 19:31
  • 2
    Hi @JeyJ, set-or-replace command is now available for stored query result – Uri Shapira Mar 21 '22 at 14:18
0

Roundabout way of doing things, but strcmp to the rescue.

DeviceLogonEvents
| where isnotempty(AccountName)
| summarize by AccountName
| order by AccountName asc
| where strcmp(AccountName, 'bob.saget') > -1
Ivan
  • 1,427
  • 1
  • 16
  • 26
  • There are a couple of downsides with this approach. First of all, if the data in the DeviceLogonEvents table is going to change while you're retrieving pages, you may get weird results (some of the new records will be reported, while some others won't be). Secondly, running the query again and again will consume resources of the cluster. The right way to go about this is Stored Query Results (described in a separate answer). – Slavik N May 24 '21 at 20:43