Introduction
I am sure a lot of you, like me, miss being able to call sub-routines in KQL. You can always create a function and call that, but it isn’t quite the same thing. This is where mv-apply comes into play.
The official documentation for mv-apply (located here) states that it “Applies a subquery to each record and returns the union of all the results of the subqueries.” But what does that really mean?
mv-apply
What it means is you can pass in a column to whatever code you have in your mv-apply operator, and it will perform that code against every row that the column generates. Let’s take a look at the steps that the mv-apply operator performs (again coming from the official documentation)
- Uses the mv-expand operator to expand each record in the input into subtables (order is preserved).
- Applies the subquery for each of the subtables.
- Adds zero or more columns to the resulting subtable. These columns contain the values of the source columns that aren’t expanded and are repeated where needed.
- Returns the union of the results.
Step 1 is pretty self-explanatory. The mv-expand command will create a new row for each entry in the column that is being looked at. So, if the column I am looking at is a JSON array containing 4 entries, then 4 rows will be created.
Step 2 is also self-explanatory. The code that is included will be run on each row that was generated.
Step 3 is a bit confusing. Basically, it is saying if the code being run would normally generate a column, like summarize, this will be created in the parent row.
Step 4 is also self-explanatory. Return a union of all the results of all the rows that were created. Keep in mind that the rows that were created would get deleted, unless your code does something that would require them to stay. We will look at that a bit later.
How call mv-apply
How can we call mv-apply. The official documentation shows:
T|mv-apply [ItemIndex] ColumnsToExpand [RowLimit] on(SubQuery)
Where T is the table you want to use.
[ItemIndex] allows you to set a variable that contains the row’s index number that can be used in the code. Typically index is used as it is easy to understand what the variable does. The format will be like
with_itemindex = index ColumnsToExpand will contain all the columns you want to expand, comma delimited. You can also associate a name with the column to expand. Personally, I recommend that since it makes it easier to keep in mind that the variable is temporary and follows best practices of other programming languages. You can also add “to typeof (typename)” if you need to convert the column into a different data type. Keep in mind that all the columns need to be dynamic.
RowLimit can be used to limit how many rows will be temporarily created.
And SubQuery contains all the code you want to run.
Example
Now let’s take a look at an example. Suppose you need to count how many entities there are in each alert. This would be hard to do without the mv-apply operator. Take a look at the code below
SecurityAlert
| mv-apply tempParams = todynamic(Entities) on (
summarize EntityCount = count(tempParams)
)Line 1 is the table to look at
Line 2 is where it gets interesting. We call the mv-apply operator and we want to use the Entities column. Since this column is a string type, we use the todynamic command to change it to a dynamic type. We are also setting it to the variable called tempParams to make it easier to keep everything straight.
Line 3 is the actual code that will be run. In this case we are getting the count of all the tempParams that were created. Note that the first line does not require the pipe “|”. It is also worth noting that if I did not create the column called EntityCount, a column could still be created but it would have the name count_tempParams, which is hard to understand its purpose.
Line 4 just closes out the code to run.
When we run the code, we can see that the new column, EntityCount, is created and will contain the value we want.
It also does not generate any extra rows (although you will have to take my word for that or just run the code yourself).
Earlier I mentioned that the extra rows will not be created unless you use a command that would cause those rows to be generated. Let’s say that instead of counting all the entities, I want to see just those entities that have IP addresses in them. This following code was modeled after the “Mass Download & copy to USB device by single user”. I say modeled since it appears there is a mistake in the code where the author calls mv-expand directly which causes too many rows to be created.
SecurityAlert
| mv-apply tempParams = todynamic(Entities) on (
where tempParams.Type == "ip"
| extend IpAddress = tostring(tempParams.Address)
)This isn’t too difference than the previous code. The big differences are the filtering on line 3 (remember, no pipe needed on the first line of the subquery), and the creation of the column on the 4th line.
Running this will create the output like shown below.
In this case, a new row will be created for each IPAddress entity. You could do the same thing using the mv-expand command and a filter, but I think this is a bit easier to read and understand.
There are more examples in the official documention.
Summary
The mv-expand operator makes it easy to apply the same code to multiple rows without necessarily creating all those rows. It can be used to perform actions that could not be done in other ways or to make your code a bit more readable.