CloudWatch Logs Insights query syntax

CloudWatch Logs Insights supports a query language you can use to perform queries on your log groups. Each query can include one or more query commands separated by Unix-style pipe characters (|).

Six query commands are supported, along with many supporting functions and operations, including regular expressions, arithmetic operations, comparison operations, numeric functions, datetime functions, string functions, and generic functions.

Comments are also supported. Lines in a query that start with the # character are ignored.

Fields that start with the @ symbol are generated by CloudWatch Logs Insights. For more information about the fields that CloudWatch Logs discovers automatically and generates, see Supported logs and discovered fields.

CloudWatch Logs Insights query commands

The following table lists the six supported query commands along with basic examples. For more powerful sample queries, see Sample queries.

Command	Description	Examples
display	Specifies which fields to display in the query results. If you specify this command more than once in your query, only the fields you specify in the last occurrence are used.	The following example uses the field @message and creates the ephemeral fields loggingType and loggingMessage for use in the query. It filters the events to only those with ERROR as the value of loggingType, but then displays only the loggingMessage field of those events in the results. fields @message | parse @message "[*] *" as loggingType, loggingMessage | filter loggingType = "ERROR" | display loggingMessage
fields	Retrieves the specified fields from log events for display. You can use functions and operations within a fields command to modify field values for display and to create new fields for use in the rest of the query.	The following example displays the fields foo-bar, action, and the absolute value of the difference between f3 and f4 for all log events in the log group. fields `foo-bar`, action, abs(f3-f4) The following example creates and displays an ephemeral field opStatus. The value of opStatus for each log entry is the concatenation of the values of the Operation and StatusCode fields, with a hyphen in between those values. fields concat(Operation, '-', StatusCode) as opStatus
filter	Filters the results of a query based on one or more conditions. You can use a variety of operators and expressions in the filter command. For more information, see Matches and regular expressions in the filter command.	The following example retrieves the fields f1, f2, and f3 for all log events with a value over 2000 in the duration field. fields f1, f2, f3 | filter (duration>2000) The following example also a valid query, but the results don't display separate fields. Instead, the results display the @timestamp and all log data in the @message field for all log events where duration is more than 2000. filter (duration>2000) The following example retrieves the fields f1 and f2 for all log events where f1 is 10 or f3 is more than 25. fields f1, f2 | filter (f1=10 or f3>25) The next example returns log events where the field statusCode has a value between 200 and 299. fields f1 | filter statusCode like /2\d\d/ The next example returns log events that have a statusCode of "300", "400", or "500". fields @timestamp, @message | filter statusCode in [300,400,500] This final example returns log events that do not have Type fields with values of "foo", "bar", or "1". fields @timestamp, @message | filter Type not in ["foo","bar",1]
stats	Calculates aggregate statistics based on the values of log fields. When you use stats, you can also use by to specify one or more criteria to use to group data when calculating the statistics. Several statistical operators are supported, including sum(), avg(), count(), min(), and max().	The following example calculates the average value of f1 for each unique value of f2. stats avg (f1) by f2
sort	Sorts the retrieved log events. Both ascending (asc) and descending (desc) order are supported.	The following example sorts the returned events in descending order based on the value of f1, and displays the fields f1, f2, and f3. fields f1, f2, f3 | sort f1 desc
limit	Specifies the number of log events returned by the query. You can use this to limit the results to a small number to see a small set of relevant results. You can also use limit with a number between 1000 and 10,000 to increase the number of query result rows displayed in the console to an amount greater than the default of 1000 rows. If you don't specify a limit, the query defaults to displaying a maximum of 1000 rows.	The following example sorts the events in descending order based on the value of @timestamp, and displays the fields f1 and f2 for the first 25 events by sort order. In this case, the sort order is by timestamp starting with the most recent, so the most recent 25 events are returned. sort @timestamp desc | limit 25 | display f1, f2
parse	Extracts data from a log field and creates one or more ephemeral fields that you can process further in the query. parse accepts both glob expressions and regular expressions. For glob expressions, provide the parse command with a constant string (characters enclosed in either single or double quotation marks) where each variable piece of text is replaced with an asterisk (*). These are extracted into ephemeral fields and given an alias after the as keyword, in positional order. Enclose regular expressions in forward slashes (/). Within the expression, each part of the matched string that is to be extracted is enclosed in a named capturing group. An example of a named capturing group is (?<name>.*), where name is the name and .* is the pattern.	Using this single log line as an example: 25 May 2019 10:24:39,474 [ERROR] {foo=2, bar=data} The error was: DataIntegrityException The following two parse expressions each do the following: the ephemeral fields level, config, and exception are created. level has a value of ERROR, config has a value of {foo=2, bar=data}, and exception has a value of DataIntegrityException. The first example uses a glob expression, and the second uses a regular expression. parse @message "[*] * The error was: *" as level, config, exception parse @message /\[(?<level>\S+)\]\s+(?<config>\{.*\})\s+The error was: (?<exception>\S+)/ The following example uses a regular expression to extract the ephemeral fields user2, method2, and latency2 from the log field @message and returns the average latency for each unique combination of method2 and user2. parse @message /user=(?<user2>.*?), method:(?<method2>.*?), latency := (?<latency2>.*?)/ | stats avg(latency2) by method2, user2

