Become Proficient with KQL in 10 Minutes

Learn the 5 functions that make up 90% of KQL queries.

Kusto Query Language (KQL) is the querying language that Microsoft uses on all their cloud platforms. It may seem like an intimidating challenge to learn a whole new query language to work with Microsoft, but most of the queries become straightforward after learning the basic syntax and some of the common functions.

In this blog, we will go through the query above and separate it into pieces so that someone with no knowledge of querying languages can learn how to use the most frequently used KQL functions. If the reader can understand this query, they will be equipped to read most other queries because they’d understand the fundamentals and can Google the rest.

Let

The “Let” function allows users to set a variable that can be used later in the function. This can be used either as a traditional variable or to keep the query organized for anyone that is reading it.

In the example query, the let function is used 3 times to label the event IDs that will be looked for in the query. Users that run this query will be able to use the let names to know right away what kind of firewall changes were made based on the let function name associated with the event ID.

Where

The “Where” function is used inside of the query to narrow down the results that the query will display. The first where statement limits the timeframe of the results, and the second where statement only looks for the event IDs outlined in the let statements.

Time Generated

“TimeGenerated” is a timestamp that is used in all KQL tables that has the exact time the log entry was generated. This is used in combination with a where statement to only look for logs that were generated in the last 1 day, which can also be a variable set in the Let function.

Project

The “Project” function is used to limit the number of columns that the user will see when they run the query. Using the project function is vital for keeping the user focused when going through the information that comes back from the query.

In this example, only the account, domain, event ID, and time generated will be displayed from the original table. This will allow the user to see only the most important information right away. If they need more, they can modify the list in the project function to show more columns.

Extend

The “Extend” function allows the user to add new columns to the results that were not in the original table. This is useful for creating new values using basic arithmetic or for gathering data from other sources.

In the example query, the “ingestion time” was added by the query, this can help security analysts put together a more accurate timeline of what happened by having both the time the log was generated and when the log was ingested by their SIEM system.

We will continue to share best practices and lessons learned in future posts on writing useful KQL queries. The basics in this guide are useful for getting started, but more query functions may need to be added soon to help users get the most out of their data.

In closing, consider these three questions when using KQL queries in your organization:

1. Can we get more out of our Microsoft cloud security services by analyzing our data with KQL?
2. Does our organization’s cybersecurity team know how to use KQL to get investigation data out of our Microsoft cloud services?
3. Have we created advanced KQL queries and made them available to our cybersecurity analysts for rapid investigations and mitigations?