In the previous post, Fun With KQL – Project, we took a dive into the
project operator and the ways it could be used. The
project operator has several variants:
project-reorder. This post will take a quick look at each.
For most of the examples we’ll build on the examples from the Fun With KQL – Project blog article, so if you’ve not read it you should do so now.
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.
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.
There are several variants to the
project operator. The first I wanted to mention is
project-away operator is the reverse of
project. It will return all columns except the ones you indicate. This can be a way of future proofing your queries. With
project, if someone adds a new column to the table it won’t appear in the results. With
project-away, these new columns will now appear in the results.
To be honest, the
project-away operator isn’t something I’ve used a great deal, but wanted to include it for completeness.
Another variant to the
project operator is
project-rename. You can use it to rename a column. As this example shows, that renamed column can also be used in calculations done with
In this example, after performing the
project we use
project-rename to change the name of the CounterName column to FreeMB. Since CounterName is no longer in our dataset, we need to use the new name in the
extend calculations. It also eliminates the need to use
extend to copy the column into the
FreeMB column, making the query more efficient.
project-rename operator, while not something I use daily, is quite useful and one I employ regularly.
The next variant of
project to discuss is
project-keep. At first glance it seems identical to
If you look closely though, you’ll notice a subtle but important difference. In the query we listed the two counter columns together. In the output though, you can see the CounterName column is followed by the InstanceName column, then the CounterValue column appears.
project-keep operator preserves the order of the columns as they appeared in the input dataset, and ignores the order they were listed in your query.
There is another useful feature to
project-keep, it allows you to use wildcards for the column names. Simply enter the first part of a column name, then use an asterisk.
In the query, I specified
Counter* for one of the columns. In the output, Kusto found three matches and included the CounterName, CounterValue, and CounterPath columns in the output.
project-keep can be useful when you are writing quick queries to examine your data. For any query I would be keeping to run in production though, I would always specify the column names. This will improve readability plus make your intent clear to KQL developers who may come behind you.
The final variant we’ll look at is
project-reorder. This operator is a bit counterintuitive, as it doesn’t behave quite like you expect.
project-reorder operator returns all columns in the input dataset. However, it will take the columns you pass in to the operator and list them first, in the order you specify, then the rest of the columns in the order they occur in the incoming dataset.
As you can see in the output, the five columns we listed come first in the output, followed by the remaining columns (you can scroll to the right in the output pane to see the rest). The
project-reorder can be handy when you want to see all of the columns, but you want to see certain columns first in the output.
project-reorder operator can use wildcards.
As you can see, it listed the three columns that began with the word
Counter, in the same order they appeared in the input dataset.
You can also reorder the columns in alphabetical order easily by appending
asc after the wildcard.
Now the three columns with the word
Counter appear in ascending alphabetical order. As you might guess, there is also a
desc that will list the columns in descending order alphabetically.
This leads to a neat trick you can do, if you want to list all the columns in alphabetical order.
project-reorder operator, simply use an asterisk followed by the
asc keyword. This will list all of the columns in the incoming dataset in alphabetical order, which can be quite useful especially in a dataset with many columns. The exception is that pesky TimeGenerated column, which Kusto will always list first in the output.
On occasion you may encounter a table that has columns with numbers embedded in the names. For example, you may have col100, col20. When sorted in ascending order alphabetically, col20 will appear before col100.
To fix this, you can use
granny-desc which will take into account these odd column names and force a100 to appear prior to (when used with
granny-asc) to a20.
This post covered the variants to the
project operator. While not used frequently, they can be useful when developing your Kusto queries.
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.