CODEWITHSUNDEEP
kqlwindows-defender
Ivan
Ivan

Reputation: 1507

Kusto KQL (Defender ATP) - Any way to compare strings by sort order?

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.

Upvotes: 1

Views: 1950

Answers (2)

Slavik N
Slavik N

Reputation: 5308

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.

Upvotes: 5

Ivan
Ivan

Reputation: 1507

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

Upvotes: 0

Related Questions