Part 3: Advanced Query Operations and Functions

Beyond Basic Queries

Once I mastered the fundamentals, I needed advanced techniques for complex production scenarios. This part covers the powerful operations I use for sophisticated log analysis.

Advanced Filtering with Regular Expressions

Regular expressions unlock powerful pattern matching capabilities.

Regex Syntax in CloudWatch

CloudWatch uses standard regex syntax:

# Basic patterns
filter @message like /ERROR/          # Contains "ERROR"
filter @message like /^ERROR/         # Starts with "ERROR"
filter @message like /ERROR$/         # Ends with "ERROR"
filter @message like /ERROR|WARN/     # Contains "ERROR" or "WARN"

# Character classes
filter @message like /[Ee]rror/       # "Error" or "error"
filter @message like /[0-9]+/         # Contains digits
filter @message like /[A-Za-z]+/      # Contains letters

# Quantifiers
filter @message like /ERROR.*/        # ERROR followed by anything
filter @message like /ERROR.+/        # ERROR followed by at least one char
filter @message like /ERROR.{10}/     # ERROR followed by exactly 10 chars
filter @message like /ERROR.{5,10}/   # ERROR followed by 5-10 chars

Real Example: Complex Log Filtering

# Find errors with request IDs
fields @timestamp, @message
| filter @message like /ERROR.*request_id=[a-f0-9]{8}-[a-f0-9]{4}/
| sort @timestamp desc
| limit 50

Capturing Groups with Parse

# Extract specific patterns
parse @message /(?<level>ERROR|WARN|INFO) \[(?<component>[^\]]+)\] (?<msg>.*)/

# Multiple captures
parse @message /user=(?<user>[^ ]+) action=(?<action>[^ ]+) duration=(?<duration>[0-9]+)ms/

# Optional groups
parse @message /(?:user=(?<user>[^ ]+))?\s*(?:duration=(?<duration>[0-9]+))?/

Real Example: Parse Structured Log Line

fields @timestamp, @message
| parse @message /\[(?<timestamp>[^\]]+)\] (?<level>[A-Z]+) (?<component>[^\:]+): (?<message>.*)/
| filter level in ["ERROR", "WARN"]
| stats count() by component, level
| sort count desc

Advanced Aggregations

Window Functions

CloudWatch doesn't have traditional window functions, but we can achieve similar results:

Running Totals Pattern

# Count by time intervals
stats count() as events by bin(5m) as interval
| sort interval

Moving Calculations

# Calculate rate of change
stats count() as requests by bin(1h) as hour
| sort hour

Multi-Level Aggregations

# Aggregate, then aggregate again
stats count() as requests, 
      avg(duration) as avg_duration 
      by endpoint, statusCode
| stats avg(avg_duration) as overall_avg by endpoint
| sort overall_avg desc

Percentile Analysis

This is critical for understanding performance:

fields @timestamp, duration
| stats 
    count() as requests,
    pct(duration, 50) as p50,
    pct(duration, 75) as p75,
    pct(duration, 90) as p90,
    pct(duration, 95) as p95,
    pct(duration, 99) as p99,
    pct(duration, 99.9) as p999
| display requests, p50, p75, p90, p95, p99, p999

Real Example: Response Time Distribution

fields @timestamp, endpoint, duration
| filter duration > 0
| stats 
    count() as total_requests,
    pct(duration, 50) as p50_ms,
    pct(duration, 95) as p95_ms,
    pct(duration, 99) as p99_ms,
    max(duration) as max_ms,
    avg(duration) as avg_ms
    by endpoint
| filter total_requests > 100
| sort p95_ms desc
| limit 20

Complex Parsing Patterns

Multi-Step Parsing

Sometimes logs require multiple parse operations:

# First parse
parse @message /Request: (?<request_data>.*)/

# Then parse extracted field
parse request_data /method=(?<method>[^ ]+) path=(?<path>[^ ]+)/

# Use extracted fields
filter method = "POST"
| stats count() by path

Parsing JSON Embedded in Logs

