Part 7: Real-World CloudWatch Query Patterns

Production-Proven Query Patterns

After years of operating AWS infrastructure, I've developed a library of query patterns that solve real problems. This final part shares the production patterns I rely on daily.

Golden Signals Monitoring

The four golden signals (from Google's SRE book) form the foundation of my monitoring strategy.

1. Latency - Response Time Tracking

Pattern: P50/P95/P99 Latency

fields @timestamp, duration
| filter @timestamp > ago(1h)
| filter ispresent(duration)
| filter duration > 0
| stats 
    count() as requests,
    pct(duration, 50) as p50_ms,
    pct(duration, 95) as p95_ms,
    pct(duration, 99) as p99_ms
    by bin(5m) as interval
| sort interval desc

Pattern: Latency by Endpoint

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

Pattern: Slow Request Deep Dive

fields @timestamp, endpoint, duration, userId, @requestId
| filter duration > 3000
| filter @timestamp > ago(1h)
| sort duration desc
| limit 50

2. Traffic - Request Rate Monitoring

Pattern: Requests Per Second

fields @timestamp
| filter @timestamp > ago(1h)
| stats count() as total_requests by bin(1m) as minute
| fields minute, total_requests / 60 as requests_per_second
| sort minute desc

Pattern: Traffic by Endpoint

fields endpoint
| filter @timestamp > ago(1h)
| stats count() as request_count by endpoint
| sort request_count desc
| limit 20

Pattern: Concurrent Users

fields userId, @timestamp
| filter ispresent(userId)
| filter @timestamp > ago(5m)
| stats count_distinct(userId) as active_users by bin(1m)
| sort bin(1m) desc

3. Errors - Error Rate and Types

Pattern: Error Rate Percentage

fields statusCode, @timestamp
| filter @timestamp > ago(1h)
| stats 
    count() as total,
    count(if(statusCode >= 400 and statusCode < 500, 1, 0)) as client_errors,
    count(if(statusCode >= 500, 1, 0)) as server_errors
    by bin(5m) as interval
| fields interval,
    client_errors * 100.0 / total as client_error_pct,
    server_errors * 100.0 / total as server_error_pct,
    (client_errors + server_errors) * 100.0 / total as total_error_pct
| sort interval desc

Pattern: Top Error Messages

fields @timestamp, @message
| filter @message like /ERROR|FATAL|Exception/
| filter @timestamp > ago(1h)
| parse @message /(?:ERROR|FATAL|Exception)[:\s]*(?<error_message>[^\n]{0,200})/
| stats count() as occurrences by error_message
| sort occurrences desc
| limit 15

Pattern: First Occurrence of New Errors

fields @timestamp, @message
| filter @message like /ERROR/
| parse @message /ERROR[:\s]*(?<error_type>[A-Za-z]+)/
| stats min(@timestamp) as first_seen, count() as total_count by error_type
| sort first_seen desc

4. Saturation - Resource Utilization

Pattern: Lambda Memory Usage

fields @timestamp, @message
| filter @type = "REPORT"
| parse @message /Memory Size: (?<mem_size>[0-9]+) MB Max Memory Used: (?<mem_used>[0-9]+) MB/
| fields @timestamp, 
    mem_used as memory_used_mb,
    mem_size as memory_limit_mb,
    mem_used * 100.0 / mem_size as memory_used_pct
| filter memory_used_pct > 80
| sort memory_used_pct desc

Pattern: Database Connection Pool

fields @timestamp, @message
| filter @message like /connection pool|connections/
| parse @message /active[=:\s]*(?<active>[0-9]+).*max[=:\s]*(?<max>[0-9]+)/
| fields @timestamp, active, max, active * 100.0 / max as utilization_pct
| filter utilization_pct > 70
| sort utilization_pct desc

SLO Tracking Patterns

Service Level Objectives are critical for production reliability.

Availability SLO (99.9%)

fields statusCode, @timestamp
| filter @timestamp > ago(24h)
| stats 
    count() as total_requests,
    count(if(statusCode < 500, 1, 0)) as successful_requests
| fields total_requests, successful_requests,
    successful_requests * 100.0 / total_requests as availability_pct,
    99.9 as slo_target,
    if(successful_requests * 100.0 / total_requests >= 99.9, "✓ Meeting SLO", "✗ Violating SLO") as slo_status

Latency SLO (95% < 200ms)

fields duration, @timestamp
| filter @timestamp > ago(24h)
| filter duration > 0
| stats 
    count() as total_requests,
    pct(duration, 95) as p95_latency,
    count(if(duration < 200, 1, 0)) as fast_requests
| fields total_requests, p95_latency,
    fast_requests * 100.0 / total_requests as fast_request_pct,
    200 as slo_target_ms,
    if(p95_latency < 200, "✓ Meeting SLO", "✗ Violating SLO") as slo_status

Error Budget Calculation

fields statusCode, @timestamp
| filter @timestamp > ago(30d)
| stats 
    count() as total_requests,
    count(if(statusCode >= 500, 1, 0)) as errors
| fields 
    total_requests,
    errors,
    total_requests * 0.001 as error_budget,  # 99.9% SLO = 0.1% error budget
    errors as errors_consumed,
    (total_requests * 0.001 - errors) as error_budget_remaining,
    errors / (total_requests * 0.001) * 100 as error_budget_consumed_pct

Incident Response Patterns

Queries I run during production incidents.

Pattern: Spike Detection

fields @timestamp
| filter @timestamp > ago(2h)
| stats count() as request_count by bin(1m) as minute
| sort minute
# Look for sudden increases

Pattern: Correlation Analysis

fields @timestamp, endpoint, statusCode, duration
| filter @timestamp > ago(1h)
| stats 
    count() as requests,
    count(if(statusCode >= 500, 1, 0)) as errors,
    avg(duration) as avg_latency
    by bin(5m) as interval, endpoint
| sort interval desc
# Identify which endpoint has issues

Pattern: Upstream Dependency Failures

fields @timestamp, @message
| filter @message like /timeout|connection refused|503|502/
| parse @message /(?<service_name>[a-zA-Z0-9\-]+)\.(execute-api|amazonaws|rds)/
| stats count() as failure_count by service_name, bin(5m)
| sort bin(5m) desc, failure_count desc

Pattern: Cascading Failures

fields @timestamp, @message, @log
| filter @message like /ERROR|timeout|failed/
| stats count() as error_count by @log, bin(1m) as minute
| sort minute desc, error_count desc
# Identify which services affected and in what order

Pattern: Recent Deployments

fields @timestamp, @message
| filter @message like /deployed|deployment|version|release/
| parse @message /version[=:\s]*(?<version>[^ ,\n]+)/
| sort @timestamp desc
| limit 10

Performance Troubleshooting Patterns

Pattern: N+1 Query Detection

fields @timestamp, @message
| filter @message like /SELECT/
| parse @message "SELECT * FROM * WHERE id = *" as table, id
| stats count() as query_count by @requestId, table
| filter query_count > 10
| sort query_count desc

Pattern: Memory Leak Detection

fields @timestamp, @message
| filter @type = "REPORT"
| parse @message /RequestId: (?<request_id>[^ ]+).*Max Memory Used: (?<mem>[0-9]+)/
| stats avg(mem) as avg_memory_mb by bin(10m) as interval
| sort interval
# Look for steadily increasing memory

Pattern: Cache Efficiency

fields @timestamp, @message
| filter @message like /cache/
| parse @message /cache[=:\s]*(?<cache_result>hit|miss)/
| stats 
    count() as total,
    count(if(cache_result = "hit", 1, 0)) as hits,
    count(if(cache_result = "miss", 1, 0)) as misses
    by bin(5m) as interval
| fields interval, 
    hits * 100.0 / total as hit_rate_pct,
    misses * 100.0 / total as miss_rate_pct
| sort interval desc

Pattern: Bottleneck Identification

fields @timestamp, component, duration
| filter @timestamp > ago(1h)
| filter ispresent(component) and ispresent(duration)
| stats 
    count() as operations,
    sum(duration) as total_time_ms,
    avg(duration) as avg_ms,
    pct(duration, 95) as p95_ms
    by component
| fields component, operations, total_time_ms, avg_ms, p95_ms,
    total_time_ms / 1000 / 3600 as total_hours
| sort total_time_ms desc

Security Monitoring Patterns

Pattern: Failed Authentication Attempts

fields @timestamp, @message, sourceIP
| filter @message like /authentication failed|login failed|invalid credentials/
| parse @message /(?:user|username|email)[=:\s]*(?<attempted_user>[^ ,\n]+)/
| parse @message /(?:ip|sourceIP|clientIP)[=:\s]*(?<ip>[0-9.]+)/
| stats count() as failed_attempts by ip, attempted_user, bin(5m) as interval
| filter failed_attempts >= 5
| sort interval desc, failed_attempts desc

Pattern: Privilege Escalation Attempts

fields @timestamp, userId, @message
| filter @message like /unauthorized|forbidden|access denied|403/
| parse @message /action[=:\s]*(?<action>[^ ,\n]+)/
| parse @message /resource[=:\s]*(?<resource>[^ ,\n]+)/
| stats count() as attempts by userId, action, resource
| filter attempts >= 3
| sort attempts desc

Pattern: Suspicious Data Access

fields @timestamp, userId, @message
| filter @message like /SELECT|download|export/
| parse @message /(?:rows|records|items)[=:\s]*(?<row_count>[0-9]+)/
| filter row_count > 10000
| stats 
    count() as large_queries,
    sum(row_count) as total_rows_accessed
    by userId, bin(1h) as hour
| filter large_queries >= 5
| sort hour desc, total_rows_accessed desc

Pattern: Geographic Anomalies

fields @timestamp, userId, requestIP, @message
| filter ispresent(userId)
| parse @message /country[=:\s]*(?<country>[A-Z]{2})/
| stats 
    count() as requests,
    count_distinct(country) as unique_countries,
    count_distinct(requestIP) as unique_ips
    by userId, bin(1h) as hour
| filter unique_countries >= 3 or unique_ips >= 10
| sort hour desc

Pattern: Unusual API Usage

fields endpoint, userId, @timestamp
| filter @timestamp > ago(1h)
| stats count() as api_calls by userId, endpoint
| filter api_calls > 1000  # Threshold for rate limiting
| sort api_calls desc

Cost Optimization Patterns

Pattern: Lambda Cost Analysis

fields @timestamp, @message
| filter @type = "REPORT"
| parse @message /Duration: (?<duration>[0-9.]+)/
| parse @message /Billed Duration: (?<billed_ms>[0-9]+)/
| parse @message /Memory Size: (?<memory_mb>[0-9]+)/
| stats 
    count() as invocations,
    sum(billed_ms) / 1000 as total_billed_seconds,
    avg(memory_mb) as avg_memory_mb
| fields invocations, total_billed_seconds, avg_memory_mb,
    total_billed_seconds * avg_memory_mb / 1024 as gb_seconds,
    gb_seconds * 0.0000166667 as estimated_cost_usd

Pattern: API Gateway Bandwidth Usage

fields @timestamp, requestBytes, responseBytes
| filter @timestamp > ago(24h)
| stats 
    sum(requestBytes) as total_request_bytes,
    sum(responseBytes) as total_response_bytes
| fields 
    total_request_bytes / 1024 / 1024 / 1024 as request_gb,
    total_response_bytes / 1024 / 1024 / 1024 as response_gb,
    (total_request_bytes + total_response_bytes) / 1024 / 1024 / 1024 as total_gb

Pattern: Idle Resources

fields @logStream, @timestamp
| stats count() as log_events, max(@timestamp) as last_activity by @logStream
| filter log_events < 10
| sort last_activity
# Identify infrequently used functions

Pattern: Most Expensive Endpoints

fields endpoint, duration, @timestamp
| filter @timestamp > ago(24h)
| stats 
    count() as requests,
    avg(duration) as avg_duration_ms,
    sum(duration) as total_duration_ms
    by endpoint
| fields endpoint, requests, avg_duration_ms,
    total_duration_ms / 1000 / 3600 as total_hours,
    requests * avg_duration_ms as cost_proxy
| sort cost_proxy desc
| limit 20

Business Metrics Patterns

Pattern: User Journey Tracking

fields userId, action, @timestamp
| filter ispresent(userId)
| filter action in ["page_view", "add_to_cart", "checkout", "purchase"]
| stats count() by userId, action
| sort userId, @timestamp

Pattern: Conversion Funnel

fields @timestamp, @message
| filter @message like /funnel_event/
| parse @message /event[=:\s]*(?<event>view|click|signup|purchase)/
| stats count() as event_count by event
| sort 
    case 
        when event = "view" then 1
        when event = "click" then 2
        when event = "signup" then 3
        when event = "purchase" then 4
    end

Pattern: Feature Usage

fields featureName, userId, @timestamp
| filter @timestamp > ago(7d)
| filter ispresent(featureName)
| stats 
    count_distinct(userId) as unique_users,
    count() as total_uses
    by featureName
| fields featureName, unique_users, total_uses,
    total_uses * 1.0 / unique_users as uses_per_user
| sort unique_users desc

Pattern: Revenue Tracking

fields @timestamp, orderId, orderValue
| filter @message like /order_completed/
| filter @timestamp > ago(24h)
| parse @message /value[=:\s]*(?<amount>[0-9.]+)/
| stats 
    count() as orders,
    sum(amount) as total_revenue,
    avg(amount) as avg_order_value
    by bin(1h) as hour
| sort hour desc

Anomaly Detection Patterns

Pattern: Statistical Anomaly Detection

# Calculate baseline
fields duration
| filter @timestamp > ago(7d) and @timestamp < ago(1h)
| stats avg(duration) as baseline_avg, stddev(duration) as baseline_stddev

# Then compare current values
fields duration, @timestamp
| filter @timestamp > ago(1h)
| stats avg(duration) as current_avg
# If current_avg > baseline_avg + (3 * baseline_stddev), it's anomalous

Pattern: Sudden Traffic Changes

fields @timestamp
| stats count() as requests by bin(5m) as interval
| sort interval
# Compare consecutive intervals for > 50% change

Pattern: New Error Types

fields @timestamp, @message
| filter @message like /ERROR/
| parse @message /ERROR[:\s]*(?<error_type>[A-Za-z]+)/
| stats 
    min(@timestamp) as first_seen,
    count() as occurrences
    by error_type
| filter first_seen > ago(1h)
| sort occurrences desc

Capacity Planning Patterns

Pattern: Peak Traffic Analysis

fields @timestamp
| filter @timestamp > ago(30d)
| stats count() as requests by bin(1h) as hour
| stats 
    max(requests) as peak_hourly_requests,
    avg(requests) as avg_hourly_requests,
    pct(requests, 95) as p95_hourly_requests

Pattern: Growth Trending

fields @timestamp
| stats count() as daily_requests by bin(1d) as day
| sort day
# Export and analyze trend

Pattern: Resource Scaling Triggers

fields @timestamp, concurrent_users
| filter @timestamp > ago(24h)
| stats max(concurrent_users) as peak_concurrency by bin(1h)
| stats max(peak_concurrency) as highest_concurrency
# Use for capacity planning

Debugging Patterns

Pattern: Request Tracing

fields @timestamp, @message, @log
| filter @requestId = "abc-123-def-456"  # Specific request
| sort @timestamp
# Trace request across all services

Pattern: User Session Debugging

fields @timestamp, @message, action
| filter sessionId = "session-xyz"
| sort @timestamp
| limit 100

Pattern: Error Context

fields @timestamp, @message, @requestId
| filter @message like /ERROR/
| filter @timestamp > ago(1h)
| dedup @requestId  # Find unique errors
| sort @timestamp desc
| limit 10

# Then drill into specific request
| filter @requestId = "found-request-id"
| sort @timestamp

Compliance and Audit Patterns

Pattern: Data Access Audit

fields @timestamp, userId, action, resourceId
| filter action in ["read", "update", "delete"]
| filter resourceId like /sensitive/
| stats count() as access_count by userId, action
| sort access_count desc

Pattern: PII Access Tracking

fields @timestamp, userId, @message
| filter @message like /pii_access|personal_data/
| parse @message /resource[=:\s]*(?<resource_type>[^ ,]+)/
| stats count() as accesses by userId, resource_type, bin(1d) as day
| sort day desc

Pattern: Change Tracking

fields @timestamp, userId, action, @message
| filter action in ["create", "update", "delete"]
| parse @message /table[=:\s]*(?<table>[^ ,]+).*id[=:\s]*(?<record_id>[^ ,]+)/
| stats count() as changes by userId, action, table
| sort changes desc

Key Takeaways

• Golden signals (latency, traffic, errors, saturation) form monitoring foundation

• Track SLOs and error budgets systematically

• Have incident response queries ready before incidents occur

• Monitor security continuously with automated queries

• Use cost analysis patterns to optimize spending

• Track business metrics alongside technical metrics

• Implement anomaly detection for proactive alerting

• Plan capacity based on historical trend analysis

• Maintain audit trails for compliance

• Save and organize your query library

Closing Thoughts

CloudWatch Logs Insights has been invaluable in my AWS journey. The query patterns in this series come from real production experience - countless incidents debugged, systems optimized, and insights discovered.

Start with the basics, build your query library incrementally, and always optimize for readability and performance. Most importantly, share your queries with your team - collaborative query development leads to better observability.

Next Steps

1. Build your query library: Start with the patterns from this series

2. Create dashboards: Visualize your most important queries

3. Set up alerts: Convert queries into CloudWatch alarms

4. Document your queries: Add comments and context

5. Share with your team: Collaborative monitoring is more effective

6. Iterate and improve: Refine queries based on real incidents

Additional Resources

• CloudWatch Logs Insights Query Syntax

• CloudWatch Logs Insights Examples

• CloudWatch Pricing

Happy querying! 🚀

---

CloudWatch Query 101 Series:

• Part 1: Introduction to CloudWatch Logs Insights

• Part 2: CloudWatch Query Syntax Fundamentals

• Part 3: Advanced Query Operations and Functions

• Part 4: Querying AWS Services with CloudWatch Logs

• Part 5: Building Observability Dashboards with CloudWatch

• Part 6: CloudWatch Query Best Practices and Performance

• Part 7: Real-World CloudWatch Query Patterns (You are here)