Part 2: CloudWatch Query Syntax Fundamentals

Learning Query Commands

After mastering basic queries, I needed to understand the full power of CloudWatch Logs Insights syntax. This part covers all the essential commands I use daily.

Query Command Structure

Every CloudWatch Logs Insights query follows this pattern:

<command> <parameters>
| <command> <parameters>
| <command> <parameters>

Commands are chained with the pipe operator (|).

Core Commands

1. fields - Selecting Data

The fields command specifies which fields to display or extract.

# Select specific fields
fields @timestamp, @message

# Select all fields
fields @timestamp, @message, @logStream, @log

# Select and rename
fields @timestamp as time, @message as log_message

# Select using wildcards (for JSON logs)
fields @timestamp, request.*

Real Example from Lambda Logs

fields @timestamp, @requestId, @message
| sort @timestamp desc
| limit 10

2. filter - Filtering Data

The filter command reduces results based on conditions.

# Basic filter
filter @message like /ERROR/

# Multiple conditions with AND
filter @message like /ERROR/ and @logStream like /production/

# Multiple conditions with OR
filter @message like /ERROR/ or @message like /WARN/

# Negation
filter @message not like /DEBUG/

# Field comparisons
filter statusCode >= 400

# Existence checks
filter ispresent(errorMessage)

Filter Operators

Operator	Description	Example
=	Equals	statusCode = 200
!=	Not equals	statusCode != 200
<	Less than	duration < 100
<=	Less than or equal	duration <= 100
>	Greater than	duration > 1000
>=	Greater than or equal	duration >= 1000
like	Pattern match	@message like /ERROR/
not like	Pattern not match	@message not like /DEBUG/
in	Value in list	statusCode in [400, 404, 500]
not in	Value not in list	statusCode not in [200, 201]

Real Example from API Gateway Logs

fields @timestamp, requestId, status, ip
| filter status >= 400
| filter status != 404
| filter ip not like /^10\./
| sort @timestamp desc

3. stats - Aggregating Data

The stats command performs aggregations.

# Count total events
stats count()

# Count by field
stats count() by statusCode

# Multiple aggregations
stats count(), avg(duration), max(duration) by endpoint

# Count distinct values
stats count_distinct(userId)

# Percentiles
stats pct(duration, 95), pct(duration, 99)

Aggregation Functions

Function	Description	Example
count()	Count events	stats count()
count_distinct()	Count unique values	stats count_distinct(userId)
sum()	Sum values	stats sum(bytes)
avg()	Average	stats avg(duration)
min()	Minimum	stats min(duration)
max()	Maximum	stats max(duration)
stddev()	Standard deviation	stats stddev(latency)
pct()	Percentile	stats pct(duration, 95)

Real Example from Application Logs

fields @timestamp, endpoint, duration, statusCode
| filter duration > 0
| stats 
    count() as request_count,
    avg(duration) as avg_ms,
    pct(duration, 50) as p50,
    pct(duration, 95) as p95,
    pct(duration, 99) as p99
    by endpoint
| sort avg_ms desc

4. sort - Ordering Results

The sort command orders results.

# Sort ascending
sort @timestamp asc

# Sort descending (default)
sort @timestamp desc

# Sort by multiple fields
sort statusCode desc, @timestamp desc

# Sort aggregated results
stats count() by statusCode
| sort count desc

Real Example

fields @timestamp, endpoint, duration
| filter duration > 1000
| sort duration desc, @timestamp desc
| limit 50

5. limit - Restricting Results

The limit command restricts the number of results.

# Return top 10
limit 10

# With sort for top N
sort duration desc
| limit 10

# After aggregation
stats count() by endpoint
| sort count desc
| limit 5

6. parse - Extracting Fields

The parse command extracts fields using patterns.

# Regular expression parse
parse @message /Error: (?<errorType>[A-Za-z]+)/

# Multiple fields
parse @message /user=(?<user>[^ ]+) action=(?<action>[^ ]+)/

# Parse JSON
parse @message "* * *" as level, component, message

# Extract from structured logs
parse @message "[*] *" as timestamp, log_message

Parse Patterns

Glob patterns (simpler):

parse @message "* completed in * ms" as operation, duration

Regular expressions (more powerful):

parse @message /duration=(?<ms>[0-9]+)/

Real Example from Application Logs

