Part 1: Introduction to KQL and Azure Log Analytics

My Journey into KQL

When I first started working with Azure observability, I was overwhelmed by the sheer volume of logs and metrics. Traditional grep and text-based log searching felt limiting when dealing with structured telemetry data from cloud resources. That's when I discovered KQL (Kusto Query Language), and it fundamentally changed how I approach observability.

KQL isn't just another query language—it's a powerful, expressive language designed specifically for log analytics and time-series data. In this series, I'll share what I've learned building production observability solutions with KQL and Azure Log Analytics.

What is KQL?

Kusto Query Language (KQL) is a read-only query language developed by Microsoft for analyzing large datasets. It's the query language used across multiple Azure services:

• Azure Log Analytics: Centralized log collection and analysis

• Azure Monitor: Infrastructure and application monitoring

• Azure Sentinel: Security information and event management (SIEM)

• Application Insights: Application performance monitoring

• Azure Data Explorer: Big data analytics platform

Why KQL Matters

From my experience, KQL excels at:

1. Time-series analysis: Built-in operators for temporal data

2. Performance: Optimized for large-scale log data

3. Expressiveness: Complex queries in readable syntax

4. Visualization: Native integration with Azure dashboards

5. Real-time insights: Fast query execution on live data

Understanding Azure Log Analytics Workspace

Before diving into queries, let me share how Azure Log Analytics workspace works based on my projects.

What is a Log Analytics Workspace?

A Log Analytics workspace is a centralized repository where Azure stores log and telemetry data from your resources. Think of it as a database specifically designed for observability data.

Key concepts I learned:

Azure Resources → Diagnostic Settings → Log Analytics Workspace → KQL Queries → Insights

Workspace Components

From my experience, here's what matters:

1. Tables: Pre-defined schemas for different log types

   • AzureActivity: Azure Resource Manager operations

   • AzureDiagnostics: Resource diagnostic logs

   • Heartbeat: VM agent health data

   • Perf: Performance counters

   • Syslog: Linux system logs

   • Event: Windows event logs

   • Custom tables for application logs

2. Data Retention: Configure how long logs are stored (30-730 days)

3. Access Control: RBAC for query permissions

4. Ingestion: How data flows into the workspace

Setting Up Your First Workspace

Here's how I typically set up a Log Analytics workspace for my projects:

1. Create the workspace:

# Using Azure CLI
az monitor log-analytics workspace create \
  --resource-group my-rg \
  --workspace-name my-workspace \
  --location eastus \
  --retention-time 30

2. Enable diagnostic settings on resources:

For example, to collect logs from an AKS cluster:

# Enable AKS logs
az monitor diagnostic-settings create \
  --resource /subscriptions/{sub-id}/resourceGroups/{rg}/providers/Microsoft.ContainerService/managedClusters/{aks-name} \
  --name aks-diagnostics \
  --workspace /subscriptions/{sub-id}/resourceGroups/{rg}/providers/Microsoft.OperationalInsights/workspaces/my-workspace \
  --logs '[{"category":"kube-apiserver","enabled":true},{"category":"kube-controller-manager","enabled":true}]' \
  --metrics '[{"category":"AllMetrics","enabled":true}]'

3. Verify data ingestion:

After configuration, data typically appears within 5-10 minutes. I use this simple query to check:

// Check recent data in workspace
search *
| where TimeGenerated > ago(1h)
| summarize count() by Type
| order by count_ desc

Your First KQL Query

Let me walk you through running your first query. This is how I started learning KQL.

Accessing the Query Editor

1. Navigate to your Log Analytics workspace in Azure Portal

2. Click "Logs" in the left menu

3. You'll see the query editor with a schema explorer on the left

Basic Query Structure

KQL queries follow a tabular flow pattern:

TableName
| operator1
| operator2
| operator3

Each line processes the data from the previous step, similar to Unix pipes.

Example: Querying Azure Activity Logs

Here's a query I use to monitor resource creation in my subscriptions:

AzureActivity
| where TimeGenerated > ago(24h)
| where OperationNameValue contains "Microsoft.Resources/subscriptions/resourceGroups/write"
| project TimeGenerated, Caller, ResourceGroup, OperationNameValue
| order by TimeGenerated desc

Breaking it down:

1. AzureActivity - Start with the Activity log table

2. where TimeGenerated > ago(24h) - Filter to last 24 hours

3. where OperationNameValue contains "write" - Find write operations

4. project - Select specific columns to display

5. order by - Sort results by time

Understanding Query Results

When you run this query, you'll see:

• TimeGenerated: When the action occurred

• Caller: Who performed the action

• ResourceGroup: Which resource group was affected

• OperationNameValue: What operation was performed

KQL vs SQL: What I Learned

Coming from a SQL background, I had to adjust my thinking. Here's what I discovered:

Similarities:

• Both are declarative query languages

• Similar filtering concepts (WHERE)

• Aggregation functions (COUNT, SUM, AVG)

• Joining capabilities

Key Differences:

