Part 2: KQL Syntax Fundamentals

Building on the Basics

After learning the fundamentals in Part 1, I found that mastering core KQL syntax operators transformed my ability to extract meaningful insights from Azure logs. In this part, I'll share the operators I use daily and how I've learned to combine them effectively.

Core Tabular Operators

KQL queries are built using tabular operators that transform data step by step. Let me walk you through each one based on my experience.

1. where - Filtering Rows

The where operator is the workhorse of KQL queries. I use it in nearly every query to filter data.

Basic filtering:

AzureActivity
| where TimeGenerated > ago(24h)
| where OperationNameValue contains "write"
| where Level == "Informational"

Comparison operators I use:

// Equality
| where Level == "Error"
| where Level != "Informational"

// Numeric comparisons
| where CounterValue > 80
| where CounterValue >= 90
| where DurationMs between (100 .. 1000)

// String operations
| where Message contains "error"       // Case-insensitive substring
| where Message !contains "warning"
| where Message startswith "ERROR:"
| where Message endswith "failed"
| where Message matches regex "\\d{3}-\\d{3}-\\d{4}"  // Regex pattern

// Case-sensitive versions
| where Message contains_cs "Error"    // Case-sensitive
| where Message has "error"            // Faster, word boundary matching
| where Message has_cs "Error"

Logical operators:

// AND conditions
| where Level == "Error" and TimeGenerated > ago(1h)

// OR conditions
| where Level == "Error" or Level == "Critical"
| where Level in ("Error", "Critical", "Warning")

// NOT conditions
| where Level !in ("Informational", "Verbose")

// Complex conditions
| where (Level == "Error" and Source == "Application") 
    or (Level == "Critical" and Source == "System")

Null handling:

// Check for null values
| where isempty(ErrorMessage)
| where isnotempty(ErrorMessage)
| where isnull(ErrorCode)
| where isnotnull(ErrorCode)

2. project - Selecting and Computing Columns

The project operator selects which columns to include and can create new calculated columns.

Basic column selection:

AzureActivity
| where TimeGenerated > ago(1h)
| project TimeGenerated, Caller, OperationNameValue, ResourceGroup

Creating calculated columns:

Perf  
| where CounterName == "% Processor Time"
| project 
    TimeGenerated,
    Computer,
    CpuPercent = CounterValue,
    CpuCategory = case(
        CounterValue < 50, "Low",
        CounterValue < 80, "Medium", 
        "High"
    ),
    IsHighCpu = CounterValue > 80

String manipulation:

AzureActivity
| project 
    TimeTaken,
    OperationShort = substring(OperationNameValue, 0, 50),
    CallerUpper = toupper(Caller),
    CallerLower = tolower(Caller),
    ResourceType = split(ResourceId, "/")[6]  // Extract from path

DateTime operations:

Heartbeat
| project 
    TimeGenerated,
    Computer,
    Date = format_datetime(TimeGenerated, "yyyy-MM-dd"),
    Hour = datetime_part("hour", TimeGenerated),
    DayOfWeek = dayofweek(TimeGenerated),
    MinutesSinceEvent = datetime_diff("minute", now(), TimeGenerated)

3. extend - Adding Columns Without Removing Others

Unlike project, extend adds new columns while keeping all existing ones. I use this when I need to add calculations but want to preserve all original data.

Perf
| where CounterName == "% Processor Time"
| extend 
    CpuCategory = case(
        CounterValue < 50, "Low",
        CounterValue < 80, "Medium",
        "High"
    ),
    IsHighCpu = CounterValue > 80,
    TimeHour = datetime_part("hour", TimeGenerated)
// All original columns are still present

Chaining extend operations:

AzureActivity
| extend 
    Hour = datetime_part("hour", TimeGenerated),
    Date = format_datetime(TimeGenerated, "yyyy-MM-dd")
| extend 
    TimeOfDay = case(
        Hour between (6 .. 12), "Morning",
        Hour between (12 .. 18), "Afternoon",
        Hour between (18 .. 22), "Evening",
        "Night"
    )

4. summarize - Aggregating Data

The summarize operator is where KQL really shines. I use it for aggregations, grouping, and statistical analysis.

Basic aggregations:

// Count total rows
AzureActivity
| where TimeGenerated > ago(24h)
| summarize count()

// Count by category
AzureActivity  
| where TimeGenerated > ago(24h)
| summarize count() by Level

// Multiple aggregations
Perf
| where CounterName == "% Processor Time"
| summarize 
    AvgCpu = avg(CounterValue),
    MaxCpu = max(CounterValue),
    MinCpu = min(CounterValue),
    StdDevCpu = stdev(CounterValue)
    by Computer

Time-based aggregations:

