Kusto Query Language

Kusto Query Language (KQL) is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQLs: databases, tables, and columns.

A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.

What is a query statement?

There are three kinds of user query statements:

• A tabular expression statement
• A let statement
• A set statement

All query statements are separated by a ; (semicolon), and only affect the query at hand.

The most common kind of query statement is a tabular expression statement, which means both its input and output consist of tables or tabular datasets. Tabular statements contain zero or more operators, each of which starts with a tabular input and returns a tabular output. Operators are sequenced by a | (pipe). Data flows, or is piped, from one operator to the next. The data is filtered or manipulated at each step and then fed into the following step.

It's like a funnel, where you start out with an entire data table. Each time the data passes through another operator, it's filtered, rearranged, or summarized. Because the piping of information from one operator to another is sequential, the query operator order is important, and can affect both results and performance. At the end of the funnel, you're left with a refined output.

Lets See the Example

StormEvents

| where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01))

| where State == "FLORIDA"  

| count

Note

KQL is case-sensitive for everything – table names, table column names, operators, functions, and so on.

Azure Monitor for setting alerts , troubleshooting some example query are given below.

KQL Query to find the Computer and Computer Environment that hosts the computer: Azure or Non-Azure
Heartbeat
| where OSType == 'Windows' or OSType == 'Linux'
| summarize arg_max(TimeGenerated, *) by SourceComputerId 
| sort by Computer
| project Computer, ComputerEnvironment
| render table

KQL Query to find the Computer OS Type,  OS Name OS Major Version, and OS Minor Version.

Heartbeat
| where OSType == 'Windows' or OSType == 'Linux'
| summarize arg_max(TimeGenerated, *) by SourceComputerId 
| sort by Computer
| project Computer, ComputerEnvironment, 
OSType, OSName, OSMajorVersion, OSMinorVersion
| render table

KQL Query to find the Computer with OS Type has only Windows and display OS Name OS Major Version and OS Minor Version.
Heartbeat
| where OSType == 'Windows'
| summarize arg_max(TimeGenerated, *) by SourceComputerId 
| sort by Computer
| project Computer, ComputerEnvironment, 
OSType, OSName, OSMajorVersion, OSMinorVersion
| render table

KQL Query to find the Computer with OS Type has only Linux and display OS Name OS Major Version and OS Minor Version.

Heartbeat
| where OSType == 'Linux'
| summarize arg_max(TimeGenerated, *) by SourceComputerId 
| sort by Computer
| project Computer, ComputerEnvironment, 
OSType, OSName, OSMajorVersion, OSMinorVersion
| render table

KQL Query to find the Computer Remote IP.
Heartbeat
| summarize arg_max(TimeGenerated, *) by SourceComputerId 
| sort by Computer
| project Computer, ComputerEnvironment, ComputerIP
| render table

KQL in other services

KQL is used by many other Microsoft services. For specific information on the use of KQL in these environments, refer to the following links:

• Log queries in Azure Monitor
• Kusto Query Language in Microsoft Sentinel
• Understanding the Azure Resource Graph query language
• Proactively hunt for threats with advanced hunting in Microsoft 365 Defender
• CMPivot queries

1. Basic Query:

   kql
   MyDataTable
   | project Column1, Column2
   | where Column1 > 10
   | order by Column2 desc
   

2. Filtering Data:

   kql
   MyDataTable
   | where Column1 > 10 and Column2 == "Value"
   

3. Aggregating Data:

   kql
   MyDataTable
   | summarize TotalCount=count(), AvgValue=avg(Column1)
   

4. Grouping Data:

   kql
   MyDataTable
   | summarize TotalCount=count() by Column2
   

5. Joining Tables:

   kql
   Join Table1 on ColumnA, Table2 on ColumnB
   | project Table1.ColumnX, Table2.ColumnY
   

6. Time Series Analysis:

   MyDataTable
   | summarize TotalCount=count() by bin(Timestamp, 1h)
   | order by Timestamp asc
   

7. Custom Functions:

   kql
   let CustomFunction = (arg1: int, arg2: string) {
       // Function logic here
   };
   MyDataTable
   | extend NewColumn = CustomFunction(Column1, Column2)
   

8. Working with JSON Data:

   kql
   MyDataTable
   | extend ParsedJson = parse_json(JsonColumn)
   | project ParsedJson.PropertyA
   

9. Time Window Functions:

   kql
   MyDataTable
   | extend Window = range start=start_date to end=end_date step 1d
   | summarize CountInWindow=count() by Window
   

10. Count Distinct Values:

    kql
    MyDataTable
    | summarize DistinctCount=dcount(Column1)
    

11. Top N Values:

    kql
    MyDataTable
    | top 10 by Column1 desc
    

12. Extract Regex Matches:

    MyDataTable
    | extend ExtractedValue = extract(@"\d{3}", 0, Column1)
    

13. Outliers Detection:

    kql
    MyDataTable
    | summarize Outliers = series_outliers(Column1)
    

14. Pivoting Data:

    kql
    MyDataTable
    | pivot Column2, sum(Column1) for Column3 in ("Value1", "Value2")
    

15. Calculate Percentiles:

    kql
    MyDataTable
    | summarize Percentile50=percentile(Column1, 50), Percentile90=percentile(Column1, 90)
    

16. Chart Visualization:

    kql
    MyDataTable
    | summarize Count=count() by Category
    | project Category, Count
    | render piechart