WHERE with regex in CW ppl

Question

How can i use a regex in a where clause, in log insights using OpenSearch ppl new feature:

where message like '%adsets/{id}/offers%'

i tried like this on some api gateway logs but i get this error:

InvalidParameterException: Failed to parse 'search source = `logGroups(logGroupIdentifier:['API-Gateway-Execution-Logs_qsqns5ug51/ro'])` | where message like '%adsets/{id}/offers%' ' due to no viable alternative at input 'message like'

and if i use == it does not find anything (and it should as i tested it with QL)
I want to find all logs which have 'adsets/{id}/offers' in @message.

re:Post Agent · Answer

When using OpenSearch PPL in CloudWatch Logs Insights, you need to use the correct syntax for regex pattern matching. For regex operations in PPL, you should use the `=~` operator rather than `like`.

Try this syntax instead:

```
search source = `logGroups(logGroupIdentifier:['API-Gateway-Execution-Logs_qsqns5ug51/ro'])` | where message =~ /adsets\/\{id\}\/offers/
```

Note that in regex patterns, you need to escape forward slashes with backslashes. The pattern is enclosed in forward slashes (`/pattern/`).

If you specifically want to use a wildcard-style pattern rather than a full regex, you can use the `like` operator with string patterns instead of regex patterns:

```
search source = `logGroups(logGroupIdentifier:['API-Gateway-Execution-Logs_qsqns5ug51/ro'])` | where message like "*adsets/{id}/offers*"
```

In PPL, the wildcard character is `*` (asterisk) rather than `%` which is used in SQL-like syntaxes.

Remember that OpenSearch PPL can only be used for queries of log groups in the Standard Log Class.  
**Sources**  
[OpenSearch PPL language - Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_AnalyzeLogData_PPL.html)  
[filter - Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax-Filter.html)  
[Filter pattern syntax for metric filters, subscription filters, filter log events, and Live Tail - Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/FilterAndPatternSyntax.html)

WHERE with regex in CW ppl

Add your answer

Relevant content