Advanced analytics using Amazon CloudWatch Logs Insights

Effective log management and analysis are critical for maintaining robust, secure, and high-performing systems. Amazon CloudWatch Logs Insights has long been a powerful tool for searching, filtering, and analyzing log data across multiple log groups. The addition of OpenSearch Piped Processing Language (PPL) and OpenSearch SQL language query support offers greater flexibility and familiarity in log analysis.

Whether you’re a developer troubleshooting application errors, a security analyst investigating potential threats, or an operations manager monitoring system performance, these enhancements allow you to:
· Use generative AI to reduce time-to-insight with natural language query generation and summarization
· Seamlessly correlate logs using powerful JOIN operations
· Utilize a wide range of functions for sophisticated data manipulation
· Correlate data cross multiple log sources

This post will guide you through the key capabilities from OpenSearch PPL and SQL to query within CloudWatch Logs Insights. You’ll learn how to optimize searches, correlate data, and analyze query results using a natural language summary. Additionally, this post will explore CloudWatch Logs Insights Query Language command for on-demand anomaly detection, further enhancing your ability to quickly identify and respond to unusual patterns in your logs.

Key capabilities from OpenSearch PPL and SQL within CloudWatch Logs Insights

CloudWatch Logs Insights has expanded query options with the addition of OpenSearch PPL and OpenSearch SQL support. You now have more choices in how you interact with your log data, catering to different skill sets and preferences within your team. These enhancements bring several key advantages that address common challenges in log analysis:

1. Familiar query languages such as SQL let you start analyzing logs more quickly, without extensive training.

Example: Instead of learning a proprietary syntax, you can use standard SQL queries like:

SELECT eventName, COUNT(*) as count
FROM loggroupname
GROUP BY eventName
ORDER BY count DESC
LIMIT 10

2. Enhanced correlation capabilities, allow you to join data from different log groups for comprehensive insights.

Example: Correlate application errors with infrastructure logs:

SELECT a.transaction_id,
       a.error_message AS application_error,
       a.timestamp AS application_timestamp
       i.error_message AS infrastructure_error,
       i.timestamp AS infrastructure_timestamp,
FROM application_logs a
LEFT JOIN infrastructure_logs i ON a.transaction_id = i.transaction_id

3. Rich Function Library lets you do sophisticated processing and analysis. Built-in functions for JSON parsing, math operations, and string processing enable deeper analysis without additional tools. Get the full list here.

Example: Parse and analyze JSON log data to extract user_id from the logs, and analyze their average response times:

SELECT
JSON_EXTRACT_SCALAR(message, '$.user_id') as user_id,
AVG(CAST(JSON_EXTRACT_SCALAR(message, '$.response_time') AS DOUBLE)) as avg_response_time
FROM loggroupname
GROUP BY JSON_EXTRACT_SCALAR(message, '$.user_id')

4. Complex query support enables you to perform subqueries and advanced aggregations for nuanced log analysis.

Example: Find unusual API usage patterns, where the calls are 2 times the average:

SELECT user_id, api_calls
FROM (
   SELECT user_id, COUNT(*) as api_calls
   FROM api_logs
   GROUP BY user_id
) subquery
WHERE api_calls > (SELECT AVG(api_calls) * 2 FROM subquery)

Pre-requisites

To get started with advanced analytics using CloudWatch Logs Insights with OpenSearch PPL and SQL support, you need to set up your environment. Follow the steps outlined in the blog post, New Amazon CloudWatch and Amazon OpenSearch Service launch an integrated analytics experience.This setup will ensure you have permissions and configurations in place to use the CloudWatch Logs Insights with OpenSearch PPL and SQL support.

Faster query generation and results analysis using generative AI

The integration of generative AI into query generation and result summarization accelerates the path from raw data to actionable insights.

Natural Language Query Generation:

CloudWatch logs insights, OpenSearch PPL, and OpenSearch SQL allow you to quickly generate queries in the context of their logs without needing extensive knowledge of the query language, reducing time to gather insights.

For example, you can review the AWS CloudTrail Logs that are sent to a CloudWatch Log group to “Get api count by eventSource and eventName and sort it by most to least”. CloudWatch will generate the query for you, and you can review the output to identify which API calls have occurred.

Fig 1. Log Insights QL using natural language to generate the query

In the Log Insights QL using natural language to generate the query image above, we’re selecting Logs Insights QL and using the query generator to run “Get api count by eventSource and eventName and sort it by most to least” to generate a Log Insights QL query using natural language.

Fig 2. OpenSearch PPL using natural language to generate the query

In the OpenSearch PPL using natural language to generate the query image above, we’re selecting OpenSearch PPL and using the query generator to run “Get api count by eventSource and eventName and sort it by most to least” to generate a OpenSearch PPL query using natural language.

Fig 3. OpenSearch SQL using natural language to generate the query

In the OpenSearch SQL using natural language to generate the query above, we’re selecting OpenSearch SQL and using the query generator to run “Get api count by eventSource and eventName and sort it by most to least” to generate a OpenSearch SQL query using natural language.

Log query summarization:

CloudWatch Logs query results can be automatically converted into clear, natural language summaries using generative AI across CloudWatch Logs Insights, OpenSearch PPL and SQL. Instead of manually analyzing hundreds or thousands of log entries, this summarization capability distills your query output into concise, easily digestible explanations. The AI examines patterns and key information in the logs to produce a human-readable summary that helps you quickly understand the core findings and identify potential issues requiring attention.

