Kusto Query Language primer for IT administrators

Why should administrators learn Kusto Query Language?

Compared to other security information and event management query languages, Kusto Query Language can retrieve the same results but much quicker. For IT and security administrators, it's important to develop a proficiency with the query language to be more efficient and more successful with efforts to find specific information.

Kusto Query Language gives you a way to query across the different data and tables in the Microsoft cloud for analysis or to do more advanced work, such as building automation routines to send alerts based on specific KQL query results.

You should want to learn to use Kusto Query Language if you work with Microsoft cloud products, including Azure Logic Apps, Microsoft Sentinel, Azure Log Analytics, Microsoft Defender, Azure AD Identity Protection and Microsoft 365.

How is Kusto Query Language different from PowerShell?

PowerShell is a full-fledged, cross-platform programming and scripting language, whereas Kusto Query Language is a query language for large data sets. While PowerShell can also query data, it is generally tied to the type of data or hosting application and may require additional modules to work with specific data types.

You can find Kusto Query Language embedded within specific applications, which limits its use to those products. In contrast, PowerShell often queries in any application with the associated modules or endpoints.

PowerShell can work with KQL queries to enhance usage. There are two ways to execute KQL queries within PowerShell: with the Azure Data Explorer .NET client libraries directly with PowerShell or with standard Azure PowerShell commands by executing the required query within PowerShell.

What are some KQL query tools?

There are multiple tools available that support KQL queries. Each one is slightly different in how it connects to supported data, with some supporting imported or user-generated databases and others specifically targeted at log and security information.

The most common tools are the following:

• Azure Data Explorer
• Kusto.CLI
• Azure Resource Graph Explorer
• Kusto.Explorer

Tools such as Azure Data Explorer and Azure Resource Graph Explorer work within Azure service information logs or databases within an Azure Data Explorer cluster.

To query large data sets stored within a data cluster, use Azure Data Explorer, Azure.CLI and Kusto.Explorer.

For queries against Azure resources, Azure or Microsoft 365 security information, use Azure Resource Graph Explorer or the Kusto Query Language tooling built into the application.

What are the basics of Kusto Query Language?

A KQL query consists of a sequence of delimited statements. There are three types of query statements:

1. tabular expression statements
2. let statements
3. set statements

KQL query statements work like a funnel: You start with a large data set and pass it through multiple operators until it is filtered, summarized or rearranged as required. All query types must use a semicolon as the separator between statements and a pipe to flow data to the following statement. The most common statement is the tabular expression, in which both the input and output are tables or a tabular data set.

The easiest way to understand KQL queries is to convert a SQL statement. A KQL example follows a simple example of the SQL statement:

# SQL Statement
SELECT * FROM Sales WHERE Manager = 'James Brown'
# KQL Statement
Sales
| where Manager == 'James Brown'

A KQL query contains the database table, pipe commands to separate filters and results. A query can use multiple filters to query earlier results further until you identify what you need. KQL supports several types of filtering, from the essential WHERE clause to UNION, SEARCH, RANGE, PRINT and many others.

The WHERE clause is the most common way to filter data; however, to help build these filters, use the SEARCH command.

For this article, we use the storm events data provided by Microsoft available at this link.

To filter using specific states, run a SEARCH command to check they exist:

search "alaska"
search "michigan"
search "california"

You can combine multiple values with AND and OR operators:

search "alaska" and ("michigan" or "california")

The SEARCH command works with specific columns to filter the data:

search State: "alaska" and EventType: "winter storm"
search State: "alaska" or EventType: "winter storm"

The queries above return different results: The first returns a single value, and the second returns 3,270 results. Each statement can also use the pipe operator to pass the previous results directly. The results change for this query and return a single result. The pipe commands function as OR operators:

StormEvents
| search State:"alaska"
| search EventType: "winter storm"

All statements can also use standard functions present in many programming and scripting languages. For example, when working with string values, you can use the following:

