Let statement

A let statement is used to set a variable name equal to an expression or a function, or to create views.

let statements are useful for:

• Breaking up a complex expression into multiple parts, each represented by a variable.
• Defining constants outside of the query body for readability.
• Defining a variable once and using it multiple times within a query.

If the variable previously represented another value, for example in nested statements, the innermost let statement applies.

To optimize multiple uses of the let statement within a single query, see Optimize queries that use named expressions.

Syntax: Scalar or tabular expressions

let Name = Expression

Parameters

Syntax: View or function

let Name = [view] ([ Parameters ]) { FunctionBody }

Parameters

Examples

The examples in this section show how to use the syntax to help you get started.

The query examples show the syntax and example usage of the operator, statement, or function.

Define scalar values

The following example uses a scalar expression statement.

let n = 10;  // number
let place = "Dallas";  // string
let cutoff = ago(62d); // datetime 
Events 
| where timestamp > cutoff 
    and city == place 
| take n

The following example binds the name some number using the ['name'] notation, and then uses it in a tabular expression statement.

let ['some number'] = 20;
range y from 0 to ['some number'] step 5

Output

Create a user defined function with scalar calculation

This example uses the let statement with arguments for scalar calculation. The query defines function MultiplyByN for multiplying two numbers.

let MultiplyByN = (val:long, n:long) { val * n };
range x from 1 to 5 step 1 
| extend result = MultiplyByN(x, 5)

Output

Create a user defined function that trims input

The following example removes leading and trailing ones from the input.

let TrimOnes = (s:string) { trim("1", s) };
range x from 10 to 15 step 1 
| extend result = TrimOnes(tostring(x))

Output

Use multiple let statements

This example defines two let statements where one statement (foo2) uses another (foo1).

let foo1 = (_start:long, _end:long, _step:long) { range x from _start to _end step _step};
let foo2 = (_step:long) { foo1(1, 100, _step)};
foo2(2) | count

Output

Create a view or virtual table

This example shows you how to use a let statement to create a view or virtual table.

let Range10 = view () { range MyColumn from 1 to 10 step 1 };
let Range20 = view () { range MyColumn from 1 to 20 step 1 };
search MyColumn == 5

Output

Use a materialize function

The materialize() function lets you cache subquery results during the time of query execution. When you use the materialize() function, the data is cached, and any subsequent invocation of the result uses cached data.

let totalPagesPerDay = PageViews
| summarize by Page, Day = startofday(Timestamp)
| summarize count() by Day;
let materializedScope = PageViews
| summarize by Page, Day = startofday(Timestamp);
let cachedResult = materialize(materializedScope);
cachedResult
| project Page, Day1 = Day
| join kind = inner
(
    cachedResult
    | project Page, Day2 = Day
)
on Page
| where Day2 > Day1
| summarize count() by Day1, Day2
| join kind = inner
    totalPagesPerDay
on $left.Day1 == $right.Day
| project Day1, Day2, Percentage = count_*100.0/count_1

Output

Using nested let statements

Nested let statements are permitted, including within a user defined function expression. Let statements and arguments apply in both the current and inner scope of the function body.

let start_time = ago(5h); 
let end_time = start_time + 2h; 
T | where Time > start_time and Time < end_time | ...

Tabular argument with schema

The following example specifies that the table parameter T must have a column State of type string. The table T may include other columns as well, but they can’t be referenced in the function StateState because the aren’t declared.

let StateState=(T: (State: string)) { T | extend s_s=strcat(State, State) };
StormEvents
| invoke StateState()
| project State, s_s

Output

Tabular argument with wildcard

The table parameter T can have any schema, and the function CountRecordsInTable will work.

let CountRecordsInTable=(T: (*)) { T | count };
StormEvents | invoke CountRecordsInTable()

Output