Introduction
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 make_set and make_list functions.
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, makeset and 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.
Make_Set Basics
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 ObjectName.
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.
Make_List Basics
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 SQL12 and 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.
Both make_set and make_list accept an optional second parameter to indicate the maximum number of items to return.
In this 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 make_list.
Make_Set_If
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 called 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?
Note that 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)
Make_List_If
There is also a make_list_if function.
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.
See Also
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.
Conclusion
In this post we saw how to use the make_set and make_list functions, along with their corresponding make_set_if and 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_set (or 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.
Arcane Code 