KQL fundamentals – Let statement
Level: Beginner | Reading time: 5 minutes
If you have ever had contact with any programming language, you should know a little bit about declaring variables. Let statements are used to assign a value to a variable as seen in the example below using dates:
let today = endofday(now()); let yesterday = startofday(now(-1d));
Associating names with expressions, let is going to help you to reuse a value in the future and also allows you to split a complex expression into several parts. You can associate each part with a name via the let statement to compose a more complete search.
The expressions associated with let statements can be of the following types:
- Scalar expression
- Tabular expression
- User defined function
We’ll see a little bit of each of them in future articles, don’t worry about those names now.
It is important to say that the names associated by let statements must be valid entity names, as we see in the example below taken from the Microsoft documentation.
Sintaxe: let Name = ScalarExpression | TabularExpression | FunctionDefinitionExpression
| 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) |
We can also use the let more than once in our search using the semicolon (;) delimiter between them, as in the following example.
let start = ago(5h);
let period = 2h;
T | where Time > start and Time < start + period | …
In the example below, timeOffSet and discardEventId are created and used as part of the SecurityEvent “where” statement.
let timeOffset = 7d;
let discardEventId = 4688;
SecurityEvent
| where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset)
| where EventID != discardEventId
We can use the let statement also for dynamic tables or lists:
let suspiciousAccounts = datatable(account: string) [
@"\administrator",
@"NT AUTHORITY\SYSTEM"
];
SecurityEvent | where Account in (suspiciousAccounts)
Another example:
let LowActivityAccounts =SecurityEvent
| summarize cnt = count() by Account
| where cnt < 10;
LowActivityAccounts | where Account contains "Mal"
I got part of the examples of the Program Manager video and presentation material from Microsoft Sentinel, Ofer Shezaf, that I strongly recommend.
Video – Azure Sentinel Webinar KQL part 1.
You can also take a look at a number of examples on the Sentinel GitHub – GitHub – Azure/Azure-Sentinel: Cloud-native SIEM for intelligent security analytics for your entire enterprise. I also recommend for you to take a look at the GitHub – rod-trent/SentinelKQL: Azure Sentinel KQL.
Summary
In this article, I showed how to use the let statement to create and use variables and pivot tables.
References:
Let – Azure Data Explorer | Microsoft Docs
Use the let statement – Learn | Microsoft Docs
Thank you and leave a comment, feedback, or suggestion!
