134

how do I query with contains string in AWS Log insights

fields @timestamp, @message
filter @message = "user not found"
| sort @timestamp desc
| limit 20

fields @timestamp, @message
filter @message strcontains("User not found")
| sort @timestamp desc
| limit 20
Punit Vora
  • 5,052
  • 4
  • 35
  • 44
kumar
  • 8,207
  • 20
  • 85
  • 176

5 Answers5

200

This should work fine

fields @timestamp, @message
| filter @message like /user not found/
| sort @timestamp desc
| limit 20
David Buck
  • 3,752
  • 35
  • 31
  • 35
Parichit Choubisa
  • 2,016
  • 1
  • 5
  • 3
  • 3
    For clarity, the forward-slashes encase the string you're searching for. For instance, I was looking for HTTP 500 errors, so my string looked like: `| filter @message like / 500 /` – dKen Aug 07 '22 at 07:08
31

I recently ran into the same scenario. strcontains takes the input string as the first argument and the search value as the second. so in your case the following should work fine.

fields @timestamp, @message
| filter strcontains(@message, "User not found")
| sort @timestamp desc
| limit 20
sash
  • 1,124
  • 2
  • 15
  • 32
26

I think you need to select them as fields and then filter on their value. e.g:

fields @timestamp, @message, strcontains(@message, "user not found") AS unf
| filter unf=1
| sort @timestamp desc
| limit 20

Or use regex

fields @timestamp, @message
| filter @message like /User\snot\sfound/
| ...

(haven't tested them)

Carlos
  • 2,883
  • 2
  • 18
  • 19
15

I was looking for contains and in filters. Allowed filtering options are:

'in', 'and', 'or', 'not', 'like', '=~', '~=', '|', '|>', '^', '*', '/', '%', '+', '-', '<', '>', '<=', '>=', '=', '!='

So the solution using like seems also the optimal version in terms of operator.

fields @timestamp, @message
| filter @message like /user not found/
| sort @timestamp desc
| limit 20

Nevertheless there's another possibility to parse the message itself and do an equal comparison for use cases where one needs to be more exact. For formatted log rows like:

2020-12-24T19:08:18.180+01:00 [main] INFO com.foo.bar.FooBar - My log message!

You can parse substrings from the message and assign them to a field which can then be filtered using equal operator ("="). In the example below you can see no "INFO" String in the message can interfere with filtering severity:

fields @timestamp, @message
| parse @message "[*] * *" as @level, @severity, @info
| filter @logStream like "my/stream/within/loggroup"
| filter @severity="INFO"
| sort @timestamp desc
| limit 20
supernova
  • 1,762
  • 1
  • 14
  • 31
  • Nice post! Is there a way to have OR expression for the "like" operator? ex.: "filter message like /aaa/ or message like /bbb/ " or "filter message in like ("aaa", "bbb") ? – denu Jan 20 '23 at 10:11
  • Yes, definitely. Here‘s some good examples what you can do from AWS: https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax-examples.html – supernova Jan 21 '23 at 20:30
1

Different ways to check if message contains substring/text in AWS Log Insights

1. Using LIKE clause(Documentation)
fields @timestamp, @message
| filter @message like /user not found/
| sort @timestamp desc
| limit 20
2. Using strcontains string method(Documentation)
fields @timestamp, @message
| filter strcontains(@message, "User not found")
| sort @timestamp desc
| limit 20
3. Using regex string method
fields @timestamp, @message
| filter @message =~ /.*User not found.*/
| sort @timestamp desc
| limit 20
Erick Mwazonga
  • 1,020
  • 12
  • 25