Kusto Query Language (KQL)

Kusto Query Language (KQL) is a fast, read-only query language developed by Microsoft for querying large volumes of structured, semi-structured, and time-series data.

KQL is a tool used to fetch and analyze data within Azure services like Log Analytics and Application Insights. It enables centralized data collection, querying, and correlation of system logs. It powers several Microsoft services including:

• Azure Data Explorer (ADX)
• Azure Monitor Logs
• Microsoft Sentinel
• Microsoft Defender XDR
• Application Insights
• Microsoft Fabric Real-Time Intelligence

Unlike SQL, KQL is designed primarily for analytics, monitoring, troubleshooting, security investigations, and log analysis.

Prerequisites

• Basic understanding of databases
• Basic SQL knowledge (helpful but not mandatory)
• Access to Azure Data Explorer or Log Analytics Workspace (optional)

KQL Query Structure

KQL follows a pipeline approach where each operator passes its output to the next operator.

TableName
| Operator1
| Operator2
| Operator3

Example:

StormEvents
| take 5

This returns the first five records.

Sample Dataset

Assume the following table:

1. View Records

Employees
| take 10

Returns first 10 records.

2. Filter Rows (where)

SQL

SELECT *
FROM Employees
WHERE Department='IT';

KQL

Employees
| where Department == "IT"

3. Select Columns (project)

Employees
| project Name, Salary

Output

4. Rename Columns

Employees
| project EmployeeName = Name,
          AnnualSalary = Salary

5. Sort Results

Ascending

Employees
| sort by Salary asc

Descending

Employees
| sort by Salary desc

6. Top Records

Highest salaries

Employees
| top 3 by Salary

7. Count Records

Employees
| count

8. Distinct Values

Employees
| distinct Department

Output

• IT
• HR
• Finance

9. Aggregation using summarize

Average Salary

Employees
| summarize AvgSalary = avg(Salary)

Maximum Salary

Employees
| summarize MaxSalary=max(Salary)

Total Salary

Employees
| summarize TotalSalary=sum(Salary)

10. Group By

Average salary by department

Employees
| summarize AvgSalary=avg(Salary)
by Department

11. Multiple Conditions

Employees
| where Department=="IT"
and Salary > 60000

12. OR Condition

Employees
| where Department=="HR"
or Department=="Finance"

13. String Search

Contains

Employees
| where Name contains "Ali"

Starts With

Employees
| where Name startswith "A"

Ends With

Employees
| where Name endswith "e"

14. Case-insensitive Search

Employees
| where Department =~ "it"

Case-sensitive

Employees
| where Department == "IT"

15. Create New Column

Employees
| extend Bonus = Salary * 0.10

16. Conditional Column

Employees
| extend Grade =
case(
Salary >=70000,"A",
Salary >=60000,"B",
"C")

17. Date Filtering

Last 7 Days

Logs
| where TimeGenerated > ago(7d)

Last 24 Hours

Logs
| where TimeGenerated > ago(24h)

18. Limit Results

Employees
| limit 5

or

Employees
| take 5

19. Join Tables

Employees Table

Projects Table

Join

Employees
| join Projects
on EmployeeId

20. Union

TableA
| union TableB

21. Parse JSON

Example JSON

{
   "user":"John",
   "age":30
}

Query

datatable(data:string)
[
'{"user":"John","age":30}'
]
| extend obj=parse_json(data)
| project obj.user,obj.age

22. Working with Dynamic Arrays

datatable(tags:dynamic)
[
dynamic(["Azure","KQL","SQL"])
]
| mv-expand tags

Output

Azure
KQL
SQL

23. Common Operators

SQL vs KQL

Real-World Examples

Find Failed Login Attempts

SigninLogs
| where ResultType != 0
| project TimeGenerated,
          UserPrincipalName,
          IPAddress,
          ResultDescription

Top Error Messages

AppTraces
| summarize Count=count()
by Message
| top 10 by Count

CPU Usage Trend

Perf
| where CounterName=="% Processor Time"
| summarize avg(CounterValue)
by bin(TimeGenerated,5m)

Requests in Last Hour

requests
| where timestamp > ago(1h)
| summarize count()

Best Practices

• Filter early using where to reduce scanned data.
• Use project to return only the required columns.
• Prefer summarize for aggregations instead of client-side processing.
• Use bin() for grouping time-series data.
• Limit results with take while developing queries.
• Use let statements to create reusable query variables.
• Apply join carefully on large datasets to improve performance.

Learning Roadmap

1. Understand tables and columns.
2. Master where filtering.
3. Learn project and extend.
4. Practice sorting and limiting results.
5. Learn summarize and aggregations.
6. Work with dates and time-series data.
7. Master joins and unions.
8. Parse JSON and dynamic data.
9. Analyse Azure Monitor and Sentinel logs.
10. Build dashboards and alerts using KQL.

KQL is essential for proactive monitoring, allowing users to identify long-running queries, errors, and performance spikes. It supports alert rules and integrates with Azure Monitor to provide insights into infrastructure health, such as database connectivity issues or application.

Its pipeline-based syntax is easy to learn and highly optimized for large-scale analytics. By mastering operators such as where, project, extend, summarize, and join, you can efficiently investigate issues, create dashboards, monitor applications, and perform advanced security analysis across Microsoft’s cloud ecosystem.