How to use s skalar stored in 'let' in a where clause with '!contains' in Kusto Query Language

Question

I have a problem which bothers me even though i think the solution must be super simple. I have to build a query with Kusto Query Language for my Azure Analytics log analyzer metric.

I want to make this script working for the latest app version and for the second latest app version.

This is the query code to get the latest app version as a skalar.

customEvents
| where client_OS contains "Android"
| summarize max(application_Version)

Now my understanding would be, that i could store this in a let and use it later on to get the second latest app version like this:

let latestVersion = customEvents
| where client_OS contains "Android"
| summarize max(application_Version);
customEvents
| where client_OS contains "Android" and application_Version !contains latestVersion
|summarize max(application_Version)

But unfortunately the compiler wont let me use a skalar with !contains. I have to use a string. Is there any way for me to make string out of this, so i can use it? Or do you have any other good way to retrieve the second highest value from application_Version column? I created this according to how i would do it in SQL, but it seems that Kusto is a bit different.

I hope you can help me fixing this and enlighten me and enhance my Kusto skills.

Best regards, Maverick

Answer 1

latestVersion is not a scalar. To make it scalar, you have to surround it with toscalar(...).

In any case, if you want to find the top 2 items, there's a much more efficient way to do it:

customEvents
| where client_OS contains "Android"
| top 2 by application_Version desc