Fun With KQL – Datatable and Calculations

Introduction

In the conclusion of my last post, Fun With KQL – Datatable, I mentioned a datatable can also be useful when you need to do special calculations. I decided it really needed further explanation and created this post to dive in a bit deeper.

If you haven’t read my datatable post yet, you should as I’ll refer back to it. Go ahead, I’ll wait.

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.

Datatable and Calculations

For this example we’ll revisit the Perf table. The Perf table has two main columns that we’ll use in this example, CounterName and CounterValue.

Our goal for this query is to multiply the CounterValue by some number, based on the CounterName. We could of course use a case or iif within the query, but that would result in a query that is long and hard to read and later modify if we need to.

Using a datatable will result in a query that is much more readable and maintainable. As the actual code for the query is a bit long I’ll place it below to make it easy to follow along. Note I’ll only skim over things we covered in the previous post, refer to it for more detailed explanations.

let CounterMultipliers = datatable (CounterName:string, Multiplier:int)
[
  "CounterName", 2,
  "restartTimeEpoch", 3,
  "memoryWorkingSetBytes", 4,
  "memoryRssBytes", 5,
  "memoryRequestBytes", 2,
  "memoryLimitBytes", 3,
  "memoryCapacityBytes", 4,
  "memoryAllocatableBytes", 5,
  "cpuUsageNanoCores", 2,
  "cpuRequestNanoCores", 3,
  "cpuLimitNanoCores", 4,
  "cpuCapacityNanoCores", 5,
  "cpuAllocatableNanoCores", 2,
  "Total Bytes Transmitted", 3,
  "Total Bytes Received", 4,
  "Logical Disk Bytes/sec", 5,
  "Free Megabytes", 2,
  "Disk Writes/sec", 3,
  "Disk Write Bytes/sec", 4,
  "Disk Transfers/sec", 5,
  "Disk Reads/sec", 2,
  "Disk Read Bytes/sec", 3,
  "Disk Bytes/sec", 4,
  "Bytes Sent/sec", 5,
  "Bytes Received/sec", 2,
  "Avg. Disk sec/Write", 3,
  "Avg. Disk sec/Transfer", 4,
  "Avg. Disk sec/Read", 5,
  "Available MBytes Memory", 2,
  "Available MBytes", 3,
  "% Used Space", 4,
  "% Processor Time", 5,
  "% Free Space", 6
];
let PerfData = view() {
Perf
| project CounterName
        , CounterValue
        , Computer
        , ObjectName
        , TimeGenerated
};
PerfData
| join CounterMultipliers on CounterName
| project CounterName
        , CounterValue
        , Multiplier
        , CounterExpanded = CounterValue * Multiplier
        , Computer
        , ObjectName
        , TimeGenerated

We begin with a let, where we will create a new datatable. I chose to name it CounterMultipliers, but we could have used any name we wanted.

The datatable will have two columns, the CounterName which is a string, and the Multiplier which I made an int.

I used a simple query to get the list of CounterName values:

Perf
| distinct CounterName
| sort by CounterName

I then copied the output into my query editor. I wrapped the names in quotes so they would be interpreted as strings. Next I have a comma, then the integer value to use for the multiplier.

Note I just picked a few random values to use here. In a real world situation this kind of operation, multiplying counter values, might not make much sense, but it will serve OK for this example.

After finishing the datatable definition I use a second let to create a temporary view on top of the Perf table and named the view PerfData. For more on this technique refer back to the Fun With KQL – Datatable post.

Now we take our PerfData view and join it to our datatable, CounterMultipliers. Note that I used the best practice of naming the counter name column in the datatable the same as in the PerfData view. It makes the query easier to read, and avoids the need for $left and $right as I described in the previous post.

