Kusto Query Language (KQL)

Kusto Query Language (KQL) is a powerful, read-only query language used to explore, analyze, and visualize large volumes of structured, semi-structured, and unstructured data in Azure services such as Log Analytics, Application Insights, and Azure Data Explorer.

⚙️ Key Features

• Optimized for fast data exploration and ad-hoc queries.
• Supports filtering, aggregation, joins, and advanced data transformations.
• Integrates with dashboards, workbooks, and alerts for real-time monitoring.
• Provides time-series analysis and pattern detection.

📊 Common KQL Operations

• Filtering: `where` clause to narrow down data.
• Projection: `project` to select specific columns.
• Aggregation: `summarize` to group and calculate metrics.
• Joining Data: `join` to combine tables.
• Sorting: `order by` to arrange results.
• Time Series Analysis: `make-series` and `bin()` for temporal aggregation.

🧩 Example Query

Logs
| where TimeGenerated > ago(1h)
| where SeverityLevel == "Error"
| summarize ErrorCount = count() by Computer
| order by ErrorCount desc
  

✅ Why Use KQL

• Quickly identify trends and anomalies in large datasets.
• Supports monitoring, diagnostics, and troubleshooting in Azure.
• Enables custom dashboards and alerting rules.
• Ideal for multi-tenant SaaS telemetry and modular infrastructure analysis.

🧠 What is KQL?

Kusto Query Language (KQL) is a read-only, data exploration language used across Azure services like:

• Azure Monitor / Log Analytics
• Microsoft Sentinel
• Azure Data Explorer
• Microsoft Defender for Cloud

It’s designed for fast, interactive analysis of large datasets — especially telemetry, logs, metrics, and time-series data — without modifying the underlying data.

🔍 Core Concepts

Concept	Description
Read-only	You can query and transform data, but not insert/update/delete.
Data-flow model	Queries are built as a sequence of operations connected by a pipe (|) — each step transforms the table before passing it on.
Schema hierarchy	Data is organized into databases → tables → columns (similar to SQL).
Case-sensitive	Table names, column names, and operators are case-sensitive.
Optimized for telemetry	Rich support for text search, parsing, time-series, aggregation, and anomaly detection.

🛠 Basic Syntax Structure

TableName
| where Timestamp > ago(1h)
| project ColumnA, ColumnB
| summarize Count = count() by ColumnA
| order by Count desc
  

How it works:

• Start with a table (TableName)
• Filter rows (where)
• Select columns (project)
• Aggregate data (summarize)
• Sort results (order by)

📌 Common Operators

Operator	Purpose	Example
where	Filter rows	where Level == "Error"
project	Select columns	project TimeGenerated, Message
summarize	Aggregate	summarize avg(DurationMs) by UserId
extend	Add calculated columns	extend LoadTimeSec = DurationMs / 1000
join	Combine tables	join kind=inner
order by	Sort results	order by Count desc
parse	Extract values from text	parse Message with "User:" UserId " logged in"

⚡ Example in Azure Monitor

Scenario: Find the top 5 IP addresses with the most failed logins in the last 24 hours.

SigninLogs
| where ResultType != 0
| where TimeGenerated > ago(24h)
| summarize Failures = count() by IPAddress
| top 5 by Failures desc
  

🎯 Best Practices for Writing KQL

• Filter early → Use where as soon as possible to reduce dataset size.
• Project only needed columns → Avoid pulling unnecessary data.
• Use summarize wisely → Aggregate before joins to reduce cost.
• Parameterize queries in Workbooks for reusability.
• Test in small time windows before running large queries.

🚀 Why You’ll Love It for Your Work

• Tag data with TenantId to filter dashboards per tenant.
• Build modular KQL snippets for reuse in Workbooks, Alerts, and APIs.
• Integrate KQL queries into Angular dashboards via the Azure Monitor REST API for live telemetry.

