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:

EmployeeId	Name	Department	Salary	City
101	Alice	IT	65000	London
102	Bob	HR	55000	Manchester
103	Charlie	IT	72000	London
104	David	Finance	68000	Birmingham

---

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

Name	Salary
Alice	65000
Charlie	72000

---

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

EmployeeId	Name
101	Alice
102	Bob

Projects Table

EmployeeId	Project
101	Portal
102	ERP

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

Operator	Purpose
where	Filter rows
project	Select columns
extend	Create new columns
summarize	Aggregate data
sort	Sort rows
top	Top records
distinct	Unique values
join	Merge tables
union	Append tables
count	Count rows
take	Return first N rows
limit	Alias for take

---

SQL vs KQL

SQL	KQL
SELECT *	TableName
WHERE	where
GROUP BY	summarize by
ORDER BY	sort by
LIMIT	take
AS	project NewName=Column
CASE	case()
JOIN	join

---

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.