AWS Log Insight Query – Generate Count of Unique Errors in Log Stream with Subquery to Dig Down into Exceptions

This was a cool query to write.

It does the following in AWS CloudWatch using Log Insights query engine:

  1. Parse all @messages for exceptions/errors/etc. and generates unique errors via removal of numerics
  2. Generates a count of how many of this error type is occurring
  3. Generates a sub query that can be copy pasted to dive into the results behind that count

# 1. ErrorCount Column shows the count for this unique error type across all log messages

# 2. LogMessage Column shows the unique error with numerics removed to show how many 
#    times this type of error is occuring across all logs

# 3. QueryString Column is a column that generates a query that can be copy pasted into Log Insights
#    and used as a follow up query to dig into the exceptions and allow for stack trace analysis 
#    across all occurences of the errors 

# 3A. The query that is generated will work most of the time but in some instances will require 
#     that you search only part of it due to no support for wildcards in log insights.
#     Generated Query:
#     - fields @timestamp, @message, @logStream 
#       | filter @message like "Error with . asdf extra things but numerics have been botched"
#     Example to Fix from Above Filter:
#     - "Error with . asdf extra things but numerics have been botched"
#     Example of Better Query Syntax Revision:
#     - "asdf extra things but numerics have been botched"
#     Final Query for Usage:
#     - fields @timestamp, @message, @logStream 
#       | filter @message like "asdf extra things but numerics have been botched"

#Generate Count of Unique Errors - The replace below removes all numerics to generate a unique error
stats count(*) as ErrorCount by replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@message, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", "") as LogMessage, 

#Generate Query String for Diving into Results - Copy Pastable
concat(concat('fields @timestamp, @message, @logStream | filter @message like "', replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@message, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""), " ", " ")),'"') as QueryString_For_Log_Analysis

#Specify the Log Stream Environment if Multiple Environments Exist - (?i) makes it case insensitive
| filter @logStream like /(?i)MyCoolApplicationLogStream/ 

#Specify the Log Criteria - Example below covers exception, caused by, error
| filter @message like /(?i)exception/ or @message like /(?i)caused by/ or @message like /(?i)error/
| display ErrorCount, LogMessage, QueryString_For_Log_Analysis
| sort by ErrorCount desc

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s