// Group into 5-minute intervals
Perf
| where TimeGenerated > ago(1h)
| where CounterName == "% Processor Time"  
| summarize avg(CounterValue) 
    by Computer, bin(TimeGenerated, 5m)
| order by TimeGenerated asc

// Hourly counts
AzureActivity
| where TimeGenerated > ago(7d)
| summarize EventCount = count() 
    by bin(TimeGenerated, 1h), Level

Advanced aggregations I use:

// Count distinct
AzureActivity
| summarize 
    TotalEvents = count(),
    DistinctCallers = dcount(Caller),
    DistinctResources = dcount(Resource)
    by ResourceGroup

// Percentiles for performance analysis
Perf  
| where CounterName == "% Processor Time"
| summarize 
    P50 = percentile(CounterValue, 50),
    P95 = percentile(CounterValue, 95),
    P99 = percentile(CounterValue, 99)
    by Computer

// Collect values into arrays
AzureActivity
| where TimeGenerated > ago(1h)
| summarize 
    OperationList = make_set(OperationNameValue),
    CallerList = make_list(Caller)
    by ResourceGroup

5. order (sort) - Sorting Results

Both order and sort do the same thing. I prefer order but both work:

// Order by time descending (most recent first)
AzureActivity
| where TimeGenerated > ago(1h)
| order by TimeGenerated desc

// Multiple column sorting
Perf
| where TimeGenerated > ago(1h)
| order by Computer asc, TimeGenerated desc

// Order by calculated values
AzureActivity
| summarize EventCount = count() by Resource
| order by EventCount desc

6. take (limit) - Limiting Results

Use take or limit to control result set size. Critical during query development:

// Get first 100 rows
AzureActivity
| take 100

// After filtering and sorting
Perf
| where CounterName == "% Processor Time"
| where CounterValue > 90
| order by TimeGenerated desc
| take 20

7. top - Getting Top N Results

top combines ordering and limiting:

// Top 10 computers by CPU usage
Perf
| where CounterName == "% Processor Time"
| summarize AvgCpu = avg(CounterValue) by Computer
| top 10 by AvgCpu desc

// Bottom 5 (least active resources)
AzureActivity
| summarize EventCount = count() by Resource  
| top 5 by EventCount asc

8. distinct - Unique Values

Get unique values from a column:

// Find all unique operation types
AzureActivity
| where TimeGenerated > ago(7d)
| distinct OperationNameValue

// Unique combinations
Perf
| where TimeGenerated > ago(1h)
| distinct Computer, ObjectName, CounterName

9. sample - Random Sampling

When debugging large datasets, I use sample:

// Get 100 random rows
AzureActivity
| sample 100

// Sample for performance testing
Perf
| sample-distinct 1000 of Computer

Data Types in KQL

Understanding data types helps write efficient queries. Here's what I've learned:

Common Data Types:

// String
| extend Name = "Application"
| extend Message = strcat("Error in ", Name)

// Long (64-bit integer)
| extend Count = 100
| extend Total = Count * 5

// Real (double-precision floating point)
| extend Percentage = 85.5
| extend Ratio = 0.95

// Datetime
| extend Now = now()
| extend Yesterday = ago(1d)
| extend CustomDate = datetime(2024-01-15)

// Timespan
| extend Duration = 1h
| extend Interval = time(1.00:30:00)  // 1 hour, 30 minutes

// Bool
| extend IsError = Level == "Error"
| extend IsHighPriority = true

// Dynamic (JSON-like)
| extend Properties = dynamic({"key": "value", "count": 42})
| extend ArrayData = dynamic(["item1", "item2", "item3"])

Type Conversion:

// String to numeric
| extend NumericString = "42"
| extend AsLong = tolong(NumericString)
| extend AsReal = toreal(NumericString)

// String to datetime
| extend DateString = "2024-01-15T10:30:00Z"
| extend AsDateTime = todatetime(DateString)

// To string
| extend CountAsString = tostring(CounterValue)

// Dynamic parsing
| extend JsonString = '{"status": "error", "code": 500}'
| extend Parsed = parse_json(JsonString)
| extend StatusValue = Parsed.status
| extend CodeValue = toint(Parsed.code)

String Operations I Use Daily

Working with logs means lots of string manipulation:

Extraction and Parsing:

AzureActivity
| extend 
    // Extract using regex
    IpAddress = extract("(\\d+\\.\\d+\\.\\d+\\.\\d+)", 1, Message),
    
    // Split strings
    ResourceParts = split(ResourceId, "/"),
    SubscriptionId = split(ResourceId, "/")[2],
    ResourceType = split(ResourceId, "/")[6],
    
    // Substring
    First10Chars = substring(Message, 0, 10),
    
    // Parse key-value pairs  
    ParsedKV = parse_kv(Message, ":", " ")

String Matching:

