Must Learn KQL Part 14: The Project Operator

Chapter 14

This post is part of an ongoing series to educate about the simplicity and power of the Kusto Query Language (KQL). If you’d like the 90-second post-commercial recap that seems to be a standard part of every TV show these days…

The full series index (including code and queries) is located here:

https://aka.ms/MustLearnKQL

The book version (pdf) of this series is located here:

https://github.com/rod-trent/MustLearnKQL/tree/main/Book_Version

The book will be updated when each new part in this series is released.

…

As noted in part/chapter 13 of this series, the next few parts/chapters (parts 13-16) will be all about how to manipulate the results of the KQL queries. As shown in part/chapter 13, the Extend operator allows us to create (and even fabricate) special data to show in the results. On its own, that’s hugely valuable. But, also noted throughout this series, the results of the query are the most important part of the process because the types, formats, and ways the data is displayed will allow us to focus on the actual security prospects. And when it comes to identifying threats quickly, efficiency is key.

While part/chapter 13 provided a way to build custom views of the data, that data was still populated among all the rest of the data. Now we get to do something with the data. We get to choose exactly what is displayed to afford our security teams the chance to catch things quickly. We can choose to display our custom data, but then handpick everything else.

This is where the Project operator comes into play. Using the Project operator, I can tell the query engine the exact data columns to show. In this case, by the way, Project is pronounced like as in projector.

The Project operator takes on the following syntax:

Tablename 
| project column1, column2, column3

Let’s take the query we used in part/chapter 13 and add one single line to end. Use the KQL Playground (https://aka.ms/LADemo) for your hands-on experience with the following query.

Perf //table name
| where CounterName == "Free Megabytes" //filtering data by 'free megabytes'
| extend FreeKB = CounterValue * 1024 //calculating free kilobytes
| extend FreeGB = CounterValue / 1024 //calculating free gigabytes
| extend FreeMB = CounterValue //calculating free megabytes
| project Computer, CounterName, FreeGB, FreeMB, FreeKB //only show these columns

In this query example, that one single line addition simply tells the query engine to only display the existing Computer and CounterName columns along with my custom created columns (FreeGB, FreeMB, FreeKB). See that?

Look at the differences in the following comparison images. The top one comes from part/chapter 13 and shows literally every column in the Perf table with my custom created data weaved in. The bottom one is much more concise, precise, and oh so nice.

The Project operator may seem like a simple tool, but its hugely powerful, giving you data choice.

But there’s more to it. As the TV detective, Columbo, used to say: Just one more thing…

The Project operator has more depth than you might realize.

First off, like the Extend operator, you can use Project to create custom columns. For example, the following query eliminates the Extend operator completely and just creates the same custom columns as before.

Perf //table name
| where CounterName == "Free Megabytes" //filtering data by 'free megabytes'
| project Computer, CounterName, FreeGB=CounterValue / 1024, FreeMB = CounterValue, FreeKB = CounterValue * 1024 //only show these columns

You might think based on this that, hey, I’ll never need to use the Extend operator again. But, no, that’s not the case and I’ll dig deeper into this as we get closer in the series to building an actual Analytics Rule (see the TOC).

And, then there are also some other Project options that are so hugely valuable that they have their own operator reference page and are actually considered their own operators. However, in respect to our discussion in this series you should absolutely keep each of these in your toolbelt and have knowledge about them.

Here’s why:

Project-away – Select what columns from the input to exclude from the output. Project-away is probably one of my favorite Project operator variants because it’s an efficiency tool. Most tables have 25 or more data columns stored inside. What if you want to display all columns except for 4 or 5? Would you use the Project operator and just manually type out 20 columns to show? I hope not – once you understand how Project-away works. Using Project-away you can effectively tell the query engine to display ALL columns EXCEPT the one’s you list in the Project-away statement. Notice also that wildcards are supported:

Tablename 
| project-away column1, column2, column3*

Project-keep – Select what columns from the input to keep in the output. This Project variant is essentially the default operation. Hint: Just use the standard Project option.

Project-rename – Renames columns in the result output. This option gives you the ability to rename column headers during query-time. Don’t like a column name? Or, maybe your team has standardized on Computer as a specific value for the Hostname column. This is how you can change the column name in the results. Note that this does not change the data column name in the table – just in the query-time results.

Tablename 
| project-rename my_new_column_name = old_column_name

Project-reorder – Reorders columns in the result output. Most generally, the order of columns in the results will be determined based on their original order in the table. But, alas, sometimes even that doesn’t hold true. If you want to make sure to display the columns in a specific order without turning their fate over to chance, use project-reorder.

Tablename 
| project-reorder column2, column3, column1