The take_any function is a random row generator. Based on the parameters passed it, it will select a random row from the dataset being piped into it. It also has a variant, take_anyif, we’ll see both in this post.
Note that take_any was originally called any and was renamed. While any still works, it has been deprecated and you should now use take_any.
Any and all of 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.
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.
Take_Any Basics
Like other functions we’ve covered so far, we will need to use summarize in order to execute take_any. In this example, we will pass an * (asterisk) into the take_any parameter. This will cause all columns for a random row to be returned.
Each time you execute this you should get a different row back from the piped in dataset. Note there are more columns that appear off screen to the right.
Take_Any For A Column
With take_any, you can also pass in a specific column name instead of using the *.
As you can see, it returns a random value from the column passed as the parameter.
Take_Any With Multiple Columns
take_any can also work with multiple columns. Just pass each column you want as a parameter, and it will return the values from a random row that contains the columns you requested.
Here, we passed three columns from the Perf table into take_any. It returned a random row with the three columns. We could have used more columns or less, according to our needs.
Returning Random Multiple Rows Based On A Column with Take_Any
You can return multiple rows from take_any. To do so, you can add by then the column name after the take_any as you can see in this example.
Here we passed an * to get all columns, then we follow with by CounterName. KQL will get a list of unique CounterNames then return a random row for each one.
Take_AnyIf
The take_anyif variant of take_any operates like other if variants we covered recently, maxif, minif, and sumif.
We pass in the name of a column in the first parameter, then the second parameter is a condition. In this case, the row that will be picked randomly must have a CounterName of % Free Space.
In the results, you can see it grabbed a random computer name from the Computer column where the CounterName for that row had a value of % Free Space.
take_anyif does have a few limitations compared to take_any. First, you cannot pass in an * and get all columns. Second, you can only enter a single column. It does not support passing in multiple columns.
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.
In this post we learned how to use take_any to grab a random row. We saw how to return all columns, a single column, or multiple columns. In addition we saw how to use take_anyif to grab a random value conditionally.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
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.
In my previous post, Fun With KQL – Max, MaxIf, Min and MinIf, we looked at the aggregation functions max and min. In this post we’ll talk about another aggregation function, sum. We’ll also look at a variant of it, sumif.
I should mention 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.
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.
Sum Basics
The sum function is straightforward. It creates a total for the column passed into it.
In this demo we’ll take the Perf table, and filter it with a where operator to only include rows where the CounterName is Free Megabytes.
We then call summarize, which is needed if you want to use an aggregation function on its own. Finally we use sum and pass in the column we want to total, CounterValue.
In this example, we result in a total of 6,398,287,032.
Including the Sum As A Column
We can include the sum function as a column in the output. To do so we need to include a by as part of the summarize so the data will be grouped correctly, in this case by the CounterName.
In the output you can see each CounterName on a row, along with the grand total for its CounterValue.
SumIf
The sumif uses two parameters. The first is the column to aggregate. The second is the condition which, if true, causes a value to be included in the summation process.
In the example below, the CounterValue will only be included if the CounterName equals Free Megabytes.
Other Uses for SumIf
We can use sumif just as we did maxif and minif. In fact, lets extend an example from the previous blog post on Max, MaxIf, Min and MinIf and add sumif to our output.
Here we added two new columns with our summed values.
You can use sum and sumif like you do max, min, maxif, and minif. Refer back to the Fun With KQL – Max and Min for other examples.
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.
We learned about sum and sumif in this post, seeing how they can be used. They can be used to return a single grand total, as well as be included as a column in the output of queries.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
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.
The max and min aggregation functions are common to almost every language, and the Kusto Query Language is no exception. As you would think, when you pipe in a dataset max returns the maximum value for the column name you pass in. Likewise min returns the lowest value.
In addition, there are variants for each, maxif and minif. We’ll see examples for all of these in this post.
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.
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.
Max
The max function is easy to use. In this example we use summarize to call an aggregation function, in this case max.
Here we can see the maximum CounterValue in the Perf table where the CounterName was Free Megabytes was 236,999.
Using Max in Other Places
You can use max in many other places in KQL. As one example, refer back to my previous post Fun With KQL – Top-Nested. Instead of the count or sum aggregations we used in the post, we could have also used max.
In this example, we used the max function to rank our top nested values.
MaxIf
There is a variant to max called maxif. It allows us to include a condition in the second parameter such that in order for the value to be considered for max, the condition must be true.
In this example, we use CounterValue in the first parameter, then we put the condition CounterName == "Free Megabytes" in the second parameter, thus restricting the search for a maximum value to only rows with Free Megabytes in the CounterName.
As of now, you may not see much difference between using the combination of where and max versus the maxif. In a moment we’ll see another way to use maxif, but for now, lets move onto min.
Min
The min function can be used like max, except it returns the lowest value in the column indicated.
Here, we found the minimum CounterValue in the dataset that was passed in was 34.
Again, like max, the min aggregate function can be used in many places in KQL, like the Top-Nested operator.
MinIf
min also as an alternate version, minif. Just like maxif, you pass the column name as the first parameter and the condition in the second parameter.
Since it is so similar to maxif we’ll skip a detailed look at it for now, but we’ll show an example of it momentarily.
Max and Min as an Output Columns
It’s possible to include max and min as output columns in your query. In this example we used summarize to calculate the max and min values, giving them better names.
Using by CounterName will group the summarized values by their CounterName, and include the CounterName column in the output.
I used the in operator to limit to just two CounterNames, but you could include all of them, or your own set.
MaxIf and MinIf as Columns
In the previous example, we saw how to create columns to hold min and max values in the output. It had one drawback though. The values were for one of the two CounterNames we limited the results to. There was no way to distinguish which CounterName these values reflected.
This is where the maxif and minif aggregate functions come into play.
In this example we create four columns using the summarize operator. For each column we use either maxif or minif to create a value for just the CounterName we want.
maxif and minif can be used in other places as well. Think back to the previous example with top-nested. Perhaps we were only interested in a handful of ObjectNames.
The second line of the query could have been written:
| top-nested 3 of ObjectName
by ObjectMax = maxif( CounterValue
, CounterName in ("CounterName1", "CounterName2")
)
This functionality would really let us hone in on just the data we need.
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.
In this post we saw how to use the aggregate functions min and max. First we saw how to use them with summarize to return a specific value, then saw how to use them with part of another query. We also saw their alternates, maxif and minif.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
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.
Back in June of 2022 I covered the top operator in my Fun With KQL – Top post. We showed how to create your own top 10 lists, for example what were the top 5 computers ranked by free disk space.
What if you needed your top results in a nested hierarchy? For example, you wanted to know which three objects in the Perf table had the most entries? But, for each one of those, what were the three counters with the most entires?
That’s where the top-nested operator comes in. It allows you to create top lists in nested, also called hierarchical levels.
Before we begin our discussion on top-nested, you should be aware 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.
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.
Top-Nested Basics
In the example below, we call the Perf table, and pipe it into our first top-nested operator. We then tell it how many items we want, in this case 3. Next, we tell it what we want three of, here we say we want the top 3 from our ObjectName column.
Next, after the by we indicate how we want to determine which ObjectName‘s are the top 3. Here we use the count aggregation, and storing that result in a new ObjectCount column.
OK, we’ve now setup the uppermost level for our top hierarchy. Now we need to tell the query what should be in the nested level. To do that, we use a comma, then a second top-nested operator.
Into the second top-nested we again say we want 3 items, this time from the CounterName column. Again we’ll rank these by the count aggregation, storing the result in the CounterNameCount column.
Finally we sort the results by the counts in descending order.
In the results, the first item was the Process object name, with 2,262,619 entries. It only had one CounterName associated with it, % Processor Time, which is why you only see one row for the Process object.
In second place is the ObjectName of LogicalDisk, with 1,286,540 entries. It had three counters associated with it. Of these, the Disk Read Bytes/sec took top place with 116,968 rows. In close second was Disk Bytes/sec with 116,965 entires in the Perf table. In third place for LogicalDisk was Disk Writes/sec.
The ObjectName that came in third was the K8SContainer, and you can see the three CounterName values associated with it.
Now that you’ve seen it in action, the top-nested operator is pretty simple to use and understand. Just tell it how many items you want, what item you want, and what aggregation you want to use to rank them.
Multiple Levels for Top-Nested
You can have many nested levels, in this next example we’ll use three levels of nesting.
Here we decided to get the top 5 of each level, and we went three levels deep, ObjectName, CounterName, then InstanceName. We could have gone even deeper, we just need additional top-nested operators for each level of our hierarchy.
Also note we decided to sort by the names of our various objects instead of the counter totals. This is a design decision that can be made by the ultimate end user of your query, and will be dependant on their needs.
Additionally, while I used 5 at every level, this isn’t a requirement. I could have used top-nested 3 at the ObjectName level, then top-nested 5 at the CounterName level, and perhaps top-nested 10 at the InstanceName level. Again, this can be determined by the needs of your end user, and Kusto is flexible enough to handle those needs.
Using Other Aggregations
So far we’ve been using the count aggregation for our needs. We can use any aggregation type we need to with top-nested. Take a look at this example.
Here, we used the sum aggregation, summing up our CounterValue column in order to determine the rank within our top-nested hierarchy.
We could have used other aggregations, such as min or max (both of which we’ll see in the next blog post), or any of the many aggregations available in Kusto.
All Others
Sometimes it’s just as important to know what wasn’t included in your top list. The top-nested operator gives us this capability through the with others capability.
In this example, on the very top of the hierarchy you can see we’ve added with others = "All Other Objects" between the column to rank and the by. It is the with others that tells top-nested to aggregate the values not included in the final top list and display those results as well.
In the output, you see a row for "All Other Objects", this is the count for all the objects that were not in the top list.
The text All Other Objects was of my own choosing. I could have used any text here, like Not in the top list, Stuff not on top or Better luck next time.
Note that when determining the value for other it used the same aggregation function as the top-nested. Here we used count, but it could have been sum or whichever aggregation function we used.
Others at All Levels
In the previous example we only included others at the top level. We can use it at all levels if we wish. We’ll harken back to our original example with two top-nested levels, and include a others for each level.
In the second row, you can see K8SContainer, and All Other Counters in a sublevel, followed the the top 3 values for the CounterNames in the K8SContainer.
All Others at Sublevels Only
As you’ve seen many times in this Fun With KQL series, the Kusto Query Language is very flexible. This allows us a way to have others appear only at the sublevels, as you can see in this demonstration.
Here we only included with others at the second level of our nest. Do note this resulted in an extra row where the ObjectName is empty.
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.
Getting a hierarchy of top items is a common business need. As you saw in this post, top-nested allows you to accomplish this easily. It also includes the ability to include the other values not included in the top-nested list.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
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.
Business Analysis is becoming mainstream in today’s corporate world. A big part of that analysis is done with pivot tables. Think of an Excel spreadsheet where data is organized into rows and columns.
The pivot plugin will take one data column from your query, and flip it to become new columns in the output data grid. The other column will become the rows, and an aggregation function will be at the cross section of the rows and columns, supplying the main data. You’ll get a better understanding through the demos in this post.
You may be wondering "plugin? What’s a plugin?"
Microsoft created a set of language extensions called plugins. These are functions which add extra capability to the Kusto Query Language. Plugins are invoked using the evaluate operator. pivot is one of the many plugins, we’ll look at more in upcoming posts.
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.
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.
Countries
Our ultimate endpoint for this demo is to get the count of requests, by the name of the request, for each country. In this demo we’ll be using AppRequests table, so lets begin by getting a list of countries.
For these demos we’ll start by using the AppRequests data, and filtering where the success was false, and the column containing the countries, ClientCountryOrRegion, is not empty.
To get our list of countries we’ll simply use distinct, then sort by the country column.
We’ll be using these countries as the columns in our output data grid.
Counts
Next, lets get the data that will be the basis for our pivot. We’ll project just two columns, Name and ClientCountryOrRegion. The Name column contains the type of request that was made, typically a GET or PUT request.
Now we call the summarize operator to get a count of the unique combination of Name and ClientCountryOrRegion and store it in RequestCount.
This does give us the data we need, the number of requests for each request type (the name) in each country. This data though, isn’t easy to read. For example, it’s difficult to compare the number of requests between countries.
We can solve this problem by using the hero of this blog post, the pivot plugin.
Pivot Basics
In this query, we get our data and project just the two columns we need. We then pipe this into the evaluate operator. As stated earlier, we have to use evaluate in order to call a plugin. Here, we follow the evaluate operator with the name of the plugin to call, in this case pivot.
Into the parameter of pivot we pass in the name of the data column from our query we want to become the column in the data grid. By default, the column not passed into pivot becomes the rows.
At the intersection of rows and columns is the aggregation function used by pivot. While there are several you can use (and we’ll see examples momentarily) by default if you don’t specify one count() is used.
After the call to evaluate pivot... we call sort, so the Name will be listed in ascending alphabetical order.
If we look at the first row, we see that "GET .aws/credentials" had one call, from the country of Russia. Looking further down, we can see "GET Employees/Create" had 1,355 calls, all in the United States.
Pivoting On A Different Column
The AppRequests table has a column ItemCount. What we want to accomplish with this next query is summing up the ItemCount value and have that be at the intersections of our pivoted table.
We need to make two modifications to the previous query. First, we have to add ItemCount to our list of projected values in order to use it in the pivot.
Next, we need to add a second parameter to the pivot plugin. We specify the type of aggregation we want to use, in this example sum. Then into the sum parameter we pass in the value to aggregate on, ItemCount.
In this case, at the intersection of the Name and ClientCountryOrRegion is the summed up ItemCount.
Other Aggregations
The pivot plugin supports many aggregations. The list includes min, max, take_any, sum, dcount, avg, stdev, variance, make_list, make_bag, make_set, and the default of count.
Be aware that these aggregations are used in many places in Kusto beyond just pivot. Over the course of these Fun With KQL blog posts we’ll be devoting posts to many of them.
Additional Columns In The Output
Sometimes you need more than just one column on the rows. The pivot plugin supports that in two ways. First, if you pipe multiple columns into it, all of them except for the pivot column you pass in are returned.
The second way is to pass the column names in as additional parameters, as you’ll see in this example.
Here, for the column to pivot on we once again used ClientCountryOrRegion, and our aggregation is sum of the ItemCount. Then we begin passing in the columns to display.
Be aware you need to pass in all the columns you want, if you omit any they won’t be in the results. In this example, we added Name and AppRoleInstance as the third and forth parameters to the pivot plugin.
I then used project-reorder, mostly for fun, you can read more about it in my Variants of Project blog post. Finally we call sort to sort the output.
While we could omit the columns as parameters and just use the default behavior of displaying everything passed in, explicitly listing the columns in the pivot parameters makes the query self documenting. It’s clear that we wanted these exact columns in the output. For that reason I prefer to list the columns as parameters.
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.
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.
Back on April 25, 2022 I did a blog post on the where operator, Fun With KQL – Where. In that post, I covered several functions that can be used with where to limit the results of a query. This list includes: startswith, endswith, has,hasprefix, hassuffix, and contains.
All of these had one thing in common, they were case insensitive. Kusto ignored the case of the text passed in. If you passed in the text BYTE, for example, Kusto would match on BYTE, Byte, bYtE, byte and other combinations.
There are versions of these which are case sensitive. We’ll see a few here, focusing on the contains keyword. In addition there are not versions, which will also be demonstrated.
There is another operator we’ll discuss here, in. It is a bit of an odd duck, in that it is case sensitive by default. We’ll see it and its variants later in this post.
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.
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.
Preface
For this section of the post I’m going to use contains for the demonstrations. Be aware everything I discuss on contains also applies to the other operators mentioned in the Introduction. Each has the same variants as contains.
Case Insensitive Contains
First, let’s take a look at the normal version of contains.
Briefly, we get the Perf table and grab three columns, TimeGenerated, CounterName, and CounterValue. This is then piped into a where, in which we use contains to look for rows in the CounterName column with the text BYTES.
In the results, you can see names like Available MBytes, Free Megabytes, and Bytes Sent/sec, to name a few. In this case the case of the text passed into contains, all uppercase, was irrelevant to the match.
Making Contains Case Sensitive
There is an alternate version of contains, contains_cs. Let’s rerun the above query, using the _cs version.
This invokes contains_cs, the case sensitive version of contains. It and will look for an exact match of the text that was passed in. In this case it looked in CounterName and found no records with BYTES in all caps, so in the results at the bottom shows "No results found…".
The other keywords I mentioned in the Introduction also have case sensitive versions, just add _cs to the end of their names.
Not Contains
In addition to the case sensitive versions of these commands, there is also a not version, as in "not contains". To invoke the not version of contains or any of the operators place a ! (exclamation mark, also called a "bang") on the front, as in !contains.
In the results you will see all rows as long as the word Bytes is not in the **CounterName column.
Note that the !contains is case insensitive. The casing of the value passed in, in this example Bytes didn’t matter.
Not Contains Case Sensitive
It probably won’t come as a surprise, but there is a case sensitive version of not contains. You simply combine the ! on the front and append the _cs to the end of the command. Here we do it with contains, using the command !contains_cs.
In this example we used !contains_cs to look for rows that did not contain the exact text BYTES in all caps. As you can see in the output, it returned rows including some that had the word Bytes, but it was in mixed case. No rows with upper case BYTES were returned.
In
In the introduction I mentioned there is one keyword that had the rules revered when it comes to case sensitivity, and that is in.
The in looks for values in a list that you pass into the in parameters. Let’s take a look.
We use where on the CounterName, then call in. In the parameters we pass in three values, three strings which we want to find rows with these values. The results show matches on some of the text that was passed in, there were many more rows not show on the screen capture.
One thing to note, by default in is case sensitive, unlike the other commands mentioned in this post. in looked for exact matches, including case, with the values we passed in.
Case Insensitive In
To make in case insensitive, you append a ~ (tilde) after the keyword, in~.
In this example, you can see it found matches for the values passed in, even though the case of the text passed in did not match the case in the results.
Not In
The in has a not version that works like the other operators. Place a ! (exclamation mark / bang) before it.
In this version of the query, !in returned all records except for ones in the list passed into the !in.
Also note we took advantage of the flexibility of the Kusto Query Language formatting and put each parameter on its own line.
Like a regular in, !in is case sensitive by default. There is a version, as you might expect, that can be case insensitive.
Not In Case Insensitive
To call the not and case insensitive version of in, simply combine the ! and ~: !in~.
With this example, it omitted matches of the three values passed in, regardless of the case of the text in the Perf table.
In Parameters
I just wanted to point out that with in there is no limit to the number of parameters. in can handle 1, 2, 3, 4, or more.
In addition, while these examples used strings in and its variants can also work with numeric values, for example where CounterValue !in (0, 42, 33, 73).
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.
In this post, we covered how to make certain operators case sensitive as well as use the not versions of them. While we focused on contains, the same methods also apply to startswith, endswith, has,hasprefix, and hassuffix.
We then looked at the in operator and how it differed from the others when it comes to case sensitivity.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
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.
I’ve just published a new course at Puralsight, Kusto Query Language: Beginning Operators. This course is the second in Pluralsight’s Kusto Query Language Learning Path.
The Kusto Query Language can be used to retrieve and analyze data, not only in your own Azure Data Clusters, but for the health and status of your Azure-hosted infrastructure as well.
In this course, Kusto Query Language: Beginning Operators, you’ll learn the basic set of Kusto operators needed to author effective, useful queries.
First, you’ll learn to search across tables. Next, you’ll discover how to limit the data being output, as well as to limit the columns of data being returned.
Finally, you’ll explore how to add calculated columns to your query results. When you’re finished with this course, you’ll have the skills to write queries to answer a majority of your needs.
If you don’t have a Pluralsight subscription, there is a way to watch for free. 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.
The Kusto Query Language (KQL) is becoming increasingly important in the world of Azure.
In this course, Kusto Query Language: Getting Started, you’ll learn foundational knowledge of the Kusto Query Language.
First, you’ll learn what KQL is and where it is used, exploring the Log Analytics environment. Next, you’ll discover the basic structure, comparing it to other query languages.
Finally, you’ll explore the definitions of terms used with KQL. When you’re finished with this course, you’ll have the necessary skills and knowledge of the Kusto Query Language and the environments it is used in to begin authoring your own Kusto Queries.
You can also see this course for free, if you take advantage of the free trial described in the previous section.
Conclusion
As I write this, I have just completed recording the third course in the series, Kusto Query Language: Scalar Operators. It should be published around the end of January 2023.
I hope you enjoy the courses! And stay tuned to the blog, as I will be continuing my ongoing series Fun With KQL.
In this post we will examine the KQL (Kusto Query Language) search operator. Search allows us to look across all columns in one or more tables for a specific text string.
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.
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.
Comments
Before I talk about search, I just wanted to mention how comments in KQL work. Here you can see my search query, with a comment above it.
Comments in KQL begin with two slashes //. If you want a multi-line comment you’ll have to begin each line with the two slashes.
Basic Search
Let’s take a look at a basic query that uses the search operator.
The query begins with our Perf table. On the next line is a pipe symbol (the vertical bar), following this is the search operator. Then, in quotation marks is the term we are looking for, Memory. So what’s happening here?
First, KQL accesses the entire Perf table. Next, it takes those results, and pipes them into the search operator. The job of search is to look across all columns in the data that was passed in, for a specific string of text. In this case the table Perf was passed into the search operator, but in a later post we’ll see ways to pass other types of data into search.
Once the search operator completes its work, it then pipes the data out to the next step in the query. Since there are no more operators after it, the next step is displaying the results in the pane at the bottom.
KQL knew the query was done when it encountered the blank line at the end. At that point it knows to go ahead and process the query. You can place more queries in the file after, and they won’t execute.
Optionally, you can place a semicolon to end the query. This is only needed though if you want to run multiple queries at the same time.
If you notice, the query is highlighted with a light gray background. This indicates the current query which will be executed when you click Run, or press Shift+Enter on your keyboard. If you need to run multiple queries you can highlight them, then run.
As you can see in the output the ObjectName column contains the word Memory. Likewise, the InstanceName column also as a row with the word Memory. Finally if you look in the CounterPath column the word Memory is embedded in the path.
The search term only has to occur in a single column to be included in the results.
Formatting the Query
In the previous example we used two lines for our query. This is a common practice when authoring Kusto queries, it improves readability and makes it easier to update.
This is not a requirement though, we could put the entire query on a single line, like so:
If you were to use the Format query button in the toolbar above the query pane, it will reformat your query breaking it into multiple lines as shown in the previous example.
Case Sensitivity with Search
By default, the search operator is not case sensitive. The following query will return the same results as the first one.
We can make a search case sensitive by adding the kind argument, indicating the search kind should be case sensitive.
Between the search operator and the term we are looking for, we add kind=case_sensitive. As you can see from the output, looking for memory with a lower case m finds no results.
Searching Everything
It is possible to search all the tables in the database. If you don’t pipe anything into search, it assumes you want to go over the whole database.
Be warned though, this is considered poor practice. It is slow on a large database, will take a long time, and will usually time out unless your database is very small.
I tried running this once on the whole sample Demo database. After half an hour it timed out.
There is a way to search across several tables though if you need to.
Searching Multiple Tables
If you have a handful of tables you want to search, you can append in, followed by the list of tables to search in parenthesis, then the term to search for.
This query searched only the tables Perf, Event, and Alert for the term Memory and displayed the output at the bottom.
Searching A Specific Column
So far we’ve seen search used to go over all columns in a table to look for a string. It is possible to search only a single column.
To search, you use the column name, followed by two equal signs, then the term to look for. This will look for an exact match. It will not return partial matches. In other words the entire contents of that column must match the search phrase exactly.
Searching For Text Anywhere In The Column
It is also possible to search for a phrase anywhere in the column text. You simply replace the double equal sign with a colon.
If you look at the results in the CounterName column, searching for MBytes returned Available MBytes, but it also returned Available MBytes Memory.
Searching Across Columns for Partial Text
In the previous example we saw how to search a single column for a partial match. In other words the text could appear anywhere in that specific column.
It is also possible to search across all the columns for a partial match using wildcards.
Doing so is simple, just place an asterisk * at the beginning and end of the search term. As you can see in the results, searching for *Bytes* found both Disk Read Bytes/sec and Bytes Sent/sec.
I slipped in another example on comments, the line with search demonstrates that a comment can come at the end of a line as well.
It is also possible to search for text which occurs only at the start, or at the end of a column’s text. To do so, you use the startswith or endswith parameters.
Finally, you can search for text with a specific word at the start and at the end, but any letters in between. Again, we use the wildcard asterisk.
Combining Searches Logically
Search will allow you to combine multiple searches in one expression, using the logical operators and and or.
I reused the search from the previous example, then used the and to add another condition. In the second condition I use an or to look for either the text C: or D: as an exact match in any column.
In the output you can see the CounterName column has matches for Free*Bytes. In the InstanceName column it found matches for both C: and D:. It also found matches inside the CounterPath column.
Regular Expressions
Finally, I’m sure fans of regular expressions will be thrilled to find out that the search operator does indeed support regular expressions (often abbreviated RegEx).
Here we used the search operator, followed by the name of the column to search, here InstanceName.
Then, to use a regular expression, add the keywords matches regex to the end, followed by the regular expression in quotes.
In this example the expression [A-Z] means any single character in the range A to Z. The colon is simply static text, it will look for any character from A to Z followed by a colon.
If you examine the InstanceName column you’ll see matches for C: and D:.
Conclusion
In this post we learned about the search operator, and the many ways to use it. In the next post we’ll examine the where operator.
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.
This post begins a new series on KQL – The Kusto Query Language. KQL is used to query large datasets on the Azure platform. In this new series you’ll learn many aspects of the Kusto language. There are a variety of places KQL is used on the Azure platform, including Azure Log Analytics, Azure Application Insights, Windows Defender Advanced Threat Protection, and the Azure Security Center to name a few.
Best of all, you can experiment with KQL without the need for your own dedicated Azure account. Microsoft has provided a user interface and sample dataset at https://aka.ms/LADemo you can use to learn Kusto at no cost to you.
In this introductory post, we’ll explore the user interface and write our first, very simple, KQL query. You might want to bookmark this post so you can easily refer back to it.
The Log Analytics User Interface
As mentioned, there are many places in Azure where you can use the KQL language to perform data analysis. For this series of blog posts, we will use the Azure Log Analytics demo site provided by Microsoft.
Across the top is the standard Azure toolbar. In the upper left is the Home > navigator, and under it is the Logs logo letting you know where you are. As these are standard Azure interface elements we won’t dig deeper into them.
The Query Area
The main portion of the interface is taken up by the query area.
The area in the upper half is where you enter the query you want to run. The lower half is where the results are displayed. We’ll see an example of this in action later in this post.
Just above the query area is a toolbar. The Run button will execute the query you’ve entered. Note too, you can use the keyboard command SHIFT+ENTER to run a query. I’m a keyboard guy, so this is what I use most often to run queries, which you’ll see if you take either of my KQL courses on Pluralsight (I’ve linked to them in the Conclusion of this post).
To the right of the Run button is a Time range. You can use this to limit the time range for your query. We’ll learn more about this in a future post.
The Save button is pretty obvious, it will allow you to save your query. Share will let you share your query, or the results of the query.
New alert rule will let you add alerts for your queries. Export will let you export the results of your query to a CSV file or other formats.
Pin to lets you pin the query to your Azure dashboard, useful for frequently run queries.
The final menu option, Format query, does just that. Reformats you query to make it look nice and more readable.
Be aware, within the demo environment some of these features will be disabled. For example, you cannot save you query to the demo environment. In your own Azure environment however these will work fine.
The Side Bar
Over on the left you’ll see the side bar area. This holds a lot of items, so let’s take a look.
At the very top you see a tab shape holding the text New Query 1. It is possible to create multiple tabs, each with its own query and result pane. You can use the + (Plus button) beside the tab to open a new tab, and the x just within the tab to close it.
Under the tab is the word Demo. This is the name of the dataset you are working with. This would be similar to a database name in SQL Server, although be aware this is most definitely not a relational database.
Next up you see Tables, Queries, and Functions. These control what is displayed in the lower side bar area. Right now it is set to Tables, which we’ll dive into in just a moment.
Below this is a search bar, which you can use to search for a table name, or a query or function when that data is displayed. Under the search bar is a filter button, as well as ways you can group the information. We’ll go over these more in a future post.
Below this is the Favorites. If you have tables that you reference frequently, you can add them to the favorites for quick reference.
The Side Bar – Tables
Under the favorites are your tables, with the tables grouped into solutions. Let’s expand the LogManagement solution by clicking the triangle beside it.
What you can see is a partial list of tables in the Log Management solution, there are too many to fit in a single screen capture. If you scroll down you’ll see there are a lot of tables!
Scroll down to find the Perf table. We will be using this table a lot in this Fun With KQL blog series.
If you hover over the table name (and you don’t have to expand the table for this to work) a popup will appear. It has the name of the table, and a description of the data in it. Notice the star to the right of the table name. If you hover over it, a helpful hint Add to favorites appears. You can just click on the start to add it to your favorites area.
Under the table name you’ll see Use in editor. Clicking on it will insert the table name into your query window. For the Perf table this isn’t a big deal, but there are some long winded table names such as ContainerRegistryRepositoryEvents. Being able to click and insert will make authoring a query go a bit faster as well as reduce issues from typing mistakes.
At the bottom of this informational pop out is a section Useful links. Clicking on the link will open a new page to the Microsoft documentation for this table.
With the table expanded, you can now see all of the columns in this table. To the right of the column name is its data type. These are pretty standard data types, string, int, real (also known as a float, double, etc. in other languages), datetime, and more.
If you double click on the column name, it will insert the name of the column into the query editor.
The Side Bar – Queries
Let’s change the contents of the side bar by clicking on the Queries at the top of the side bar. Here you will see queries which Microsoft has built for you. You can use these as examples for building your own queries.
The queries are grouped into categories, although you can use the Group by to change this to a variety of other groupings.
Go down and expand the Other grouping, then scroll down a bit. Look for the queries that begin with m1_.
These are the queries I used in my Kusto Query Language (KQL) from Scratch course. Microsoft sponsored this course, and wanted to include them on the demo site. The m followed by a number indicates which module in the course the demos are associated with. The majority of the samples I will be using in this Fun With KQL series of blog posts will be derived from the m1-demo-intro and m2-demo-80-percent queries.
If you hover over the query name a pop out appears. It has a brief description, and the ability to either Run the query, or Load to editor. Unless it is a query you authored and run frequently, I suggest always loading it into the editor to review first.
The Side Bar – Functions
Let’s now click on the Functions at the top of the side bar.
This is a list of built in functions you can use in your queries. We won’t be going into functions as part of this Fun With KQL introductory series, but know this is where you can find them.
Additional Tools
As the final step in this introduction, lets look at the small toolbar just above the query editor, to the very right side of the user interface.
The Feedback is pretty obvious. A pop out will appear from the right side where you can file issues, ask for help, give product suggestions, or just tell the team at Microsoft how much you love KQL and the user interface.
The Queries button causes a window to appear, which displayes all of the built in queries in a card format. You can scroll down with all of the queries loaded, or click on one of the categories on the left to narrow the view.
If you hover over one of the query boxes it will provide the option to run the query or load the query into the editor. This is an easy way to browse through all of the queries and get more information about them.
The very right most item in this toolbar is a drop down menu with several options.
At the bottom are links to the Community pages where you can find help at the forums or jump to the GitHub repo for the Azure Monitor Community.
Just above it are four links to Microsoft documentation pages around the user interface as well as the Kusto Query Language.
The very top most item is a link to an Online course you can take. This will take you to Pluralsight and specifically the Kusto Query Language (KQL) from Scratch course.
And look at that, the author is little old me! Yes, this is the course I created for Pluralsight, sponsored by Microsoft. If you have a Pluralsight subscription already you can just sign in and take the course. If not, you can go to this link Kusto Query Language (KQL) from Scratch and use the Try for free link. You’ll get a free 10 day pass to Pluralsight with which you can watch my KQL courses, or any of the courses on Pluralsight including the many courses I have done for Pluralsight over the years.
Your First Query
Whew, that was a lot of info, but now you have a good understanding of the user interface. In this case it is for examining Log Analytics, but the UI is similar for most of the places you can use Kusto with.
It’s time to write our first Kusto query. Remember the Perf table we looked at earlier? Lets write a query to return the contents of the Perf table.
In the query editor, type in Perf. Note that when it comes to table and column names KQL is case senstive. Perf is not the same as perf or PERF. The latter two will cause an error if you attempt to use them.
That’s it, no having to use a Select, no requirement to enter column names, just enter the name of the table, then click the Run button, or use SHIFT+ENTER.
To prevent a run away query, the interface limits the maximum number of rows that are returned, here 30,000. This is shown to you in the blue informational box just above the query resutls. You can dismiss the message by clicking the x toward the right side.
At the bottom it shows the run time for the query, here just a little over three seconds. That’s one of the huge benefits to Kusto, it is very fast!
If you click on any column header it will provide a few options.
The up and down arrows can be used to change the sorting between ascending and descending order. Faintly visible are the three dots which brings up a menu. In this menu you can filter the results.
If you look to the right side you’ll see the word Columns displayed vertically. Clicking it causes the column filter tool to pop out.
You can use this to unselect (or reselect) columns from the output. You can also transform the output to a pivot table. Note the pivot table is a new feature since the KQL from Scratch course was created.
The last thing to note is the Query details link on the very bottom right. This causes a pop out with more information about the execution of the query.
This gives a brief overview that can assist you with query tuning. I won’t take the time to go over each statistic, you can use the little i in a circle button to the right of each one for more details.
A Note on Result Filtering
Before I leave, I just wanted to point out an important aspect of filtering the results. These filters apply after the query has been run. Kusto has already brought back the (in this case) 30,000 rows of data.
There are techniques you can use when writing your queries that will filter down the results before they are returned. This will make the queries run faster, and make it easier to work with the data. Stay tuned, as we’ll be learning many of these techniques in this blog series, Fun With KQL.
Conclusion
Congratulations, and a big thank you for reading this far. It was a long blog post, but there were a lot of aspects of the user interface to cover.
You may want to bookmark this post for future reference. From here on we’ll be focused on the Kusto language itself, and only mention elements of the user interface when needed.
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 