Using KQL to Master Sentinel Data

Kusto Query Language, or KQL for short, is omnipresent in the Microsoft world and is used in different product stacks. Like any language, KQL can be challenging to understand and know where to start. This article is intended to help newcomers to get started.

Applications that Support KQL:

A common misconception is that Microsoft Sentinel is the only application that uses KQL, but that is far from true. The language is used increasingly throughout the Microsoft cloud stack, not only in security products. Below is a subset of Microsoft products that currently support KQL.

• Log Analytics
  • Microsoft Sentinel is built on top of Log Analytics, so this is a no-brainer. I still wanted to mention Log Analytics as it’s not only security-focused. Log Analytics also reports querying data about your Azure IaaS and PaaS resources.
• Microsoft 365 Defender
  • The advanced hunting feature allows you to retrieve data generated by the Defender stack. With KQL, we can query the data to get valuable insights.
• CMTrace
  • CMTrace is a part of Microsoft Endpoint Manager Configuration Manager, which allows you to retrieve data from your ConfigMgr machines in real time. Using KQL, we can get information into the installed programs or hardware, among others.
• Application Insights
  • This is one of the use cases which is not specific to security. Application Insights is useful for developers to find runtime information about their applications, such as all the exceptions thrown.
• Azure Data Explorer
  • Azure Data Explorer was one of the first Azure resources to use KQL. It is a real-time analysis service meant for large volumes of data.

How to get started

To start, I want to mention that KQL is often compared to SQL as they have many similarities. Just as SQL, KQL queries always start with specifying the tables to use; each table has multiple columns of different types (string, integers…). After you choose the tables, you can add additional statements to your query by using the pipe sign (‘|’). Each statement starts with an operator, which can be compared to a function. Some examples of operators are:

• Extend => create new (calculated) columns
• Top => only retrieve the top x results
• Where => creation of a condition

When starting, I recommend that you get to know the different tables that are available in your workspace. In Sentinel, we can do this by opening the ‘Logs’ tab and expanding the ‘Microsoft Sentinel’ category of tables. This shows every table in a specific category. Each table can be expanded to show every available column with their specific types (Figure 1).

The main issue you might face is identifying the table and columns where your data is stored. For that, there are two valuable operators: search and take.

The search operator looks into every available table or column to find information. It can be compared to a where operator without any conditions. The search operator is available at all levels. If you are unsure about which table data is saved, you could run a query such as search “GroupMember”. This will search every table within your workspace and look for the word ‘GroupMember’. If you already know the correct table, you can also use the following query AuditLogs | search “GroupMember” which will search for the string in the specific table.

It is essential to understand that the search operator is not a high-performant operator as it is ‘lazy’; it does not require any conditions. You should avoid using the search operator for anything other than exploring data, as Microsoft might throttle queries if they are too inefficient.

Besides search, the take operator can also be used to explore your tables and columns. The take operator is followed by an integer. An example query is AuditLogs | take 1; this returns one row of the AuditLogs table. This is useful for finding exactly what data is within your table. Using the take operator increases performance as returning one row is much faster than 50,000.

Official Documentation and Third-Party Resources

There is a ton of good documentation about KQL available. This includes official documentation from Microsoft and helpful third-party resources created by the community.

• Microsoft Sources
  • The KQL documentation is available on Microsoft Learn and includes detailed information about all available operators, together with some examples for each one. This is not ideal for beginners who are reading through 200 different operators.
  • As part of the SC-200 certification path on Microsoft Learn, there is a module specific to KQL. This provides a basic step-by-step introduction to the language.
• Third-party sources
  • The primary way to learn KQL is through the MustLearnKQL course, created by Rod Trent, a Microsoft employee. This is an open-source training course available through GitHub and Rod’s blog. It provides a step-by-step language walkthrough together with useful tips and tricks. After you finish the training, you can take a small quiz that offers a completion certificate at the end.
  • If you are keen on video training, Pluralsight has a course called ‘KQL from scratch’, which does an excellent job at introducing KQL and the different operators step-by-step. While Pluralsight is not free, you can enable a 14-day trial to play around with.

To start, I recommend checking out Pluralsight as it’s a great way to get an overview of everything. After this, use the MustLearnKQL course as a refresher and for Sentinel-specific information.

The Curse of Portals

One of the common pitfalls I see is portals. Most of the information that’s available through KQL is available through some portal/GUI. While it is the easiest to retrieve this information through the portal, it is not efficient in the long run. KQL is much easier to create complex queries, which might be impossible or labor-intensive through the portal.

In the beginning, you will find it easier to use the portal and ditch KQL. I recommend that you ‘force’ yourself to use KQL, as it is best in the long run. Using KQL will make you more efficient, but there is a learning curve. You need to know which tables and columns contain the correct information, which takes some time. But with some practice, you can be efficient with KQL.

KQL is an Important Skill

KQL is omnipresent and is used through multiple products in the Microsoft stack. If you use Microsoft (Cloud) in your day-to-day job, you will run into it sooner rather than later. KQL is an important skill and will help you move faster and find correct data more efficiently.