KQL Overview – Kusto Query Language
Level: Beginner | Reading time: 10 minutes
Are you familiar with the KQL query language – Kusto Query Language? It is used to perform data analysis and to hunt for threats in Microsoft Sentinel, create Analytics and Workbooks, and also to hunt in Defender for Endpoint and other solutions. This post will give you an overview of what you should know to start using this powerful tool. In addition, KQL is also billed on the SC-200 – Microsoft Security Operations Analyst certification exam.
A great starting point is to use the Microsoft Learn website to learn more about KQL, you can access this link.
What is KQL?
KQL, or Kusto Query Language, as I mentioned above, is a powerful language used in searches (hunting) in products such as Microsoft’s cloud SIEM Sentinel and the endpoint security using Microsoft Defender for Endpoint. For example, in MDE (Microsoft Defender for Endpoint), you can use Advanced Search, a KQL-based threat hunting tool that allows you to explore up to 30 days of raw data.
So, we can summarize KQL at this moment as a language that allows you to quickly query data and process and return the results without modifying the data or metadata (read-only queries).
So, we can say that KQL is a read-only request to process data and return results. The request is made in plain text and uses a dataflow model that is designed to make it easier to create, read, and automate the syntax. Queries are organized in a hierarchy much like SQL: using databases, tables, and columns.
In the example below, I am accessing the Microsoft Sentinel logs query on portal.azure.com.
The Defender for Endpoint KQL can be accessed via the security.microsoft.com portal, on the home page, on the Hunting menu.
If you want to do a hands-on test, you can log in with any Microsoft account or create a trial and go to the lab site that was created just for you to test the queries in practice – https://aka.ms/lademo.
Below, the image shows the KQL framework in Microsoft Sentinel:
Use Cases
So, let’s work with an initial use case so you can visualize the result of a practical scenario.
In this example, I want to find all Windows logon events from 2 to 1 week ago that occurred on a computer with a name that starts with “App“.
With the search below, I’m fetching data inside a table called SecurityEvents. This table contains security events collected from Windows computers by Microsoft Defender for Cloud or through Sentinel.
This is possible in this lab because we have connected Windows machines to the Log Analytics Workspace used by Sentinel, so security events from the Windows event log are forwarded to this table.
Let’s run the command below to test:
SecurityEvent | limit 100 // Searchs for: Activity, EventID, Computer
I can also summarize by activity:
SecurityEvent | summarize by Activity // find the Event signaling login
Now, let’s gather the events between 14 and 7 days ago and filter by event ID “4624” (Event: An account was successfully registered) on the computer, starting with “App“.
SecurityEvent
| where TimeGenerated between (ago(14d)..ago(7d)) // start with the time filter
| where EventID == "4624"
| where Computer startswith "App" // case insensitive
// This is the solution, but there are so many results
Finally, let’s summarize and search by counting the number of computers:
SecurityEvent
| where TimeGenerated between (ago(14d)..ago(7d))
| where EventID == "4624"
| where Computer startswith "App"
| summarize count() by Computer
// so let’s count per computer
These examples and many others can also be learned on the website of the Sentinel Ninja Training L400. I strongly recommend it!
Summary
In this article, you learned how to take the first steps in learning KQL searches.