# Log format: "INFO: json_data={...}"
parse @message /json_data=(?<json_str>\{.*\})/

# CloudWatch auto-parses JSON structure
# Access as: json_str.field_name

Real Example: Parse Nginx Access Logs

fields @timestamp, @message
| parse @message /(?<ip>[0-9.]+) - (?<user>[^ ]+) \[(?<timestamp>[^\]]+)\] "(?<method>[A-Z]+) (?<path>[^ ]+) HTTP\/[^"]+" (?<status>[0-9]+) (?<bytes>[0-9]+) "(?<referer>[^"]*)" "(?<user_agent>[^"]*)"/
| filter status >= 400
| stats count() as errors by status, path
| sort errors desc

Parse with Glob Patterns

Glob patterns are simpler for structured logs:

# Simple format
parse @message "* * * *" as timestamp, level, component, message

# With literals
parse @message "[*] *: *" as timestamp, level, message

# Mixed
parse @message "User * performed * in * ms" as user, action, duration

Real Example: Application Log Parsing

fields @timestamp, @message
| parse @message "[*] * - * - *ms" as timestamp, level, operation, duration
| fields @timestamp, level, operation, toNumber(duration) as duration_ms
| filter duration_ms > 1000
| stats count(), avg(duration_ms), max(duration_ms) by operation
| sort avg(duration_ms) desc

Advanced Time-Series Analysis

Time Bucketing and Grouping

# Events per 5-minute bucket
stats count() by bin(5m)

# Multiple time granularities
stats count() by bin(5m) as time_bucket, statusCode
| sort time_bucket, statusCode

Detect Anomalies Over Time

# Find sudden spikes in error rate
stats 
  count() as total,
  count(if(statusCode >= 400, 1, 0)) as errors
  by bin(5m) as interval
| fields interval, 
    errors * 100.0 / total as error_rate_pct
| sort interval

Rate Calculations

# Requests per second
stats count() as total_requests by bin(1m) as minute
| fields minute, total_requests / 60 as requests_per_second
| sort minute

Real Example: Traffic Pattern Analysis

fields @timestamp, endpoint, statusCode
| stats 
    count() as requests,
    count(if(statusCode >= 500, 1, 0)) as server_errors,
    count(if(statusCode >= 400 and statusCode < 500, 1, 0)) as client_errors
    by bin(5m) as time_bucket, endpoint
| fields time_bucket, endpoint, requests,
    server_errors * 100.0 / requests as server_error_pct,
    client_errors * 100.0 / requests as client_error_pct
| filter server_error_pct > 1 or client_error_pct > 5
| sort time_bucket desc

Working with Multiple Log Groups

Sometimes you need to correlate data across services.

Query Multiple Log Groups

In the console:

1. Select multiple log groups (Ctrl/Cmd + Click)

2. Write query that works across all groups

3. Use @log field to differentiate

fields @timestamp, @message, @log
| filter @message like /ERROR/
| stats count() by @log

Cross-Service Correlation

# Query API Gateway + Lambda together
fields @timestamp, @message, @logStream
| filter @message like /request_id:(?<req_id>[a-f0-9\-]+)/
| parse @message /request_id:(?<request_id>[a-f0-9\-]+)/
| stats count() by request_id, @log
| sort count desc

Real Example: End-to-End Request Tracing

# Across API Gateway, Lambda, and RDS
fields @timestamp, @message, @log
| parse @message /requestId[=:]\s*(?<request_id>[a-f0-9\-]+)/
| filter ispresent(request_id)
| fields @timestamp, request_id, @log, @message
| sort @timestamp

Advanced Statistical Functions

Standard Deviation

fields duration
| stats 
    avg(duration) as mean,
    stddev(duration) as std_dev,
    count() as sample_size

Coefficient of Variation

stats 
    avg(duration) as mean,
    stddev(duration) as std_dev
| fields mean, std_dev, (std_dev / mean * 100) as coeff_variation

Outlier Detection