Fig 4. Query results are summarized into natural language image below uses the output from the CloudTrail queries used above to provide a simplified explanation of the query results.

Fig 4. Query results are summarized into natural language

CloudWatch Log Insights on-demand anomaly detection

CloudWatch Logs Insights offers a powerful command for on-demand anomaly detection, providing users with the ability to quickly identify unusual patterns in their logs without complex setup or configuration. This allows you to pinpoint potential issues, reducing mean time to detection (MTTD) and improving overall system reliability.

You can customize your analysis using flexible time ranges, choosing between relative or absolute comparison periods to match your needs. This adaptability makes it possible to perform precise, targeted investigations. This differs from CloudWatch Log Anomalies which is defined on an individual CloudWatch Log Standard log class group using a log anomaly detector that requires a specific evaluation frequency, filter patterns, and maximum anomaly visibility period for the detector.

Common use cases for CloudWatch Log Insights on-demand anomaly detection include:
1. Security monitoring: Detect unusual access patterns or authentication failures.
2. Application health: Identify spikes in error rates or latency.
3. Infrastructure management: Spot unusual resource utilization patterns.

To use the on-demand anomaly command:
1. Go to CloudWatch Logs Insights and select your log group.
2. Use the pattern command to @message to identify log patterns
3. Pipe to the anomaly command to detect unusual patterns
4. Optional, use the compare function to analyze patterns across different time ranges

On-demand anomaly example:

pattern @message | anomaly

You are provided with five fields:
@description – the description of the anomaly.
@anomalyLogSamples – are samples of the anomaly log events.
@priority – an opinionated priority status to review based on log samples.
@priorityScore – an opinionated priority score based on the priority.
@patternString – patterns found in the logs with the different tokens removed.

Fig 5. Pattern Anomalies based on the current and previous 30-minute period

This feature transforms how you interact with your logs, moving from reactive troubleshooting to proactive issue detection and resolution. Whether you’re managing a small application or a large-scale distributed system, the on-demand anomaly detection command in CloudWatch Logs Insights provides a powerful tool for maintaining system health and performance.

Correlation

CloudWatch Logs Insights’ support for JOINs and sub-queries enables correlation capabilities across multiple log groups, deepening insights and allowing more comprehensive troubleshooting. These features allow you to easily track end-to-end transaction flows and troubleshoot issues across service boundaries.

You can use subqueries to filter results based on aggregated data or comparing against a single value. For example, “find records where X meets condition Y”. You can also use JOINs when combining data from multiple tables, working with large datasets, performing complex operations across tables.

1. Using Subqueries
Subqueries help you find relationships between services, such as matching completed payments with their original orders.

OpenSearch SQL Example – Finding Orders with Completed Payments:

SELECT correlationId, timestamp
FROM `/orders/prod` o
WHERE EXISTS (
    SELECT 1
    FROM `/payments/prod` p
    WHERE p.correlationId = o.correlationId
    AND p.paymentStatus = 'completed'
)
ORDER BY timestamp DESC
LIMIT 10;

Equivalent OpenSearch PPL Example:

fields timestamp, correlationId 
| where `correlationId` IN [ 
    search source=`/payments/prod` 
    | where paymentStatus="completed" 
    | fields `correlationId`
] 
| head 10

Example output:

{
  "timestamp": "2025-07-31T16:24:45.302015Z",
  "correlationId": "CORD08B8DF1"
}

2. Using JOINs
JOINs allow you to combine order and payment information for a complete view of transactions.

OpenSearch SQL JOIN Example:

SELECT o.correlationId, o.orderId, p.paymentStatus, p.paymentMethod, p.amount 
FROM `/orders/prod` o 
INNER JOIN `/payments/prod` p ON o.correlationId = p.correlationId 
ORDER BY o.timestamp DESC 
LIMIT 100;

OpenSearch PPL JOIN Example:

LEFT JOIN left=o, right=p on o.correlationId = p.correlationId `/payments/prod`
| fields o.correlationId, o.orderId, p.paymentStatus, p.paymentMethod, p.amount
| head 10

Example output:

{
  "correlationId": "CORD08B8DF1",
  "orderId": "ORD76175D",
  "paymentStatus": "completed",
  "paymentMethod": "paypal",
  "amount": 113.43
}

When using correlation, you want to include the appropriate time windows when correlating data, indexing frequently queried fields, considering the query performance with large datasets, and handling missing or null correlation IDs. To optimize your queries for performance, especially when dealing with large volumes of log data; regularly review and refine your saved queries to ensure they continue to provide valuable insights as your systems evolve; leverage AI-powered features to accelerate your analysis to verify critical findings; and use the on-demand anomaly detection feature to catch potential issues before they impact your users.

Conclusion

These enhancements within CloudWatch Log Insights for OpenSearch PPL and SQL query allow for more efficient and effective log management and analysis, whether you’re conducting security audits, performance optimization, or troubleshooting.

Learn more about advanced log analytics in Amazon CloudWatch documentation, and start exploring the new possibilities that OpenSearch PPL and OpenSearch SQL bring to your CloudWatch Logs Insights experience today.

Authors