// Different matching strategies
AzureActivity
| where Message contains "error"           // Substring, case-insensitive
| where Message has "error"                // Word boundary, faster
| where Message startswith "ERROR:"
| where Message endswith "failed"
| where Message matches regex "E[0-9]{3}"  // Regex: E followed by 3 digits

String Building:

Heartbeat
| extend 
    // Concatenation
    FullName = strcat(Computer, " - ", OSType),
    
    // Formatting
    DisplayTime = format_datetime(TimeGenerated, "yyyy-MM-dd HH:mm:ss"),
    
    // Replace
    CleanMessage = replace("ERROR", "ISSUE", Message),
    
    // Replace regex
    MaskedIp = replace_regex(Message, "\\d+\\.\\d+\\.\\d+\\.\\d+", "X.X.X.X")

DateTime Operations

Time is fundamental to observability. Here are the time operations I rely on:

Relative Time:

// Common time ranges
| where TimeGenerated > ago(1h)        // Last hour
| where TimeGenerated > ago(1d)        // Last day
| where TimeGenerated > ago(7d)        // Last week
| where TimeGenerated > ago(30d)       // Last 30 days

// Time arithmetic
| where TimeGenerated > now() - 2h
| where TimeGenerated > startofday(now())
| where TimeGenerated > startofweek(now())
| where TimeGenerated > startofmonth(now())

Date Parts and Formatting:

Heartbeat
| extend 
    // Extract parts
    Year = datetime_part("year", TimeGenerated),
    Month = datetime_part("month", TimeGenerated),
    Day = datetime_part("day", TimeGenerated),
    Hour = datetime_part("hour", TimeGenerated),
    Minute = datetime_part("minute", TimeGenerated),
    DayOfWeek = dayofweek(TimeGenerated),    // 0 = Sunday
    DayOfYear = dayofyear(TimeGenerated),
    WeekOfYear = weekofyear(TimeGenerated),
    
    // Formatting
    DateOnly = format_datetime(TimeGenerated, "yyyy-MM-dd"),
    TimeOnly = format_datetime(TimeGenerated, "HH:mm:ss"),
    Custom = format_datetime(TimeGenerated, "yyyy-MM-dd HH:mm")

Time Calculations:

Heartbeat
| extend 
    // Differences
    MinutesAgo = datetime_diff("minute", now(), TimeGenerated),
    HoursAgo = datetime_diff("hour", now(), TimeGenerated),
    
    // Add/subtract
    OneHourLater = TimeGenerated + 1h,
    YesterdaySameTime = TimeGenerated - 1d,
    
    // Start/end of periods
    DayStart = startofday(TimeGenerated),
    DayEnd = endofday(TimeGenerated),
    WeekStart = startofweek(TimeGenerated),
    MonthStart = startofmonth(TimeGenerated)

Time Binning:

// Group into time buckets
Perf
| summarize avg(CounterValue) 
    by bin(TimeGenerated, 5m)      // 5-minute intervals

// Other time bins I use
| by bin(TimeGenerated, 1h)        // Hourly
| by bin(TimeGenerated, 1d)        // Daily  
| by bin(TimeGenerated, 7d)        // Weekly

Practical Query Patterns

Here are query patterns I use regularly in production:

Pattern 1: Error Rate Over Time

AppTraces
| where TimeGenerated > ago(24h)
| summarize 
    Total = count(),
    Errors = countif(Level == "Error"),
    ErrorRate = 100.0 * countif(Level == "Error") / count()
    by bin(TimeGenerated, 1h)
| order by TimeGenerated asc

Pattern 2: Top Talkers

AzureDiagnostics
| where TimeGenerated > ago(1h)
| where Category == "ApplicationGatewayAccessLog"
| summarize RequestCount = count() by clientIp_s
| top 20 by RequestCount desc

Pattern 3: Resource Health Check

Heartbeat
| where TimeGenerated > ago(5m)
| summarize LastHeartbeat = max(TimeGenerated) by Computer
| extend MinutesSinceLastHeartbeat = datetime_diff("minute", now(), LastHeartbeat)
| where MinutesSinceLastHeartbeat > 3
| project Computer, LastHeartbeat, MinutesSinceLastHeartbeat
| order by MinutesSinceLastHeartbeat desc

Perf
| where TimeGenerated > ago(7d)
| where CounterName == "% Processor Time"
| summarize 
    AvgCpu = avg(CounterValue),
    P95Cpu = percentile(CounterValue, 95)
    by Computer, bin(TimeGenerated, 1h)
| render timechart

Query Performance Tips

Based on my experience optimizing queries:

1. Filter Early and Aggressively

// Good - Filter immediately
Perf
| where TimeGenerated > ago(1h)
| where CounterName == "% Processor Time"
| where Computer == "web-server-01"
| summarize avg(CounterValue)

