How can I receive SNS notifications about Amazon RDS for SQL Server error and agent log events that match a CloudWatch filter pattern?

Short description

First, publish SQL Server logs to Amazon CloudWatch Logs. Then, create CloudWatch alarms and SNS notifications for the SQL Server DB log events that match the filter pattern that you create.

After the Amazon RDS for SQL Server log data is published to CloudWatch, you can create metric filters to search and filter the logs. Metric filters define the terms and patterns that are searched for in the log data as it is sent to Amazon CloudWatch Logs. Then, CloudWatch Logs uses the metric filters to turn log data into numerical CloudWatch metrics that you can set alarms for. You can use any type of CloudWatch statistic, including percentile statistics, when viewing these metrics or setting alarms. For more information, see Creating metrics from log events using filters.

Resolution

You can create filter patterns, such as "Login failed for user" to identify failed login attempts. Or, you can create a filter for "I/O requests taking longer than 15 seconds to complete" to identify I/O bottlenecks or disk bottlenecks.

The following example uses a metric filter to search for and count events that include the term "Login failed for user" to track failed login attempts.

1. Open the Amazon RDS console, and choose Databases from the navigation pane.
2. Choose the name of the DB instance that you want to configure CloudWatch metrics for.
3. From the Configuration tab, choose Error under CloudWatch Logs. This opens the Logs pane in the CloudWatch console. If you don't see a Published Logs section, be sure that you are publishing for SQL Server logs to CloudWatch Logs.
4. From the CloudWatch console navigation pane, choose Log groups.
5. Choose the error log for the RDS DB instance, and then choose Create Metric Filter.
6. For Filter Pattern, enter a pattern, such as "Login failed for user."
   Note: All entries in the filter pattern are case sensitive.
7. From the same page, you can test the filter pattern. Select the log group that you want to test the filter against under the Select Log Data to Test section, and then choose Test Pattern.
8. From the Results section, you can see how many results match the filter pattern in the log file. You can also choose Show test results to see more details.
9. Choose Assign Metric.
10. Enter a Filter name and a Metric name.
11. Choose Show advanced metric settings, and confirm that Metric Value is set to 1. This value specifies that the count is incremented by 1 for each log event that contains "Login Failed for User."
12. For Default Value, enter 0.
13. Choose Create Filter. A Create Alarm button displays. Choose Create Alarm.
14. From the Period dropdown list, select the evaluation period. The default period is 5 minutes.
15. From the Conditions section, choose the following:
    Threshold type - Static.
    Whenever test is - Greater > threshold.
    Than - Enter 10.
16. Expand the Additional configuration section. For Datapoints to alarm, enter the number of evaluation periods (data points) that must be in the ALARM state to trigger the alarm.
17. From Missing data treatment, select Treat missing data as missing.
18. Choose Next.
19. From the Notification section, under Select an SNS topic, select an existing SNS topic, or create a new topic to receive notifications.
20. Enter the email endpoints that you want to receive the notification.
21. Choose Next.
22. Enter the Alarm name and Alarm description, and then choose Next.
23. From the Preview and create page, review the Metric graph and Conditions.
24. Choose Create alarm.

After following these example steps, the alarm state is Insufficient data for the first few minutes. After the alarm has enough data, the status is OK. If you receive more than 10 login failures within a 5-minute period, then the CloudWatch alarm sends an SNS notification to the email that you specified.

Related information

Overview of monitoring metrics in Amazon RDS