Let statement
Use the let statement 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.
Syntax
Syntax for scalar or tabular expressions
let Name = ScalarExpression
let Name = TabularExpression
| Field | Definition | Example |
|---|---|---|
| Name | The variable name, must be valid. | You can escape the name, for example ["Name with spaces"] |
| ScalarExpression | An expression with a scalar result. | let one=1; |
| TabularExpression | An expression with a tabular result. | let RecentLog = Logs \| where Timestamp > ago(1h) |
Syntax of view or function
let Name = [view] ( [TabularArgName : ( * ) , [ArgName : ArgType ], ... ] ) { FunctionBody }
let Name = [view] ( [ [TabularArgName : ([AttributeName : AttributeType] [, ... ] ) ] , [ [ArgName : ArgType , ...] ] ) { FunctionBody `}
| Field | Definition |
|---|---|
| FunctionBody | An expression that yields a user defined function, an anonymous function declaration. |
| view | Appears only in a parameterless let statement with no arguments. When used, the let statement is included in queries with a union operator with wildcard selection of the tables/views. |
| TabularArgName | The name of the tabular argument. Can appear in the FunctionBody and is bound to a particular value when the user defined function is invoked. |
| AttributeName : AttributeType | The name and type of the attribute. Part of the table schema definition, which includes a set of attributes with their types. |
| ArgName | The name of the scalar argument. Can appear in the FunctionBody and is bound to a particular value when the user defined function is invoked. |
| ArgType | The type of the scalar argument. Currently the following are supported for user defined functions: bool, string, long, datetime, timespan, real, and dynamic (and aliases to these types). |
Note
- You can use
(*)for the tabular expression. - When using a tabular expression as part of a user defined function, the columns can't be accessed as part of the function.
- Tabular arguments appear before scalar arguments.
- Any two statements must be separated by a semicolon.
Examples
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 x using the ['name'] notion, and then uses it in a tabular expression statement.
let ['some number'] = 20;
range y from 0 to ['some number'] step 5
Create 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
| x | result |
|---|---|
| 1 | 5 |
| 2 | 10 |
| 3 | 15 |
| 4 | 20 |
| 5 | 25 |
Create 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
| x | result |
|---|---|
| 10 | 0 |
| 11 | |
| 12 | 2 |
| 13 | 3 |
| 14 | 4 |
| 15 | 5 |
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
| result |
|---|
| 50 |
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
| $table | MyColumn |
|---|---|
| Range10 | 5 |
| Range20 | 5 |
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
| Day1 | Day2 | Percentage |
|---|---|---|
| 2016-05-01 00:00:00.0000000 | 2016-05-02 00:00:00.0000000 | 34.0645725975255 |
| 2016-05-01 00:00:00.0000000 | 2016-05-03 00:00:00.0000000 | 16.618368960101 |
| 2016-05-02 00:00:00.0000000 | 2016-05-03 00:00:00.0000000 | 14.6291376489636 |
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 | ...