Must Learn KQL Part 13: The Extend Operator

Chapter 13

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.

…

I think it’s necessary at this point to do a slight self-recap because in the next few parts/chapters for this Must Learn KQL series (parts 13-16), I’ll talk about how to manipulate the KQL query results so that they can be customized to show exactly what is important to your operations.

Jumping all the way back to Must Learn KQL Part 3: Workflow, realize where we are in the workflow. We’re actually very near the end of our original goal. We have discussed finding and organizing data, now it’s time to learn how to provide custom views of the data.

Custom data views are important in that each environment is different and each environment’s requirements for security will differ – sometimes greatly. Whether its geographical, business political, or something else, the data that is exposed will alter the perception of the organization’s risk. So, it’s important to expose the right data.

One valuable operator provided with KQL to customize the data views is the Extend operator. The Extend operator allows us to build custom columns in real-time in the query results. It allows you to create calculated columns and append them to the results. Understand, though, that we’re not creating columns of data that are stored back into the data table, but only generating a column of custom data based on our current request.

Here’s a good example…

The following query looks through the Computer data column in the SecurityEvent table, calculates the character length of the name of each computer found, and produces custom column called ‘ComputerNameLength’ in the results. Feel free to use this query our the KQL Playground demo environment (https://aka.ms/LADemo).

SecurityEvent //the table
| extend ComputerNameLength = strlen(Computer) //creates a new column called ComputerNameLength of the calculation of the number of characters of the computer name in the Computer column

Here’s what this will look like…

Custom column

Again, this column of data is generated in real-time. Once the results have been cleared, this data no longer exists.

Extend operator syntax

TableName
| extend [ColumnName | (ColumnName[, ...]) =] Expression [, ...]

In simpler terms, just as before with our standard query workflow we (1) give the query engine the table we want to use, then (2) use the extend operator to assign a custom name to a new column, and then (3) insert data into it.

So, using the previous example, I:

1. Designated the SecurityEvent table

2. Assigned the name ComputerNameLength to the new column

3. Inserted the data I wanted to see. In this case, the hostname length for each computer found in the data.

The data that is inserted into the custom column(s) can be text, number values, calculations, etc., etc., etc. I can use and combine existing table data, or I can fabricate data to be included in the custom column.

In the following example, I’m literally just making stuff up in that the first column called My_Calculation, is just the result of 8 x 8 (64), and My_Fabricated_Data is just something I wanted to say.

SecurityEvent
| extend My_Calculation = 8*8
| extend My_Fabricated_Data = "Yay for me!"

The results look like the following…

Just random stuff

But the true beauty for this function is to take existing data, combine it, and display it in meaningful ways.

Take the following as an example of using existing data to make to display it in better ways. Use the following query in the KQL Playground (https://aka.ms/LADemo).

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

This example looks at the Perf table to find free disk space on the recorded systems and display it in kilobytes, megabytes, and gigabytes. Your results will look something like the following:

Free disk space

The Extend operator is a valuable tool to enable customizing the data that is displayed. As noted, we’ll be working with several KQL operators to help develop our own custom views in the next few parts/chapters. But the Extend operator is a key creation key tool that you’ll find used throughout tools like Microsoft Sentinel to provide things like data parsing and creating custom entities. If you’re working with data from custom log files, for example, that data is probably not normalized, and you’ll need to expose things like usernames and hostnames that can’t be exposed on their own. This is where the Extend operator provides huge value. And, as we continue our march to building your first Microsoft Sentinel Analytics Rule, the Extend operator is yet another logical step in that process. Extend is used quite a bit in Analytics Rules, so understanding it’s power and capability is important.

For those that want just a little bit more before we come together again for the next part/chapter, here’s a task for you.

I was tasked recently to locate billable data by computers – or which computers were costing the company the most. The following is what I came up with.

find where TimeGenerated > ago(1d) project _BilledSize, _IsBillable, Computer, _ResourceId
| where _isBillable=true and isnotempty(Computer) 
| summarize billedData = sumif(_BilledSize, _IsBillable=~true),
            freeData   = sumif(_BilledSize, _IsBillable=~false) by Computer 
| extend Total_Data = billedData + freeData            
| order by billedData desc

Now, don’t freak out. This is really not that difficult to understand. Remember, our goal with this series is to be able to look at a query and understand what the results will be – whether we created the query or not.

Take some time and break this down.