KQL Cheat Sheet - Azure Monitor, Sentinel, Application Insights

📜 KQL Cheat Sheet

20+ ready-to-use query patterns for Azure Monitor, Microsoft Sentinel, and Application Insights.

1️⃣ Azure Monitor & Log Analytics Queries

1. Heartbeat
   | summarize Count = count() by Computer

   List all monitored computers with heartbeat count.

2. Perf
   | where ObjectName == "Processor" and CounterName == "% Processor Time"
   | summarize AvgCPU = avg(CounterValue) by bin(TimeGenerated, 5m), Computer

   Average CPU usage per computer in 5-min bins.

3. AzureActivity
   | where ActivityStatus == "Failed"
   | project TimeGenerated, Caller, OperationName, ActivityStatus

   Show failed Azure operations.

4. SecurityEvent
   | where EventID == 4625
   | summarize FailedLogins = count() by Account, bin(TimeGenerated, 1h)

   Failed login attempts per account per hour.

5. InsightsMetrics
   | where Name == "Requests"
   | summarize TotalRequests = sum(Val) by bin(TimeGenerated, 1h), Resource

   Total requests per resource per hour.

2️⃣ Microsoft Sentinel Queries

6. SigninLogs
   | where ResultType != 0
   | summarize Failures = count() by UserPrincipalName, IPAddress

   Failed sign-ins by user and IP.

7. SecurityAlert
   | summarize Alerts = count() by AlertName, Severity

   Count of alerts by name and severity.

8. SecurityIncident
   | where Status == "Active"
   | project IncidentNumber, Title, Severity, Owner

   List active incidents with details.

9. OfficeActivity
   | where Operation == "FileAccessed"
   | summarize AccessCount = count() by UserId, Site_Url

   File access count by user and site.

10. DeviceNetworkEvents
    | where RemoteUrl contains "malicious.com"
    | project Timestamp, DeviceName, RemoteUrl

    Detect connections to malicious domains.

3️⃣ Application Insights Queries

11. requests
    | summarize AvgDuration = avg(duration) by bin(timestamp, 1h)

    Average request duration per hour.

12. requests
    | where success == "False"
    | summarize Failures = count() by name

    Failed requests by operation name.

13. dependencies
    | summarize AvgDuration = avg(duration) by target

    Average dependency call duration by target.

14. traces
    | where message contains "Exception"
    | project timestamp, message, severityLevel

    List exception traces with severity.

15. pageViews
    | summarize Views = count() by name

    Page view count by page name.

4️⃣ Cross-Service / Advanced Patterns

16. union requests, dependencies
    | summarize TotalCalls = count() by bin(timestamp, 1h)

    Combine requests and dependencies for total calls per hour.

17. SigninLogs
    | join kind=inner (SecurityAlert) on $left.UserPrincipalName == $right.CompromisedEntity
    | project TimeGenerated, UserPrincipalName, AlertName

    Correlate sign-ins with security alerts.

18. Perf
    | where CounterName == "% Processor Time"
    | summarize AvgCPU = avg(CounterValue) by Computer
    | order by AvgCPU desc

    Top CPU-consuming machines.

19. SecurityEvent
    | where EventID in (4720, 4722, 4723)
    | summarize Count = count() by EventID, Account

    Track account creation, enable, and password change events.

20. requests
    | extend DurationSec = todouble(duration)/1000
    | summarize P95 = percentile(DurationSec, 95) by bin(timestamp, 1h)

    95th percentile request duration per hour.

21. Heartbeat
    | summarize LastSeen = max(TimeGenerated) by Computer
    | where LastSeen < ago(1h)

    Machines not reporting heartbeat in the last hour.

💡 Tips for Using KQL Efficiently

• Filter early with where to reduce dataset size.
• Use project to select only needed columns.
• Aggregate with summarize before joins for performance.
• Parameterize queries in Workbooks for reusability.
• Test with small time windows before scaling up.