4.KQL Variables to Optimize Your Query

In this fourth blog post of my blog post series on mastering KQL, I will explore the use of variables to optimize your queries. Specifically, I will dive into the `let` command, a powerful tool for enhancing the efficiency and readability of your KQL queries. By the end of this post, you will have a solid understanding of how to use the `let` command to its full potential, enabling you to write more effective and maintainable queries.

Agenda:

Introduction

What is the ‘let’ command

What are the benefits

My recommendations

Conclusion

Introduction

In this fourth installment of the blog post series on mastering KQL, I will explore the use of variables to optimize your queries. Specifically, I will dive into the `let` command, a powerful tool for enhancing the efficiency and readability of your KQL queries. By the end of this post, you will have a solid understanding of how to use the `let` command to its full potential, enabling you to write more effective and maintainable queries.

What is the `let` Command?

The `let` command in KQL is used to define variables that can store the results of expressions or queries. These variables can then be reused throughout your query, making it more concise and easier to manage. The `let` command can be particularly useful when you have complex expressions or when you want to reuse the same calculation multiple times within a query.

The `let` command can also help in simplifying the maintenance of your queries. If you need to update a frequently used expression, you only need to change it in one place, rather than updating multiple instances throughout your query. This not only saves time but also reduces the risk of errors.

In addition to improving readability and maintainability, the `let` command can enhance performance. By computing complex expressions once and storing the result in a variable, you can avoid redundant calculations, which can be particularly beneficial when working with large datasets.

Moreover, the `let` command supports the creation of more modular queries. By defining variables for different parts of your query, you can reuse these components in other queries, promoting a more modular and reusable codebase. This modularity can significantly ease the process of debugging and extending your queries.

The syntax for the `let` command is straightforward. You start with the keyword `let`, followed by the variable name, the assignment operator `=` and the expression or query whose result you want to store. Here's the syntax format:

let VariableName = ExpressionOrQuery;

Here's an example of how to use the `let` command ( you can have as many as you would like):

let EndpointWithNTLMHash = 
ExposureGraphEdges
| where EdgeLabel == @"has credentials of"
| where EdgeProperties.rawData.ntlmHash.ntlmHash == "true"
| distinct SourceNodeName;
let VulnerableEndpoint =
DeviceTvmSoftwareInventory
| where SoftwareName contains "office_" and SoftwareVendor == "microsoft"
| where DeviceName has_any (EndpointWithNTLMHash)
| distinct DeviceName;
DeviceNetworkEvents
| where RemotePort == "445" and RemoteIPType == "Public"
| where ActionType == "ConnectionSuccess"
| where DeviceName has_any (VulnerableEndpoint)

(this is a Steven Lim query from his GitHub respository)

The query uses the `let` command to break down a complex series of conditions into easier-to-understand components.

First, the `EndpointWithNTLMHash` variable is defined. It filters the `ExposureGraphEdges` to find nodes where the label indicates they have NTLM credentials, and the NTLM hash is marked as true. It then selects distinct source node names from these results.

Next, the `VulnerableEndpoint` variable is created. It filters the `DeviceTvmSoftwareInventory` for devices with software names containing "office_" and a vendor of "microsoft". It then checks if these devices are within the list defined by `EndpointWithNTLMHash` and selects distinct device names that meet these criteria.

Finally, the query looks at `DeviceNetworkEvents` to find events where the remote port is 445, the remote IP type is public, and the action type is "ConnectionSuccess". It then checks if the device name matches any in the `VulnerableEndpoint` list.

Using the `let` command in this way improves the readability and maintainability of the query by breaking it down into smaller, more manageable parts. It also allows for easier debugging and potential performance optimizations.

let recentDevices = DeviceInfo
    | where Timestamp >= ago(7d);  // Filter devices from the last 7 days
recentDevices
    | where OSPlatform == "Windows"  // Filter for Windows devices
    | project DeviceName, OSPlatform, Timestamp  // Display relevant fields
    |where isnotempty(DeviceName)

The query uses the let command to define a variable (recentDevices) that stores data from the last 7 days. It then filters this data for Windows devices, ensures the DeviceName field is not empty, and selects only the DeviceName, OSPlatform, and Timestamp columns to display. This approach organizes the query for clarity and reuse.

What Are the Benefits?

Using the `let` command in your KQL queries offers several benefits:

Improved Readability: By breaking down complex queries into smaller, named components, you make your queries easier to understand. This can be particularly helpful when collaborating with others or when revisiting your queries after some time.

Enhanced Maintainability: Variables allow you to reuse expressions and calculations, reducing redundancy in your code. This makes it easier to update and maintain your queries as changes only need to be made in one place.

Performance Optimization: Defining variables can help optimize query performance by reducing the need for repeated calculations. This can lead to faster query execution times and more efficient use of resources.

Simplified Debugging: Using variables can make it easier to identify and isolate issues in your queries. By breaking down your query into smaller parts, you can test and validate each component individually.

My Recommendations

To get the most out of the `let` command, consider the following best practices:

Use descriptive variable names that clearly describe their purpose. This will make your queries more intuitive and easier to follow. While variables can improve readability and maintainability, avoid overusing them, as this can lead to overly fragmented queries. Strive for a balance between using variables and maintaining a coherent query structure. Scope your variables appropriately to avoid unnecessary memory usage, and only keep variables that are essential for the subsequent steps of your query. When building complex queries with variables, test each step incrementally to ensure correctness, which will help you catch errors early and debug more effectively.

Additionally, always document your variables and their intended use. This practice not only aids in keeping your queries understandable for others but also helps you remember the specific purposes of each variable when revisiting your code after some time. Consistent documentation is especially crucial in collaborative environments where multiple team members might work on the same queries.

Furthermore, consider the performance implications of your variable definitions. Efficient use of variables can significantly reduce the computational load and improve the execution speed of your queries. Optimize your queries by eliminating redundant calculations and consolidating variable definitions where possible. By doing so, you ensure that your queries are not only easy to read but also perform efficiently under varying loads.

Lastly, stay updated with the latest features and enhancements in KQL. The language is continually evolving, and new functions or improvements to existing commands can provide more optimal solutions for your queries. Regularly revisiting and refactoring your KQL queries in light of these updates can lead to better performance and maintainability.

Incorporating these best practices will help you leverage the full potential of the `let` command, enabling you to write more effective and maintainable KQL queries. By thoughtfully naming, documenting, optimizing, and staying current with KQL advancements, you can transform complex querying tasks into streamlined processes that drive your analytic and operational goals forward.

Conclusion

The `let` command in KQL boosts readability, maintainability, and performance. By using variables for intermediate results, you can simplify and optimize complex queries. Follow these best practices to maximize the `let` command's potential for effective and maintainable KQL queries.

In my next blog post, I will continue to explore advanced KQL techniques, delving deeper into the language's capabilities and showcasing how to leverage its full potential in your security operations. Stay tuned as I continue this journey together, mastering KQL and enhancing our security practices. I look forward to embarking on this journey together as I master KQL and enhance our security practices.