KQL

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

FieldDefinitionExample
NameThe variable name, must be valid.You can escape the name, for example ["Name with spaces"]
ScalarExpressionAn expression with a scalar result.let one=1;
TabularExpressionAn 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!

Tiago Souza

Tiago Souza

Security Technical Specialist
Cyber Security Technical Specialist at Microsoft | Cloud Security & Threat Protection | Blog content creator at CyberGeeks.Cloud - https://linktr.ee/tiagovf