• ==
• has
• contains
• startswith
• endswith
• matches regex

When working with numbers and dates, all common comparison operators work:

• ==
• !=
• <
• >
• <=
• >=

You can also use empty predicates, such as isempty(), notempty(), isnull() and notnull().

Other functions in KQL help summarize column data, such as counting values, searching within values, combining values and rendering charts:

StormEvents
| where State in ("TEXAS", "NEBRASKA", "HAWAII")
| where Source in ("Newspaper", "Emergency Manager", "Amateur Radio", "Storm Chaser")
| summarize count() by Source, State

Lastly, KQL also joins specific values to create a unique data set based on specific column values:

StormEvents
| where EventType == "Heavy Snow"
| join (
     StormEvents
     | where EventType == "Hail"
) on State
| distinct State

The RENDER operator determines how you want the data returned.

How do you use the KQL tools to work with data?

With the data filtered and queried, you can easily export it into the desired format depending on your application or scripting language. Azure Data Explorer provides a simple Export to CSV option within the UI.

The Kusto.Explorer application offers an assortment of export options, such as CSV, JSON, text and Excel.

Kusto.CLI exports results with the client-side command #save with a defined format and location:

Kusto.Cli.exe "https://help.kusto.windows.net/Samples;Fed=true" -execute:"#save C:\Exports\StormEvents.log" -execute:"StormEvents | search EventType: 'winter storm'"

Azure Resource Graph Explorer connects directly to your Azure tenant and subscription. It also provides export capabilities when executing a query.

What are some KQL query examples?

If you spend a lot of time working directly within Azure or Microsoft 365, you notice that nearly all the querying mechanisms now use Kusto Query Language.

Security products, such as Microsoft Sentinel, Azure Log Analytics and the Microsoft 365 Defender suites, rely on KQL for querying.

To start using Kusto Query Language requires understanding its workflow: Select the data source, make a filter and export the results.

For example, if you want to query all user logins for a specific time, Azure Log Analytics connects to Azure AD, which lets you use KQL to select the SigninLogs table. Next, define your filters by date and time, and then determine the output, which would be a table. The final query would resemble the following:

SigninLogs
| where TimeGenerated between (datetime(2022-08-18) .. datetime(2022-09-12))
| summarize Logins=count() by UserPrincipalName
| order by UserPrincipalName asc

Using the same example to refine the filter, you could change the query as shown below:

SigninLogs
| where TimeGenerated between (datetime(2022-08-18) .. datetime(2022-09-12))
| where LocationDetails contains "Virginia"
| where AuthenticationRequirement =~ 'singleFactorAuthentication'
| where UserDisplayName =~ "Liam Cleary"
| where AppDisplayName =~ "Azure Portal"
| project UserId, UserPrincipalName, UserType, Location

Each layer filters the results until you get the desired record or records.

You can access the same data stored within other services, such as Azure AD. The query structure is the same when working with security information within Microsoft Sentinel. For example, the following query reviews all successful user logins over the past three hours from Virginia:

SecurityEvent
| where TimeGenerated > ago (3h)
| where LocationInformation has ("Virginia")
| where EventID == 4624
| summarize count() by Account
| order by Account asc

To delve deeper and inspect running processes over the past 10 days, join multiple commands using the LET operator to define variables and create a chart from various queries:

let Processes = SecurityEvent
| where TimeGenerated > ago(10d)
| where EventID == "4688";
let TopProcesses = Processes
| summarize count() by Process
| top 10 by count_;
Processes
| where Process in (TopProcesses)
| summarize count() by bin (TimeGenerated, 3h), Process
| render timechart

Why you should learn to use Kusto Query Language

Kusto Query Language is quickly becoming the de facto standard for querying logs and analytics within both Azure and Microsoft 365. In the beginning, the queries look complex, but they are quite simple to create and provide excellent capabilities.

To learn more about Kusto Query Language, visit the Microsoft documentation for Azure Data Explorer, and review the section on KQL at the following link.