amazon web services - AWS Cloudwatch Filter and Pattern Syntax Issue

Question

I followed the instructions here https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/FilterAndPatternSyntax.html

but it's not working as i'm expecting it to. I'm trying to create following pattern [..., Query_time>1800,], but not working. I need the logs having query time greater than 1800.

Below is the cloudwatch log:

# Time: 210126  5:31:49 # User@Host: et_user[et_user] @  [127.0.0.1]  Id: 458426829 # Query_time: 6.408787  Lock_time: 0.000206 Rows_sent: 1  Rows_examined: 19439654 SET timestamp=1611639109; SELECT                                 COUNT(DISTINCT v.customer_id) AS total_registrations,                                 COUNT(r.id) AS total_savings,                                 SUM(r.savings_estimate) AS total_estimated_savings                             FROM web_ten.`validation` v                                 LEFT OUTER JOIN web_ten.`savings` r ON v.user_id = r.user_id AND r.status=1                                     AND r.company LIKE 'ABDSDF%'                                     LEFT OUTER JOIN `web_ten`.outlet AS o ON r.outlet_id = o.id                                     AND o.user_id NOT IN (SELECT id FROM `web_ten`.`user` WHERE is_tutorial = 1)                             WHERE                                   v.isused = 1 AND no_company = 'ABDSDF'                                    AND v.email NOT IN (                                         SELECT `email` FROM `web_ten`.excluded_demo_emails                                     );
# User@Host: et_user_test[et_user_test] @  [127.0.0.1]  Id: 453140660 # Query_time: 2.018429  Lock_time: 0.000051 Rows_sent: 0  Rows_examined: 743405 use production; SET timestamp=1611639131; UPDATE `web_ten`.`pm_user_order` SET `cron_processing`=0 WHERE `id` > 0;

question from:https://stackoverflow.com/questions/65890168/aws-cloudwatch-filter-and-pattern-syntax-issue

Answer 1

In this case you are extracting values from Space-Delimited Log Events so you have to define every field based on the blank spaces. The filter pattern you should use is:

[,field2 = *Query*, query_time_value>1800,...]

Explanation:

• The first field (text before the first space) is irrelevant in the filter so is not declared
• The second field needs to be filtered as Query to ensure that only the desired logs should be included in the filter
• The third field is the current value of the query time, so here is the filter ( >1800)
• The next fields are not needed so are ignored using "..."

In this way your metric filter should work. I have tested based on your logs and it is the result:

Test result metric filter

Update

According to your log structure you print querytime in 2 different formats. You would need 2 differents filter patterns:

[,,,,,,,,,,,,f13=*Query*,querytimevalue>18,...]

and

[,,,,,,,,f9=*Query*,querytimevalue>18,...]

Another easier option to filter this is using CloudWatch Insights and filter with this query:

parse @message '*Query_time: * *' as f1, querytime, f2
| display querytime
| filter querytime > 18

Consider that CloudWatch Insigths does not allow you to create metrics or alarms based on this query but you can see it in a dashboard.