In previous posts, I’ve mentioned using certain functions and operators to investigate conditions in your system. Naturally you’ll need to create lists of those items, based on certain conditions.
For example, you may want to get a list of the counters associated with an object. Or, you may want to get a list of computer where a certain condition is met.
In this article we’ll see how to get those lists using the Kusto
The set of 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.
Older Names – MakeSet and MakeList
Before I start, let me mention there are older versions of these functions,
makelist. They were renamed to fall in line with revised Kusto naming standards, adding the underscore into the name.
While the old versions still work, you should use the newer version of the names in case Microsoft should phase them out in the future.
For our first example, let’s see how to get a set of items, associated with another item. In this query, we’ll get a list of counter names associated with an object name.
We take the
Perf table and pipe in into the
summarize operator. A new column name is declared, Counters. We then use
make_set, passing in the CounterName column. After the
by, we use
This will result in Counters holding a JSON array of CounterNames associated with an ObjectName.
If you look at the output, the second row for the ObjectName of
Memory as been expanded. In the Counters column you see a JSON array holding two values,
Available MBytes and
Available MBytes Memory.
Simply put, the
Memory object has two counter names associated with it,
Available MBytes and
Available MBytes Memory.
Making a Set Based on a Condition
A second, and slightly more useful way to use
make_set is to get a list of items where a condition is true.
In this example we again turn to the
Perf table. We use a
where operator to limit the results to our
% Free Space counters where its value is greater than
95 (i.e. 95%).
As before, we go into a
summarize operator, creating a new column Computers. We call
make_set and pass in the Computer column.
Note that for this query we didn’t use the
by portion. In this case,
make_set takes the data in the Computer column creates a JSON array, as you can see in the output. This gave us a set of three computers who have more than 95% free space.
The second way to create these sets is the
make_list function. It works almost identically to
make_set, with one minor difference. Let’s see the query in action, and that difference will become clear.f
This query is identical to the one for
make_set, except of course for using
make_list. However, look at the results.
You’ll see the first computer,
SQL01.na.contosohotels.com appears twice in the list. Likewise the computers that begin with
SQL00 appear multiple times. And that’s just in little bit that is visible!
Now you can see the big difference,
make_set creates a unique list of items. Each entry will only appear once in the JSON array. The
make_list function performs no such de-duplication. If the item (in this case the computer name) appeared 100 times, it would be in the JSON array 100 times.
Crashing the User Interface
In the previous example, I attempted to click on the arrow beside the Computers in order to expand the list. The user interface came down with a bad case of "fall down go boom". It sat for a while, before just locking up on me.
I finally determined that the JSON array just had too many items to display. Fortunately, there is a way around this.
make_list accept an optional second parameter to indicate the maximum number of items to return.
make_list example, after the Computer column I passed in the value of
64. This will limit the number of items in the JSON array to sixty four items.
I could have used any number, honestly I picked 64 because I happened to glance over at my old Commodore 64 sitting on my desk and decided that would be a good number. Computer history is fun!
Now that I had limited my JSON array, I was able to expand the data in the results grid, and could see the duplicated values. Again, both of these functions support the use of the optional parameter, however you are more likely to need it with
In our first example for
make_set, before calling it we had a
where operator. Part of it limited the results to rows with a counter value greater than 95.
There is an alternative to
make_set_if. With this function we can pass the condition in as a parameter.
Here we still used
where to limit the data to the free space percentage counter. But as a second parameter to
make_set_if, we pass in a condition of
CounterValue >= 95.
We could have included both conditions by surrounding them with parenthesis, such as:
make_set_if(Computer, (CounterName == "% Free Space" and CounterValue >= 95))
However it turned out to be a more efficient to remove the non free space rows first.
And yes, in this version I did use greater than or equal to, instead of just greater than as I did originally, because why not?
make_set_if also supports the parameter to limit the result set size. It becomes the third parameter, as in:
make_set_if(Computer, CounterValue >= 95, 64)
There is also a
It behaves like
make_set_if, except for not removing duplicated values. In this example I added the third parameter to limit the size of the JSON array to 32 items.
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.
In this post we saw how to use the
make_list functions, along with their corresponding
make_list_if functions, to get a list of values in a JSON array. These are useful functions for returning a list of items, such as computers, where some condition is true.
The next article in this series will focus on the
mv-expand function, which can be used to take the JSON array created by
make_list) and convert it into rows.
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.