Kusto KQL - Part 4 - Variables and Arrays
- brencronin
- 51 minutes ago
- 5 min read
KQL Essentials: Variables with let and Arrays with dynamic
Kusto Query Language (KQL) supports variables using the let statement, which allows you to store values, expressions, or entire subqueries for reuse within a query. This enhances readability, reduces duplication, and improves maintainability of complex queries.
Using let to Create Variables
The let statement follows this basic format:
let VariableName = ExpressionOrValue;Key Components of let statement:
let – Keyword to initiate variable declaration.
VariableName – Descriptive name for your variable.
= – Assignment operator.
ExpressionOrValue – Can be:
A scalar value: 10, "admin", true
A scalar expression: now(), startofday(ago(1d))
A tabular expression: A subquery, like:
kql
CopyEdit
let FloridaEvents = StormEvents | where State == "FL";
; – A semicolon is required to terminate the let statement.
Example:
let BlockedPorts = dynamic([22, 445, 3389]); DeviceNetworkEvents
| where RemotePort in (BlockedPorts)Practical Uses of let
Time filtering:
let StartTime = ago(1d); let EndTime = now(); Heartbeat
| where TimeGenerated between (StartTime .. EndTime)Multiple reuse of a subquery:
let SuspiciousLogins = SigninLogs
| where Location != "US"; SuspiciousLogins
| summarize count() by UserPrincipalNameWorking with Arrays: dynamic Type
In Kusto Query Language (KQL), values are assigned to arrays using the dynamic data type or by utilizing aggregation functions that produce dynamic arrays. Use the dynamic() function to define arrays or JSON-style objects. This is especially helpful for filtering against multiple values:
1. Direct Assignment with dynamic:
The most straightforward way to assign values to an array is by using the dynamic keyword and enclosing the values within square brackets [].
let myArray = dynamic(["value1", "value2", 123, true]);2. Using pack_array:
The pack_array() function can be used to create a dynamic array from a list of scalar expressions.
let myPackedArray = pack_array("apple", "banana", "orange");3. Aggregation Functions:
Several aggregation functions in KQL are designed to create dynamic arrays by collecting values from a group of rows. These are typically used within a summarize operator.
make_list(column):Creates an array containing all values from the specified column within each group.
make_list_if(column, predicate):Similar to make_list, but only includes values where the predicate is true.
make_list_with_nulls(column):Creates an array including all values from the specified column, even if they are null.
make_set(column):Creates an array containing only the unique values from the specified column.
make_set_if(column, predicate):Similar to make_set, but only includes unique values where the predicate is true.
Example using make_list:
MyTable | summarize myValues = make_list(ColumnToAggregate) by GroupingColumnDynamic Array practical examples
List of IPs.
let BannedIPs = dynamic(["192.168.1.1", "10.0.0.5"]);
DeviceNetworkEvents
| where RemoteIP in (BannedIPs)Define filters once and reuse across multiple tables:
let TargetUsers = dynamic(["admin", "svc-account"]);
DeviceLogonEvents
| where AccountName in (TargetUsers)List of bad malcious command line arguments used to evaluate logs.
let BadArgs_LOLBinaryname = dynamic(["/val1", "/val2"]);
DeviceProcessEvents
//Searches for the LOL binary execution with any of the suspicious process arguments created in the array with dynamic. Repeat for initiating process command line
| where ProcessCommandLine has "LOLBinaryname" and ProcessCommandLine has_any (BadArgs_LOLBinaryname)Example BITSAdmin
let BadArgs_LOLBitsAdmin = dynamic(["/create", "/addfile", "/transfer", "download", "upload", "http", "/SetNotifyCmdline", "%COMSPEC%", "cmd.exe", "powershell.exe", "regsvr32.exe", "https", "ftp", "ftps"]);
DeviceProcessEvents
//Searches for the LOL binary execution with any of the suspicious process arguments created in the array with dynamic. Repeat for initiating process command line
| where ProcessCommandLine has "bitsadmin" and ProcessCommandLine has_any (BadArgs_LOLBitsAdmin)
Pulling values from a watchlist into an array.
let watchlist = (_GetWatchlist('IP tracker') | project DstIP);
Heartbeat
| where ComputerIP in (watchlist)Tips for dynamic arrays:
Values must match the case of data in the column unless you're using case-insensitive functions.
dynamic() is often used with has_any, in, and mv-expand for multi-value filtering.
Use todynamic() to cast JSON fields into usable dynamic objects in parsed logs.
Scalar vs. Non-Scalar Variables in KQL
Scalar Variables
A scalar variable holds a single, typed value a datetime, integer, string, boolean, etc. They're essentially constants you name for reuse or readability.
let eventTime = datetime(2023-10-14 09:00:00);
let sentBytesThreshold = 1000000;These work anywhere an expression is valid, in where, extend, summarize, arithmetic, you name it. The engine substitutes the value inline at parse time, so there's no performance cost.
Tabular Variables
A tabular variable holds a result set, rows and columns, like a mini-table.
let suspiciousIPs = WebProxy
| where SentBytes > 5000000
| summarize by ClientIP;On its own, this is inert. A key construct is, a tabular variable in isolation does nothing useful, you need to join it, union it, or feed it into another query. Its power comes from composition:
suspiciousIPs
| join kind=inner (SecurityEvent) on ClientIPWhere toscalar() Fits In
toscalar() bridges the two worlds, it collapses a tabular result into a single scalar value so you can use it like any scalar variable.
let avgBytes = toscalar(
WebProxy
| summarize avg(SentBytes)
);
WebProxy
| where SentBytes > avgBytes * 2The critical constraint: the tabular expression passed to toscalar() must resolve to exactly one row and one column. If it returns multiple rows, KQL takes an arbitrary value, silently, with no error, which is a common source of bugs.
Situation | Right tool |
Fixed threshold you want to name | let x = 1000000 (scalar literal) |
Dynamically computed single value (avg, max, count) | toscalar(... | summarize ...) |
A set of rows to join/filter against | tabular variable + join or in~ |
Multiple computed values | tabular variable, not toscalar() |
The Practical Distinction
The confusion usually comes from wanting to do something like "filter against a value I computed from the data itself." A tabular variable can't go in a where clause directly, but a scalar can. So the pattern is:
// ❌ This doesn't work — tabular in a scalar position
let threshold = WebProxy | summarize max(SentBytes);
// ✅ toscalar() makes it usable as a filter
let threshold = toscalar(WebProxy | summarize max(SentBytes));
WebProxy
| where SentBytes > threshold * 0.9The short rule: if you computed it and need to compare against it, use toscalar(). If you computed a set of things to match against, keep it tabular and use join or | where x in (tabularVar).
The diagram shows the three "worlds" and how they connect:
Blue (top-left): Scalar literals live here. A single typed values you can use directly in any filter or calculation without any conversion.
Teal (bottom-left): Tabular variables live here. They hold result sets but are inert alone, only becoming useful when composed with joins, unions, or in~ lookups.
Amber (center): toscalar() is the bridge. It takes a tabular expression and collapses it into something the scalar world can use. The coral warning box is the key thing to internalize: if your tabular expression returns more than one row, KQL silently picks an arbitrary value with no error, so always pair it with an aggregation like max(), avg(), or count() that guarantees a single result.
Best Practices using let
Keep variable names descriptive and meaningful.
Group related let statements at the top of KQL queries for clarity.
Use let to simplify repetitive filters or expressions.
For performance, define variables only when reused or when improving readability.
Use tabular let expressions to pre-filter large tables and reduce query cost.
Comments