In my previous post Fun With KQL – IIF, we saw how to use the Kusto
iif function to check for a condition then perform an action based on the result of a condition.
What if you had multiple conditions you need to check? While you could string multiple
iif functions together there’s better solution: the KQL
Before we take a look at
case, you should know that the samples in this post will be run inside the LogAnalytics demo site found at https://aka.ms/LADemo. This demo site has been provided by Microsoft and can be used to learn the Kusto Query Language at no cost to you.
If you’ve not read my introductory post in this series, I’d advise you to do so now. It describes the user interface in detail. You’ll find it at https://arcanecode.com/2022/04/11/fun-with-kql-the-kusto-query-language/.
Note that my output may not look exactly like yours when you run the sample queries for several reasons. First, Microsoft only keeps a few days of demo data, which are constantly updated, so the dates and sample data won’t match the screen shots.
Second, I’ll be using the column tool (discussed in the introductory post) to limit the output to just the columns needed to demonstrate the query. Finally, Microsoft may make changes to both the user interface and the data structures between the time I write this and when you read it.
case function works by checking a condition, and if true executing the code associted with it. It then checks the next condition, and runs the code associated with it, and so on. When it runs out of conditions, it will execute the final set of code.
This is much easier to understand with a good example, so let’s take a look.
As with so many of the samples in this Fun With KQL series, we start by piping the
Perf table into a
where to limit the dataset to
% Free Space. We then
take 100 rows for a small dataset for this demo.
Now we flow into an
extend, which creates a new column FreeLevel. We use the
case function to get its value.
As the first parameter to
case, we pass in a condition,
CounterValue < 10. If this condition evaluates to true, it will return the value in the second parameter. In this case it is a simple string,
Critical, but we could have done a calculated value.
If the condition is
false, in other words CounterValue is greater than or equal to 10, it moves onto the next parameter.
The third parameter is another condition,
CounterValue < 30. If true, it executes the code in the fourth parameter, returning the text string
Danger. If false, it moves on to the next parameter.
The fifth parameter is
CounterValue < 50. Again, if true it returns
Look at it. If false, it moves on to the next parameter.
The seventh and final parameter is not a condition, but a value. Because of this, the
case treats it as an else. It will execute this final parameter and return its value.
Its important to understand the
case uses what is known as short circuit logic. When it finds the first positive match, it returns the value associated with it, then stops checking.
For example, lets say the CounterValue had been 20. It would have checked the
CounterValue < 10, said "nope no match", then moved on. It would then do the
CounterValue < 30 check, and said "ah, 20 is less than 30, so we have a match!". It would then execute the code associated with it, in this case returning the string
At this point it will stop checking. The
CounterValue < 50 will never be checked, nor the else portion of the
Using Case to Summarize
case function can become useful when combined with the
Here our query is similar to the previous one, except our text is a bit more descriptive.
We then pipe this into a
summarize, where we get a count of computers for each of the FreeLevel items. As you can see in the output, the "You’re OK…" level has 98,816 computers in this category. The "Look at it…" level has 2,834 computers.
Now you have an idea of the state of health of your infrastructure, and can decide if you need to do more examination of these 2,834 computers. For example you could run queries, using techniques from previous posts, to get a list of computers you may want to look at.
Number of Conditions
In the previous examples we used three conditions as well as the else parameter. You can have less conditions, or even more. A common use of
case is to convert a month number into a month name, as you can see in this example.
In the first
extend, we use the
datetime_part function to get the number of the month. We do it here, so we only have to run the function once, and not for every line in the
We run through a series of conditional checks, comparing the new MonthNumber column to a hard coded value. When it finds a match, it returns the three character abbreviation for that month.
At the end of the
case is the else part, where the value
Unknown is returned. In theory, in this situation it should never fail to find a match, however the
case function always expects to have an else parameter.
case, we pipe into the project. In the project we use
strcat to combine the new MonthName with a space. Then,
format_datetime is used to get the day number and the year number for the TimeGenerated column, resulting in dates like
April 23, 2022.
The following operators, functions, and/or plugins were used or mentioned in this article’s demos. You can learn more about them in some of my previous posts, linked below.
Fun With KQL – Format_DateTime
In this post we saw how the
case statement can be used to evaluate multiple conditions and return a value. We also learned how it short circuits during its decision making process.
The demos in this series of blog posts were inspired by my Pluralsight courses Kusto Query Language (KQL) from Scratch and Introduction to the Azure Data Migration Service, two of the many courses I have on Pluralsight. All of my courses are linked on my About Me page.
If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.