Kusto KQL - Part 1 - Overview
- brencronin
- 17 hours ago
- 6 min read
Understanding KQL in the Microsoft Data Ecosystem
In Microsoft's data ecosystem, KQL stands for Kusto Query Language. The name "Kusto" is a playful nod to Jacques Cousteau, the renowned deep-sea explorer. Just as Cousteau explored the depths of the oceans, KQL enables users to explore the depths of their data with power and precision.
It’s worth noting that KQL is sometimes confused with another similarly named language: Kibana Query Language, used for querying data in Elastic Stack (ELK) environments. While they share the same acronym, these two query languages are entirely different in syntax, structure, and use cases.
KQL is both intuitive and powerful, offering multiple ways to construct queries that return the same, or similar, results. However, this flexibility can also lead to frustration, especially when trying to adapt published KQL examples that don’t work as expected in your own environment. This is often due to differences in data schemas, such as table names or field names, which vary between implementations.
KQL - Diving In - Query Overview
The example below follow much of what is outlined at the beginning of the Rod Trent book 'Must learn KQL' which can be found here: MustLearnKQL/Book_Version/MustLearnKQL_Book.pdf at main · rod-trent/MustLearnKQL · GitHub
Let’s begin by examining the basic structure of a Kusto Query Language (KQL) query where tabular statements contain zero or more operators, each of which starts with a tabular input and returns a tabular output.
Operators are separated by a pipe
Data flows are piped from one operator to the next, whereas each piped step acts as a type of filter on the data:
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| summarize count() by Account
| order by Account asc
| project Account, SuccessfulLogons = count_This query follows a common KQL pattern:
Table Selection
The query begins by specifying the table to query, in this case, SecurityEvent, which typically contains Windows security event logs.
Filtering with where
The first filter limits results to the last hour:
| where TimeGenerated > ago(1h)This is a best practice in query design, as it narrows the scope early and improves performance. The second filter targets a specific event. In simple terms "EventID" would be a column in the SecurityEvent table and a match condition for "4624" would search the entire table for any log entries that match 4624 within the EventID column:
| where EventID == "4624"Event ID 4624 represents a successful logon in Windows event logs.
Summarizing and Sorting
Next, the query summarizes the number of logons by user account:
| summarize count() by AccountIt then sorts the results alphabetically:
| order by Account ascProjecting Output Columns
Finally, the project operator formats the output:
| project Account, SuccessfulLogons = count_Although this example is widely found online, it may not work in your environment out of the box. That’s because not all Microsoft Defender or Sentinel deployments use the SecurityEvent table. In environments where Microsoft Defender for Endpoint is deployed, Windows telemetry is often stored in different tables, such as DeviceLogonEvents. In this case, the equivalent query would look like:
DeviceLogonEvents
| where TimeGenerated > ago(1h)
| where ActionType == "LogonSuccess"
| summarize count() by Account
| order by Account asc
| project Account, SuccessfulLogons = count_Here:
The DeviceLogonEvents table stores logon-related telemetry.
ActionType == "LogonSuccess" filters for successful logon attempts.
Another example of searching logon related data in KQL. this would be looking for ActionType == "Logon Failed" (e.g., equivalent of Windows EventID == 4625) and summarizing the data by failure count per DeviceName.
DeviceLogonEvents
| where DeviceName in ("system1", "system2") and ActionType == "LogonFailed"
| summarize Logonfailure=count() by DeviceNameKQL Rules
KQL Rule #1: Know Your Data Source
Before writing any KQL query, always identify the correct table that contains the data you need. The table names, field names, and event types can vary depending on your data connectors, ingestion sources, and Microsoft security solutions in use.
KQL - Diving In - Data Location
The first step in working with KQL is to list all available tables in your environment. You are also asking the question:
Does the data even exist in the environment?
Ideally, your tables will have descriptive names that help guide your investigation. You can view table names directly in the Microsoft Sentinel GUI under the Logs section. Alternatively, you can run the following KQL query to programmatically list all tables that contain data:
search *
| summarize count() by $table
| project $tableThis query scans across all ingested data, summarizes the event count by table, and returns a list of tables currently populated with data. It’s a great way to get a quick overview of your data landscape.
If you are using Microsoft Defender products:
Near real-time detections are supported for the following tables:
AlertEvidence
CloudAppEvents
DeviceEvents
DeviceFileCertificateInfo
DeviceFileEvents
DeviceImageLoadEvents
DeviceLogonEvents
DeviceNetworkEvents
DeviceNetworkInfo
DeviceInfo
DeviceProcessEvents
DeviceRegistryEvents
EmailAttachmentInfo
EmailEvents (except LatestDeliveryLocation and LatestDeliveryAction columns)
EmailPostDeliveryEvents
EmailUrlInfo
IdentityDirectoryEvents
IdentityLogonEvents
IdentityQueryEvents
UrlClickEvents
Other tables: https://learn.microsoft.com/en-us/azure/azure-monitor/reference/tables/tables-category
The Microsoft Defender tables are specifically the tables: DeviceEvents, DeviceFileCertificatelnfo, DeviceFileEvents, DevicelmageLoadEvents, Devicelnfo, DeviceLogonEvents, DeviceNetworkEvents, DeviceNetworklnfo, DeviceProcessEvents, and DeviceRegistryEvents.
Another useful note that in Sentinel logging systems the _cl in the table name typically stands for custom logs.
DataTables - Columns - getschema
If you are unsure of the columns in a table for where filters you can use the getschema filter which will display all the columns within that table with the ColumNames as well as DataType and ColumnType. Azure data tables can also be found here: https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/resource-logs-schema
Tablename
| getschemaor
search *
| distinct $tableExploring Table Columns and Sampling Data with limit and take
Sometimes, you may want to quickly examine the columns in a table and preview a sample of the data. The simplest way to do this is by querying the table directly:
TableNameThis returns all data from the table, limited to the time range defined in your query scope (e.g., the last 24 hours, if that's your default).
Sampling Data with limit or take
To avoid pulling too much data at once, especially in large tables, you can use the limit or take operator to restrict the number of rows returned:
TableName
| limit 10or
TableName
| take 10Both commands do the same thing: they return 10 rows from the table. However, unless you explicitly sort the data, the rows returned will be random and not necessarily the same each time the query runs. These operators are designed for quick sampling, not deterministic output.
Using the search Operator in KQL
The search operator in Kusto Query Language (KQL) is a powerful tool for performing full-text searches across multiple tables or columns. It's particularly useful when you're not sure where certain data resides, or when you're doing broad threat hunting across a workspace.
Basic Usage - KQL Search
To search across all tables in your environment for a specific string:
search "string"This will scan across all columns and all tables that are indexed in the current scope.
Narrowing the KQL Search to Specific Tables
You can limit the search scope to specific tables using the in clause:
search in (DeviceEvents, OfficeActivity) "string"You can also use wildcards to match table name patterns:
search in (Device*) "string"KQL Searching Within a Specific Table
Once you've identified the relevant table, it's more efficient to use a targeted search:
Tablename
| search "string"or
search in (OfficeActivity) "string"If you're still unsure of the table but want to identify where the string appears:
search "String"
| distinct $tableThis will return a list of tables where the string was found.
Advanced String Matching
KQL search supports wildcards within strings:
search "mim*z" This would match values like mimikatz, mim3z, or mimxyz.
Column-Specific Searches
To search for a string within a specific column:
search FileName:"search string"You can also combine multiple conditions with syntax, search <ColumnName>:"<SearchPredicate>" (and|or) (not) <ColumnName>:"<SearchPredicate>"
search FileName:"mimikatz" and InitiatingProcessFileName:"rundll32"Negation is supported using not, which must be enclosed in parentheses:
search FileName:"powershell" and not (RemoteIP:"10.10.10.5" Important Search Behavior and Performance Notes
Minimum Term Length: Kusto indexes only alphanumeric terms with 4 or more characters. Terms shorter than this are not indexed and may result in slower queries.
Tokenization: Non-alphanumeric characters (like _, ., or -) are treated as delimiters and break strings into searchable terms.
Case Insensitivity: By default, search is case-insensitive.
Efficiency Tip: Use search to explore broadly, but switch to where clauses for performance-tuned queries once you've identified your target table and fields.
Learn More about KQL Search
Common tasks with KQL for Microsoft Sentinel
Must Learn KQL
For more details on the search operator, visit the official documentation: KQL search operator – Microsoft Learn
Comments