fields @timestamp, endpoint, duration
| stats 
    avg(duration) as mean,
    stddev(duration) as std_dev
    by endpoint
| fields endpoint, mean, std_dev,
    mean + (3 * std_dev) as upper_threshold,
    mean - (3 * std_dev) as lower_threshold

Real Example: Performance Outliers

# Find requests beyond 3 standard deviations
fields @timestamp, duration as request_duration
| stats avg(request_duration) as mean_duration,
        stddev(request_duration) as std_dev_duration
| fields mean_duration, 
    mean_duration + (3 * std_dev_duration) as outlier_threshold
# Then in second query:
fields @timestamp, endpoint, duration
| filter duration > 1500  # Use calculated threshold
| sort duration desc

Conditional Aggregations

Count with Conditions

stats 
  count() as total,
  count(if(statusCode = 200, 1, 0)) as success,
  count(if(statusCode >= 400 and statusCode < 500, 1, 0)) as client_errors,
  count(if(statusCode >= 500, 1, 0)) as server_errors

Sum with Conditions

stats 
  sum(if(statusCode = 200, bytes, 0)) as success_bytes,
  sum(if(statusCode >= 400, bytes, 0)) as error_bytes

Average with Filters

stats 
  avg(if(statusCode = 200, duration, null)) as success_avg_duration,
  avg(if(statusCode >= 400, duration, null)) as error_avg_duration

Real Example: Success vs Error Performance

fields @timestamp, statusCode, duration
| stats 
    count() as total_requests,
    count(if(statusCode < 400, 1, 0)) as successful,
    count(if(statusCode >= 400, 1, 0)) as failed,
    avg(if(statusCode < 400, duration, null)) as success_avg_ms,
    avg(if(statusCode >= 400, duration, null)) as failure_avg_ms,
    pct(if(statusCode < 400, duration, null), 95) as success_p95,
    pct(if(statusCode >= 400, duration, null), 95) as failure_p95
| fields total_requests, successful, failed,
    successful * 100.0 / total_requests as success_rate,
    success_avg_ms, failure_avg_ms, success_p95, failure_p95

Data Transformation Techniques

Categorization

# Categorize response times
fields duration,
  if(duration < 100, "excellent",
    if(duration < 300, "good",
      if(duration < 1000, "acceptable",
        if(duration < 3000, "slow", "unacceptable")
      )
    )
  ) as performance_tier

| stats count() by performance_tier

Bucketing

# Bucket into ranges
fields duration,
  floor(duration / 100) * 100 as duration_bucket
| stats count() by duration_bucket
| sort duration_bucket

Real Example: Traffic Categorization

fields @timestamp, bytes_sent,
  if(bytes_sent < 1024, "tiny",
    if(bytes_sent < 10240, "small",
      if(bytes_sent < 102400, "medium",
        if(bytes_sent < 1048576, "large", "huge")
      )
    )
  ) as size_category
| stats 
    count() as requests,
    sum(bytes_sent) / 1024 / 1024 as total_mb
    by size_category
| sort total_mb desc

Deduplication Techniques

Count Distinct

# Unique users
stats count_distinct(userId) as unique_users

# Unique IPs
stats count_distinct(ip_address) as unique_ips by bin(1h)

Finding Duplicates

# Find duplicate request IDs
stats count() as occurrences by requestId
| filter occurrences > 1
| sort occurrences desc

Real Example: Unique User Activity

fields @timestamp, userId, action, endpoint
| filter ispresent(userId)
| stats 
    count() as total_actions,
    count_distinct(userId) as unique_users,
    count_distinct(action) as unique_action_types
    by endpoint
| fields endpoint, unique_users, total_actions,
    total_actions * 1.0 / unique_users as actions_per_user
| sort unique_users desc

Complex String Manipulation

Extract and Transform

# Extract domain from URL
parse @message /https?:\/\/(?<domain>[^\/]+)/
| stats count() by domain

# Extract file extension
parse path /\.(?<ext>[^.\/]+)$/
| stats count() by ext

String Replacement and Cleaning