Finally we pipe the output of the join into a project operator. I return all the columns, but add a new one, CounterExpanded. For its calculation I simply take the CounterValue column from the PerfData view and multiply it by the Multiplier column from the `datatable.

Below you can see the output from the query.

If you look in the lower right though, you will spot an issue. The query only returned 32 rows.

This demonstrates a critical piece of information when it comes to the join. For each row in the first table, PerfData, it grabs a row from the second table. Since we had 32 rows in the datatable, only 32 rows were returned.

Fixing the Query

Fixing the query is simple. All we need to do is swap the order of the two tables.

CounterMultipliers
| join PerfData on CounterName
| project CounterName
        , CounterValue
        , Multiplier
        , CounterExpanded = CounterValue * Multiplier
        , Computer
        , ObjectName
        , TimeGenerated

Note I only included the last part of the query, everything above this piece is still the same.

Now look at the output.

In the lower right, as well as in the blue area at the top of the output, you can see over 300,000 rows were returned. This is the output we expect, every row in the Perf table (from the view) linked to each row in the CounterMultipliers datatable.

Including and Excluding Columns

In this example, in the output I included the Multiplier column. This was done so you could see the data and test the calculation for yourself. In a normal situation you likely wouldn’t be doing this.

I also included the CounterValue column. Again, this may or may not be needed, you could choose to have just the CounterExpanded column.

Think about the output, and how it will be used to decide if you want to include these types of columns in your query output.

Conclusion

In this post we learned how to use a datatable to create calculated values. This made the code much cleaner, and easier to read.

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.

There are three courses in this series so far:

I have two previous Kusto courses on Pluralsight as well. They are older courses but still valid.

These are a few 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. On the page 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.

Fun With KQL – Datatable

Introduction

As you’ve seen with the join in my Fun With KQL – Join post it can be useful to combine two tables in order to clarify the output. What if, though, you need data that isn’t in an existing table?

That’s where the datatable operator comes into use. The datatable allows you to create a table of data right within the query. We’ll see a few useful examples in this post.

DataTable Basics

Let’s say you have a table that stores colors. These colors though are stored in decimal format, the application that uses them converts them to an HTML hex color. For our report though, we’d like to convert these to a friendly name, which is not stored in the source data.

To accomplish this we can create a color table with the codes as well as friendly color names. We’ll use the datatable operator to do this.

We’ll need to use our old friend let. Following we declare a name for our datatable, here we used HTMLColors although we could use any name we want.

After the equal sign we use the datatable operator. In the parenthesis we declare our column names and their data types. The first is Name, then a colon, then the data type of string. Our second column is also a string, with the name of HexCode.

The final column is DecimalValue, and its datatype is int. You can find a complete list of Kusto data types in the Microsoft documentation, Scalar data types.

After we finish the datatable declaration with a closing right parenthesis, we have an opening square bracket then declare our data. Here I put each row of data on a line by itself, this is a good practice to make it easy to read and modify.

As you can see, our first row is:

"Red",	"#FF0000", 16711680,

First is what I call our friendly name associated with the color, followed by a comma. We wrapped it in double quotes, since it is a string datatype.

Then we have the hex color code, also wrapped in quotes to make it a string, followed by a comma. Finally comes the decimal value, which is just an integer number, no quotes required.

Then line ends in a comma, then we proceed to the next line of data. At the end of our data, the row with Aquamarine, we do not have a comma since that is the last row of data. We have a closing square bracket to finish off the datatable data, then a semicolon as the KQL query will continue.

Next have the name of our datatable, piped into a project, then we listed the three columns in the datatable.

As you can see in the image above, the output section has a list of colors, hex values, and decimal value. Note that a few scrolled off the bottom of the screen.

Now we could join this to a table, linking on the DecimalValue column and display the friendly Name column in the output.

Datatable In Practice

Let’s look at an example of the datatable in practice. The Perf table has a column, ComputerName. A lot of the computer names are rather cryptic though. It’d be nice to have a friendly, human readable name that clearly says what the computer is used for, rather than unclear like JBOX10.

Let’s take a look at the query needed to add friendly names to our Perf table output.

We begin with a let and define a datatable we’ll call computers. It will have two columns, ComputerName and FriendlyName, both of which are strings.

We fall into our data, each line is simply the computer name as found in the Perf table, followed by the friendly name we want to use.

Next comes another let in which we create a datatable from Perf for the last five minutes, and use project to pick out a few columns. We’ve named this datatable PerfInfo.

Now we take our computer datatable and join it to PerfInfo. Because the column we are joining on has different names in each table, I had to use the $left and $right, as described in my Fun With KQL – Join post.

We then project the columns we want, and use sort to order the output.

I should mention I just guessed at the friendly names for some of the computer names in the Perf table. I have no idea if JBOX10 is really the XBox Java Server, but it just sounded good for this demo.

Naming DataTable Columns

I just wanted to mention that normally I would use the same column name in the datatable that is used in the table I’m joining. For example, I would use just Computer in the datatable so it would match up with the Computer column in the Perf table. That way I wouldn’t have to use the $left and $right syntax.

For this example though, I deliberately used a different name so I could remind you of the ability to use $left and $right to join tables when the column name is different.

The most frequent time you’d need this is when you are joining multiple tables with different column names for the same data, which as anyone who has worked with databases for any length of time happens far too often.

Again, for more information on this technique see my Fun With KQL – Join post.

Conclusion

The datatable can be a useful operator for situations where you want to provide user friendly names, but don’t have an existing table that contains them.

It could also be used if you needed to do calculations. For example, rather than using iif statements you could create a datatable with a country code and a standard shipping amount, then in the table use that shipping amount in a calculation, perhaps adding it to the total value of a purchase.

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.

Tag: Datatable

Fun With KQL – Datatable and Calculations

Introduction

Datatable and Calculations

Fixing the Query

Including and Excluding Columns

See Also

Conclusion

Fun With KQL – Datatable

Introduction

DataTable Basics

Datatable In Practice

Naming DataTable Columns

See Also

Conclusion