// Less optimal - Filter after summarize
Perf
| summarize avg(CounterValue) by Computer, CounterName, bin(TimeGenerated, 1m)
| where TimeGenerated > ago(1h)
| where CounterName == "% Processor Time"

2. Use Appropriate Operators

// Fast - Use 'has' for word matching
| where Message has "error"

// Slower - 'contains' is more flexible but slower
| where Message contains "error"

// Slowest - Regex is powerful but expensive
| where Message matches regex "error"

3. Limit During Development

// Use take while developing
AzureActivity
| where TimeGenerated > ago(7d)
| take 100    // Remove this in production
| where ResourceGroup == "production-rg"
| summarize count() by OperationNameValue

4. Project Only Needed Columns

// Good - Select only what you need
AzureActivity
| project TimeGenerated, OperationNameValue, ResourceGroup
| where TimeGenerated > ago(1h)

// Less optimal - Working with all columns
AzureActivity
| where TimeGenerated > ago(1h)
// All columns retained

Common Mistakes I Made

Let me share mistakes I made when learning KQL:

Mistake 1: Wrong operator order

// Wrong - summarize before where
AzureActivity
| summarize count() by ResourceGroup
| where TimeGenerated > ago(1h)  // Too late!

// Correct
AzureActivity
| where TimeGenerated > ago(1h)
| summarize count() by ResourceGroup

Mistake 2: String comparison case sensitivity

// This might miss matches
| where Level == "error"  // Won't match "Error" or "ERROR"

// Better approaches
| where tolower(Level) == "error"
| where Level =~ "error"  // Case-insensitive equality

Mistake 3: Not handling nulls

// This filters out nulls unexpectedly
| where ErrorMessage != ""

// Explicit null handling
| where isnotempty(ErrorMessage)

Practice Exercises

Try these queries to reinforce your learning:

Exercise 1: Activity Summary

// Find hourly activity counts by level for the last 24 hours
AzureActivity
| where TimeGenerated > ago(24h)
| summarize count() by Level, bin(TimeGenerated, 1h)
| order by TimeGenerated asc

Exercise 2: Performance Analysis

// Find servers with average CPU > 80% in the last hour
Perf
| where TimeGenerated > ago(1h)
| where CounterName == "% Processor Time"
| summarize AvgCpu = avg(CounterValue) by Computer
| where AvgCpu > 80
| order by AvgCpu desc

Exercise 3: String Parsing

// Extract resource type from ResourceId
AzureActivity
| where TimeGenerated > ago(1h)
| extend ResourceParts = split(ResourceId, "/")
| extend ResourceType = ResourceParts[6]
| summarize count() by ResourceType
| top 10 by count_

Key Takeaways

where filters rows - use it early and often
project selects specific columns and creates calculated fields
extend adds columns while keeping all original data
summarize aggregates data - the heart of analytics queries
Always filter by time first for performance
Understand string operations for log parsing
DateTime functions are essential for time-series analysis
Query performance matters - filter early, project sparingly

In Part 3, we'll explore advanced operators like join, union, mv-expand, and powerful functions that enable complex analysis across multiple data sources.

PreviousPart 1: Introduction to KQL and Azure Log Analytics NextPart 3: Advanced Query Operators and Functions

Last updated 14 hours ago

hashtagBuilding on the Basics

hashtagCore Tabular Operators

hashtag1. where - Filtering Rows

hashtag2. project - Selecting and Computing Columns

hashtag3. extend - Adding Columns Without Removing Others

hashtag4. summarize - Aggregating Data

hashtag5. order (sort) - Sorting Results

hashtag6. take (limit) - Limiting Results

hashtag7. top - Getting Top N Results

hashtag8. distinct - Unique Values

hashtag9. sample - Random Sampling

hashtagData Types in KQL

hashtagCommon Data Types:

hashtagType Conversion:

hashtagString Operations I Use Daily

hashtagExtraction and Parsing:

hashtagString Matching:

hashtagString Building:

hashtagDateTime Operations

hashtagRelative Time:

hashtagDate Parts and Formatting:

hashtagTime Calculations:

hashtagTime Binning:

hashtagPractical Query Patterns

hashtagPattern 1: Error Rate Over Time

hashtagPattern 2: Top Talkers

hashtagPattern 3: Resource Health Check

hashtagPattern 4: Performance Trending

hashtagQuery Performance Tips

hashtag1. Filter Early and Aggressively

hashtag2. Use Appropriate Operators

hashtag3. Limit During Development

hashtag4. Project Only Needed Columns

hashtagCommon Mistakes I Made

hashtagMistake 1: Wrong operator order

hashtagMistake 2: String comparison case sensitivity

hashtagMistake 3: Not handling nulls

hashtagPractice Exercises

hashtagExercise 1: Activity Summary

hashtagExercise 2: Performance Analysis

hashtagExercise 3: String Parsing

hashtagKey Takeaways