fields @timestamp, @message
| filter @message like /duration=/
| parse @message /method=(?<method>[^ ]+) endpoint=(?<endpoint>[^ ]+) duration=(?<duration>[0-9]+)/
| stats avg(duration) by method, endpoint
| sort duration desc

7. display - Showing Fields

The display command shows visualization fields (similar to fields but for viz).

display @timestamp, avg_duration

Usually used with visualizations.

Working with Time

Time operations are crucial in log analysis.

Time Functions

# Current time minus duration
ago(15m)
ago(1h)
ago(1d)

# Time binning
bin(5m)
bin(1h)
bin(1d)

# Date formatting
datefloor(@timestamp, 1h)
dateceil(@timestamp, 1h)

Time-Based Queries

Events in Last Hour

fields @timestamp, @message
| filter @timestamp > ago(1h)

Events Per 5-Minute Interval

stats count() by bin(5m) as time_bucket
| sort time_bucket

Events Per Hour

stats count() by bin(1h) as hour
| sort hour

Real Example: Request Rate Over Time

fields @timestamp, requestId
| stats count() as requests by bin(5m)
| sort bin(5m)

Data Types

Understanding data types helps write correct queries.

Supported Types

• String: Text values ("hello", log messages)

• Number: Integers and floats (200, 12.34)

• Boolean: true or false

• Timestamp: ISO 8601 format

Type Conversion

CloudWatch automatically converts types, but you can be explicit:

# Parse string to number
parse @message /duration=(?<dur>[0-9]+)/
| filter toNumber(dur) > 1000

# Parse string timestamp
parse @message /timestamp=(?<ts>[^,]+)/
| sort ts

String Operations

String Functions

# String length
strlen(@message)

# Substring
substr(@message, 0, 10)

# Lowercase
tolower(@message)

# Uppercase
toupper(@message)

# Trim whitespace
trim(@message)

# Concatenation
concat(method, " ", endpoint)

# Replace
replace(@message, "old", "new")

String Matching

# Contains
filter @message like /ERROR/

# Starts with
filter @logStream like /^production/

# Ends with
filter @logStream like /\.log$/

# Case-insensitive (use tolower/toupper)
filter tolower(@message) like /error/

Real Example: Extract User Agent Info

fields @timestamp, @message
| parse @message /"userAgent":"(?<ua>[^"]+)"/
| filter ua like /Mobile/
| stats count() by ua
| sort count desc
| limit 10

Number Operations

Arithmetic

# Basic arithmetic
fields duration / 1000 as seconds

# Multiple operations
fields (bytes_sent + bytes_received) / 1024 / 1024 as mb_total

# With stats
stats sum(bytes) / 1024 / 1024 as total_mb

Math Functions

# Absolute value
abs(value)

# Ceiling
ceil(value)

# Floor
floor(value)

# Round
round(value)

# Natural log
log(value)

# Square root
sqrt(value)

Real Example: Convert Bytes to GB

fields @timestamp, @logStream, bytes_transferred
| stats sum(bytes_transferred) as total_bytes by @logStream
| fields @logStream, total_bytes / 1024 / 1024 / 1024 as total_gb
| sort total_gb desc

Working with JSON Logs

Many AWS services log in JSON format. Here's how I query them:

Accessing JSON Fields

If logs are JSON, CloudWatch automatically parses them:

{
  "requestId": "abc-123",
  "method": "GET",
  "path": "/api/users",
  "statusCode": 200,
  "duration": 45
}

Query:

fields @timestamp, requestId, method, path, statusCode, duration
| filter statusCode >= 400
| sort duration desc

Nested JSON Fields

{
  "request": {
    "method": "GET",
    "headers": {
      "userAgent": "Mozilla/5.0"
    }
  }
}

Query:

fields @timestamp, request.method, request.headers.userAgent
| filter request.method = "POST"

Real Example: Lambda Logs (JSON)

fields @timestamp, @message
| filter @type = "REPORT"
| parse @message /Duration: (?<duration>[0-9.]+)/
| parse @message /Billed Duration: (?<billed>[0-9]+)/
| parse @message /Memory Size: (?<memSize>[0-9]+)/
| parse @message /Max Memory Used: (?<memUsed>[0-9]+)/
| stats 
    avg(duration) as avg_duration,
    max(duration) as max_duration,
    avg(memUsed / memSize * 100) as avg_memory_pct

Boolean Logic

AND Logic

# Both conditions true
filter statusCode >= 400 and duration > 1000