Aspect	SQL	KQL
Syntax	SELECT-FROM-WHERE	Table | pipe operators
Primary use	Relational databases	Log analytics, time-series
Query flow	Bottom-up	Left-to-right, top-to-bottom
Time operators	Limited built-ins	Rich temporal functions
Optimization	Query planner	Column-store optimized

SQL approach:

SELECT TimeGenerated, Level, Message
FROM AppTraces
WHERE TimeGenerated >= DATEADD(hour, -1, GETDATE())
  AND Level = 'Error'
ORDER BY TimeGenerated DESC;

KQL approach:

AppTraces
| where TimeGenerated > ago(1h)
| where Level == "Error"
| project TimeGenerated, Level, Message
| order by TimeGenerated desc

Common Tables I Use Daily

From my production work, these are the tables I query most frequently:

1. AzureActivity - Resource operations

AzureActivity
| where TimeGenerated > ago(1d)
| summarize count() by OperationNameValue
| top 10 by count_

2. Heartbeat - VM/agent health

Heartbeat
| where TimeGenerated > ago(5m)
| summarize max(TimeGenerated) by Computer
| where max_TimeGenerated < ago(3m)
// VMs that haven't sent heartbeat in 3+ minutes

3. Perf - Performance metrics

Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5m)

4. ContainerLog - Kubernetes logs

ContainerLog
| where TimeGenerated > ago(1h)
| where LogEntry contains "error"
| project TimeGenerated, Computer, ContainerID, LogEntry
| order by TimeGenerated desc

5. AzureDiagnostics - Resource diagnostic logs

AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Category == "ApplicationGatewayFirewallLog"
| where action_s == "Blocked"
| summarize count() by clientIp_s
| top 10 by count_

Understanding Time in KQL

Time is critical in observability queries. Here's what I use most:

Time Functions:

// Last hour
| where TimeGenerated > ago(1h)

// Last 7 days
| where TimeGenerated > ago(7d)

// Specific date range
| where TimeGenerated between (datetime(2024-01-01) .. datetime(2024-01-31))

// Today
| where TimeGenerated > startofday(now())

// This week
| where TimeGenerated > startofweek(now())

Time Bucketing:

// Group into 5-minute intervals
| summarize count() by bin(TimeGenerated, 5m)

// Group into hourly buckets
| summarize avg(CounterValue) by bin(TimeGenerated, 1h)

Practical Tips from My Experience

1. Use Time Filters Early

Always filter by time first to reduce data scanned:

// Good - Filter time first
AzureActivity
| where TimeGenerated > ago(1h)
| where ResourceGroup == "my-rg"

// Less optimal - Time filter later
AzureActivity
| where ResourceGroup == "my-rg"
| where TimeGenerated > ago(1h)

2. Limit Result Sets

Use take or limit during development:

AzureActivity
| take 100  // Get first 100 rows quickly

3. Use Schema Explorer

The left panel in the query editor shows available tables and columns. Click to insert them into your query.

4. Save Useful Queries

I maintain a collection of frequently-used queries saved in the workspace.

5. Check Query Performance

Look at the query statistics shown after execution:

• Records scanned

• Execution time

• Data processed

Setting Up Your Learning Environment

Here's how I recommend setting up for this series:

1. Create a Test Workspace

az monitor log-analytics workspace create \
  --resource-group kql-learning \
  --workspace-name kql-101-workspace \
  --location eastus

2. Enable Sample Data

Azure provides sample data for learning. Enable it in your workspace settings, or generate your own logs from:

• A simple VM with diagnostics enabled

• An App Service with Application Insights

• Azure Activity logs (available by default)

3. Use Query Shortcuts

In the query editor:

• Ctrl/Cmd + Enter: Run query

• Ctrl/Cmd + Space: Intellisense

• Ctrl/Cmd + K, Ctrl/Cmd + C: Comment

• Ctrl/Cmd + K, Ctrl/Cmd + U: Uncomment

What's Next

Now that you understand the basics of KQL and Azure Log Analytics, we'll dive deeper into:

• Part 2: KQL syntax fundamentals and core operators

• Part 3: Advanced operators and functions

• Part 4: Querying specific Azure resources

• Part 5: Building observability dashboards

• Part 6: Query optimization and best practices

• Part 7: Production patterns and real-world use cases

Practice Exercise

Before moving to Part 2, try these queries in your workspace:

1. Find all activities in the last hour:

AzureActivity
| where TimeGenerated > ago(1h)
| take 10

1. Count events by type:

search *
| where TimeGenerated > ago(24h)
| summarize count() by Type

1. Find the most active resources:

AzureActivity
| where TimeGenerated > ago(7d)
| summarize count() by Resource
| top 10 by count_

Key Takeaways

• KQL is designed for log analytics and time-series data

• Azure Log Analytics workspace is your centralized log repository

• Queries flow left-to-right, top-to-bottom using pipe operators

• Always filter by time early in your queries

• Understanding table schemas is crucial for effective queries

In Part 2, we'll explore KQL syntax fundamentals and master the core operators that form the foundation of every query I write.