# Remove sensitive data
fields @message 
| fields replace(@message, /password=[^&]+/, "password=***") as cleaned_message

Case Normalization

# Normalize for grouping
fields tolower(method) as method_lower,
       toupper(status_text) as status_upper
| stats count() by method_lower

Real Example: URL Path Analysis

fields @timestamp, @message
| parse @message /"path":"(?<path>[^"]+)"/
| parse path /^\/(?<api_version>v[0-9]+)\/(?<resource>[^\/\?]+)/
| filter ispresent(resource)
| stats 
    count() as requests,
    count_distinct(@requestId) as unique_requests
    by api_version, resource
| sort requests desc
| limit 20

null Handling and Coalescing

Dealing with Missing Data

# Check for null
filter ispresent(field_name)
filter not ispresent(field_name)

# Provide defaults
fields if(ispresent(userId), userId, "anonymous") as user
fields if(ispresent(errorCode), errorCode, 0) as error_code

Coalesce Pattern

# Use first non-null value
fields 
  if(ispresent(customerId), customerId,
    if(ispresent(userId), userId,
      if(ispresent(sessionId), sessionId, "unknown")
    )
  ) as identifier

Real Example: Complete Missing Fields

fields @timestamp,
  if(ispresent(userId), userId, "anonymous") as user,
  if(ispresent(statusCode), statusCode, 0) as status,
  if(ispresent(duration), duration, -1) as duration_ms,
  if(ispresent(errorMessage), errorMessage, "no error") as error
| filter user != "anonymous" or status >= 400
| stats count() by user, status

Advanced Performance Queries

Rate/Throughput Analysis

# Requests per second over time
stats count() as requests by bin(1s) as second
| fields second, requests as rps
| sort second desc
| limit 100

Latency Heatmap Data

# Bucket latencies for visualization
fields duration,
  bin(1m) as minute,
  floor(duration / 50) * 50 as latency_bucket
| stats count() by minute, latency_bucket
| sort minute, latency_bucket

Real Example: P50/P95/P99 Tracking

fields @timestamp, endpoint, duration
| filter duration > 0 and ispresent(endpoint)
| stats 
    count() as requests,
    pct(duration, 50) as p50,
    pct(duration, 95) as p95,
    pct(duration, 99) as p99,
    max(duration) as max_duration
    by bin(5m) as interval, endpoint
| filter requests > 10
| sort interval desc, p95 desc

Security Analysis Patterns

Failed Authentication Attempts

fields @timestamp, @message, ip_address
| filter @message like /authentication failed|invalid credentials/
| parse @message /user[=:]?\s*(?<attempted_user>[^ ,\]]+)/
| stats count() as attempts by ip_address, attempted_user
| filter attempts >= 5
| sort attempts desc

Suspicious Activity Detection

fields @timestamp, userId, action, ip_address
| stats 
    count() as actions,
    count_distinct(ip_address) as unique_ips,
    count_distinct(action) as unique_actions
    by userId, bin(5m) as interval
| filter actions > 100 or unique_ips > 5
| sort actions desc

Real Example: Brute Force Detection

fields @timestamp, @message
| filter @message like /login failed|authentication failed/
| parse @message /ip=(?<ip>[0-9.]+)/
| parse @message /user=(?<username>[^ ]+)/
| stats count() as failed_attempts by ip, username, bin(5m) as time_window
| filter failed_attempts >= 10
| sort time_window desc, failed_attempts desc

Key Takeaways

• Regular expressions enable powerful pattern matching

• Multi-level aggregations provide deeper insights

• Percentiles (p50, p95, p99) are critical for performance analysis

• Complex parsing handles real-world log formats

• Time-series operations reveal trends and anomalies

• Conditional aggregations enable sophisticated analysis

• String manipulation and transformation clean and normalize data

• count_distinct helps with deduplication and uniqueness analysis

• Multiple log groups enable cross-service correlation

In Part 4, we'll explore querying specific AWS services - Lambda, API Gateway, ECS, RDS, and more - with production-ready query patterns from my experience.