Notes about query commands in the previous table

The following rules, guidelines, and tips apply to the query commands in the previous table.

• Any log field named in a query that has characters other than the @ sign, the period (.), and alphanumeric characters must be surrounded by backtick (`) characters. For example, the foo-bar field name must be enclosed in backtick characters because it includes a non-alphanumeric character.

• Both fields and display are used to specify the fields to display in the query results. The differences between the two are as follows:

  • You use the display command only to specify which fields to display in the results. You can use the fields command with the as keyword to create new ephemeral fields using functions and the fields that are in the log event. For example, fields ispresent(resolverArn) as isRes creates an ephemeral field named isRes which can be used in the remainder of the query. The value of isRes is either 0 or 1 depending on whether or not resolverArn is a discovered field in the log event.

  • If you have multiple fields commands and do not include a display command, the fields specified in all of the fields commands are displayed.

  • If you have multiple display commands, only the fields specified in the final display command are displayed.

Matches and regular expressions in the filter command

You can use comparison operators (=, !=, <, <=, >, >=), Boolean operators (and, or, and not) and regular expressions in the filter command.

You can use in to test for set membership. Put an array with the elements to check for immediately after in. You can use not with in. String matches using in must be complete string matches.

To filter by substrings, you can use like or =~ (equal sign followed by a tilde) in the filter command. For a substring match using like or =~, enclose your substring to match with double or single quotation marks. To perform regular expression matching, enclose the expression to match with forward slashes. The query returns only log events that match the criteria that you set.

Examples

The following three examples return all events in which f1 contains the word Exception. The first two examples use regular expressions. The third example uses a substring match. All three examples are case sensitive.

fields f1, f2, f3 | filter f1 like /Exception/

fields f1, f2, f3 | filter f1 =~ /Exception/
    

fields f1, f2, f3 | filter f1 like "Exception"

The following example changes the search for "Exception" to not be case sensitive.

fields f1, f2, f3 | filter f1 like /(?i)Exception/ 

The following example uses a regular expression. It returns all events in which f1 is exactly the word Exception. The query isn't case sensitive.

fields f1, f2, f3 | filter f1 =~ /^(?i)Exception$/

Using aliases in queries

You can use as to create one or more aliases in a query. Aliases are supported in the fields, stats, and sort commands.

You can create aliases for log fields and for the results of operations and functions.

Examples

The following examples show the use of aliases in query commands.

fields abs(myField) as AbsoluteValuemyField, myField2

Returns the absolute value of myField as AbsoluteValuemyField and also returns the field myField2.

stats avg(f1) as myAvgF1 | sort myAvgF1 desc

Calculates the average of the values of the f1 as myAvgF1 and returns them in descending order by that value.

Using comments in queries

You can comment out lines in a query by using the # character. Lines that start with the # character are ignored. This can be useful to document your query or to temporarily ignore part of a complex query for one call, without deleting that line.

In the following example, the second line of the query is ignored.

fields @timestamp, @message
    # | filter @message like /delay/
    | limit 20

Supported operations and functions

The query language supports many types of operations and functions, as shown in the following tables.

Comparison operations

You can use comparison operations in the filter command and as arguments for other functions. Comparison operations accept all data types as arguments and return a Boolean result.

= != < <= > >=

Boolean operators

You can use the Boolean operators and, or, and not. You can use these Boolean operators only in functions that return a Boolean value.

Arithmetic operations

You can use arithmetic operations in the filter and fields commands and as arguments for other functions. Arithmetic operations accept numeric data types as arguments and return numeric results.

Operation	Description
a + b	Addition
a - b	Subtraction
a * b	Multiplication
a / b	Division
a ^ b	Exponentiation. 2 ^ 3 returns 8
a % b	Remainder or modulus. 10 % 3 returns 1

Numeric operations

You can use numeric operations in the filter and fields commands and as arguments for other functions. Numeric operations accept numeric data types as arguments and return numeric results.

Operation	Result type	Description
abs(a: number)	number	Absolute value.
ceil(a: number)	number	Round to ceiling (the smallest integer that is greater than the value of a).
floor(a: number)	number	Round to floor (the largest integer that is smaller than the value of a).
greatest(a: number, ...numbers: number[])	number	Returns the largest value.
least(a: number, ...numbers: number[])	number	Returns the smallest value.
log(a: number)	number	Natural log.
sqrt(a: number)	number	Square root.

General functions

You can use general functions in the filter and fields commands and as arguments for other functions.

Function	Result type	Description
ispresent(fieldName: LogField)	boolean	Returns true if the field exists.
coalesce(fieldName: LogField, ...fieldNames: LogField[])	LogField	Returns the first non-null value from the list.

String functions

You can use string functions in the filter and fields commands and as arguments for other functions.

Function	Result type	Description
isempty(fieldName: string)	boolean	Returns true if the field is missing or is an empty string.
isblank(fieldName: string)	boolean	Returns true if the field is missing, an empty string, or contains only white space.
concat(str: string, ...strings: string[])	string	Concatenates the strings.
ltrim(str: string) ltrim(str: string, trimChars: string)	string	If the function does not have a second argument, it removes white space from the left of the string. If the function has a second string argument, it does not remove white space. Instead, it removes the characters in trimChars from the left of str. For example, ltrim("xyZxyfooxyZ","xyZ") returns "fooxyZ".
rtrim(str: string) rtrim(str: string, trimChars: string)	string	If the function does not have a second argument, it removes white space from the right of the string. If the function has a second string argument, it does not remove white space. Instead, it removes the characters of trimChars from the right of str. For example, rtrim("xyZfooxyxyZ","xyZ") returns "xyZfoo".
trim(str: string) trim(str: string, trimChars: string)	string	If the function does not have a second argument, it removes white space from both ends of the string. If the function has a second string argument, it does not remove white space. Instead, it removes the characters of trimChars from both sides of str. For example, trim("xyZxyfooxyxyZ","xyZ") returns "foo".
strlen(str: string)	number	Returns the length of the string in Unicode code points.
toupper(str: string)	string	Converts the string to uppercase.
tolower(str: string)	string	Converts the string to lowercase.
substr(str: string, startIndex: number) substr(str: string, startIndex: number, length: number)	string	Returns a substring from the index specified by the number argument to the end of the string. If the function has a second number argument, it contains the length of the substring to be retrieved. For example, substr("xyZfooxyZ",3, 3) returns "foo".
replace(str: string, searchValue: string, replaceValue: string)	string	Replaces all instances of searchValue in str with replaceValue. For example, replace("foo","o","0") returns "f00".
strcontains(str: string, searchValue: string)	number	Returns 1 if str contains searchValue and 0 otherwise.

Datetime functions

You can use datetime functions in the filter and fields commands and as arguments for other functions. You can use these functions to create time buckets for queries with aggregate functions.

As part of datetime functions, you can use time periods that consist of a number and then either m for minutes or h for hours. For example, 10m is 10 minutes and 1h is 1 hour.

Function	Result type	Description
bin(period: Period)	Timestamp	Rounds the value of @timestamp to the given period and then truncates.
datefloor(timestamp: Timestamp, period: Period)	Timestamp	Truncates the timestamp to the given period. For example, datefloor(@timestamp, 1h) truncates all values of @timestamp to the bottom of the hour.
dateceil(timestamp: Timestamp, period: Period)	Timestamp	Rounds up the timestamp to the given period and then truncates. For example, dateceil(@timestamp, 1h) truncates all values of @timestamp to the top of the hour.
fromMillis(fieldName: number)	Timestamp	Interprets the input field as the number of milliseconds since the Unix epoch and converts it to a timestamp.
toMillis(fieldName: Timestamp)	number	Converts the timestamp found in the named field into a number representing the milliseconds since the Unix epoch.

IP address functions

You can use IP address string functions in the filter and fields commands and as arguments for other functions.

Function	Result type	Description
isValidIp(fieldName: string)	boolean	Returns true if the field is a valid IPv4 or IPv6 address.
isValidIpV4(fieldName: string)	boolean	Returns true if the field is a valid IPv4 address.
isValidIpV6(fieldName: string)	boolean	Returns true if the field is a valid IPv6 address.
isIpInSubnet(fieldName: string, subnet: string)	boolean	Returns true if the field is a valid IPv4 or IPv6 address within the specified v4 or v6 subnet. When you specify the subnet, use CIDR notation such as 192.0.2.0/24 or 2001:db8::/32.
isIpv4InSubnet(fieldName: string, subnet: string)	boolean	Returns true if the field is a valid IPv4 address within the specified v4 subnet. When you specify the subnet, use CIDR notation such as 192.0.2.0/24.
isIpv6InSubnet(fieldName: string, subnet: string)	boolean	Returns true if the field is a valid IPv6 address within the specified v6 subnet. When you specify the subnet, use CIDR notation such as 2001:db8::/32.

Stats aggregation functions

You can use aggregation functions in the stats command and as arguments for other functions.

Function	Result type	Description
avg(fieldName: NumericLogField)	number	The average of the values in the specified field.
count() count(fieldName: LogField)	number	Counts the log events. count() (or count(*)) counts all events returned by the query, while count(fieldName) counts all records that include the specified field name.
count_distinct(fieldName: LogField)	number	Returns the number of unique values for the field. If the field has very high cardinality (contains many unique values), the value returned by count_distinct is just an approximation.
max(fieldName: LogField)	LogFieldValue	The maximum of the values for this log field in the queried logs.
min(fieldName: LogField)	LogFieldValue	The minimum of the values for this log field in the queried logs.
pct(fieldName: LogFieldValue, percent: number)	LogFieldValue	A percentile indicates the relative standing of a value in a dataset. For example, pct(@duration, 95) returns the @duration value at which 95 percent of the values of @duration are lower than this value, and 5 percent are higher than this value.
stddev(fieldName: NumericLogField)	number	The standard deviation of the values in the specified field.
sum(fieldName: NumericLogField)	number	The sum of the values in the specified field.

Stats non-aggregation functions

You can use non-aggregation functions in the stats command and as arguments for other functions.

Function	Result type	Description
earliest(fieldName: LogField)	LogField	Returns the value of fieldName from the log event that has the earliest timestamp in the queried logs.
latest(fieldName: LogField)	LogField	Returns the value of fieldName from the log event that has the latest timestamp in the queried logs.
sortsFirst(fieldName: LogField)	LogField	Returns the value of fieldName that sorts first in the queried logs.
sortsLast(fieldName: LogField)	LogField	Returns the value of fieldName that sorts last in the queried logs.