# Multiple AND conditions
filter method = "POST" 
  and statusCode >= 400 
  and duration > 1000

OR Logic

# Either condition true
filter statusCode = 404 or statusCode = 500

# Multiple OR conditions
filter statusCode in [400, 401, 403, 404, 500, 502, 503]

Combined Logic

# Grouped conditions
filter (statusCode >= 400 and statusCode < 500) 
  or statusCode >= 500

# Complex filtering
filter method in ["POST", "PUT", "DELETE"]
  and statusCode >= 400
  and duration > 1000
  and @message not like /healthcheck/

Conditional Expressions

if Function

# Basic if
fields if(statusCode >= 400, "error", "success") as result

# Nested if
fields 
  if(statusCode >= 500, "server_error",
    if(statusCode >= 400, "client_error", "success")
  ) as error_type

# With stats
stats count() by if(duration > 1000, "slow", "fast") as speed_category

Real Example: Categorize Response Times

fields @timestamp, duration,
  if(duration < 100, "fast",
    if(duration < 500, "normal",
      if(duration < 1000, "slow", "very_slow")
    )
  ) as performance_category
| stats count() by performance_category

Checking for Field Existence

ispresent Function

# Check if field exists
filter ispresent(errorMessage)

# Check if field missing
filter not ispresent(userId)

# Conditional on existence
fields if(ispresent(errorCode), errorCode, "N/A") as error

Real Example: Handle Optional Fields

fields @timestamp,
  if(ispresent(errorMessage), errorMessage, "No error") as error,
  if(ispresent(userId), userId, "anonymous") as user
| filter errorMessage like /timeout/ or not ispresent(userId)

Practical Query Patterns

Pattern 1: Error Rate Over Time

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

Pattern 2: Top Error Messages

fields @message
| filter @message like /ERROR/
| parse @message /ERROR: (?<error>[^,\n]+)/
| stats count() as occurrences by error
| sort occurrences desc
| limit 10

Pattern 3: Performance by Endpoint

fields endpoint, duration
| filter ispresent(duration)
| stats
    count() as requests,
    avg(duration) as avg_ms,
    pct(duration, 50) as p50,
    pct(duration, 95) as p95,
    pct(duration, 99) as p99,
    max(duration) as max_ms
    by endpoint
| filter requests > 100
| sort p95 desc

Pattern 4: User Activity Analysis

fields @timestamp, userId, action
| filter ispresent(userId)
| stats count_distinct(action) as unique_actions,
        count() as total_actions
        by userId
| sort total_actions desc
| limit 20

Pattern 5: Time-Based Comparison

fields @timestamp, statusCode
| stats count() as requests by bin(1h) as hour, statusCode
| sort hour, statusCode

Query Optimization Tips

From my experience:

1. Filter Early

# Less efficient
parse @message /duration=(?<dur>[0-9]+)/
| filter dur > 1000

# More efficient
filter @message like /duration/
| parse @message /duration=(?<dur>[0-9]+)/
| filter dur > 1000

2. Limit Field Selection

# Don't do this
fields @*

# Do this
fields @timestamp, @message, statusCode, duration

3. Use Aggregations Wisely

# Aggregate before further processing
stats count() by endpoint
| filter count > 100
| sort count desc

4. Specific Time Ranges

# Good - specific range
filter @timestamp > ago(1h)

# Better - even more specific
filter @timestamp > ago(30m) and @timestamp < ago(15m)

Common Syntax Errors I Made

Error 1: Missing Pipes

# Wrong
fields @timestamp filter @message like /ERROR/

# Correct
fields @timestamp | filter @message like /ERROR/

Error 2: Incorrect String Matching

# Wrong - missing regex delimiters
filter @message like ERROR

# Correct
filter @message like /ERROR/

Error 3: Type Mismatches

# Wrong - comparing string to number
filter duration > "1000"

# Correct
filter duration > 1000

Error 4: Invalid Field Names

# Wrong - field doesn't exist
fields @timestamps

# Correct
fields @timestamp

Key Takeaways

• Six core commands: fields, filter, stats, sort, limit, parse

• Filter early in query for better performance

• Use stats for aggregations and analytics

• parse extracts custom fields from unstructured logs

• JSON logs are automatically parsed

• Time functions like ago() and bin() are essential

• String and number operations provide flexibility

• Use ispresent() to handle optional fields

In Part 3, we'll explore advanced query operations including complex parsing, regular expressions, and time-series analysis for production monitoring.