# Events, logs, and audit trails Monitoring [DB instance metrics](db-instance-monitoring.md) and [OS metrics](os-monitoring.md), analyzing the trends and comparing metrics to baseline values, and generating alerts when values breach defined thresholds are all necessary and best practices that help you achieve and maintain the reliability, availability, performance, and security of your Amazon RDS DB instances. However, a complete solution must also monitor database events, log files, and audit trails of MySQL and MariaDB databases. **Sections** + [Amazon RDS events](rds-events.md) + [Database logs](database-logs.md) + [Audit trails](audit-trails.md) # Amazon RDS events An *Amazon* *RDS event* indicates a change in the Amazon RDS environment. For example, when the DB instance status changes from *Starting* to *Available*, Amazon RDS generates the event `RDS-EVENT-0088 The DB instance has been started`. Amazon RDS delivers events to Amazon EventBridge in near real time. You can access events though the Amazon RDS console, the AWS CLI command [describe-events](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/rds/describe-events.html), or the Amazon RDS API operation [DescribeEvents](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeEvents.html). The following screen illustration shows events and logs displayed on the Amazon RDS console. ![\[Alarms, events, and logs displayed on the Amazon RDS console\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/amazon-rds-monitoring-alerting/images/alarms-events-logs-rds-console.png) Amazon RDS emits different types of events, including DB instance events, DB parameter group events, DB security group events, DB snapshot events, RDS Proxy events, and blue/green deployment events. The information includes: + Source name and source type; for example: `"SourceIdentifier": "database-1", "SourceType": "db-instance"` + Date and time of the event; for example: `"Date": "2022-12-01T09:20:28.595000+00:00"` + Message associated with the event; for example: `"Message": "Finished updating DB parameter group"` + Event category; for example: `"EventCategories": ["configuration change"]` For a complete reference, see [Amazon RDS event categories and event messages](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.Messages.html) in the Amazon RDS documentation. We recommend that you monitor Amazon RDS events, because these events indicate status changes in the availability of DB instances, configuration changes, read replica status changes, backup and recovery events, failover actions, failure events, modifications to security groups, and many other notifications. For example, if you have set up a read replica DB instance to provide enhanced performance and durability for your database, we recommend that you monitor Amazon RDS events for the *read replica* event category associated with DB instances. This is because events such as `RDS-EVENT-0057 Replication on the read replica was terminated` indicate that your read replica is no longer synchronizing with the primary DB instance. A notification to the responsible team that such an event has happened could help timely mitigation of the issue. Amazon EventBridge and additional AWS services, such as AWS Lambda, Amazon Simple Queue Service (Amazon SQS), and Amazon Simple Notification Service (Amazon SNS), can help you automate responses to system events such as database availability issues or resource changes. On the Amazon RDS console, you can retrieve events from the past 24 hours. If you use the AWS CLI or the Amazon RDS API to view events, you can retrieve events from the past 14 days by using the **describe-events** command as follows. ``` $ aws rds describe-events --source-identifier database-1 --source-type db-instance { "Events": [ { "SourceIdentifier": "database-1", "SourceType": "db-instance", "Message": "CloudWatch Logs Export enabled for logs [audit, error, general, slowquery]", "EventCategories": [], "Date": "2022-12-01T09:20:28.595000+00:00", "SourceArn": "arn:aws:rds:eu-west-3:111122223333:db:database-1" }, { "SourceIdentifier": "database-1", "SourceType": "db-instance", "Message": "Finished updating DB parameter group", "EventCategories": [ "configuration change" ], "Date": "2022-12-01T09:22:40.413000+00:00", "SourceArn": "arn:aws:rds:eu-west-3:111122223333:db:database-1" } ] } ``` If you want to store events for the long term, either until the specified expiration period or permanently, you can use [CloudWatch Logs](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/WhatIsCloudWatchLogs.html) to log the information about the events that were generated by Amazon RDS. To implement this solution, you can use an Amazon SNS topic to receive Amazon RDS event notifications and then call a Lambda function to log the event in CloudWatch Logs. 1. Create a Lambda function that will be called on the event and log the information from the event to CloudWatch Logs. CloudWatch Logs is integrated with Lambda and provides a convenient way to log event information, by using the **print** function to `stdout`. 1. Create an SNS topic with a subscription to a Lambda function (set **Protocol** to Lambda), and set the **Endpoint** to the Amazon Resource Name (ARN) of the Lambda function that you created in the previous step. 1. Configure your SNS topic to receive Amazon RDS event notifications. For detailed instructions, see the [AWS re:Post article](https://repost.aws/knowledge-center/sns-topics-rds-notifications) on how to get your Amazon SNS topic to receive Amazon RDS notifications. 1. On the Amazon RDS console, create a new event subscription. Set **Target** to the ARN, and then select the SNS topic you previously created. Set **Source type** and **Event categories to include** according to your requirements. For more information, see [Subscribing to Amazon RDS event notification](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.Subscribing.html) in the Amazon RDS documentation. # Database logs MySQL and MariaDB databases generate logs that you can access for auditing and troubleshooting. Those logs are: + [Audit](https://mariadb.com/kb/en/mariadb-audit-plugin-log-format/) – The audit trail is a set of records that log the server's activity. For each client session, it records who connected to the server (user name and host), which queries were run, which tables were accessed, and which server variables were changed. + [Error](https://dev.mysql.com/doc/refman/8.0/en/error-log.html) – This log contains the server's (`mysqld`) startup and shutdown times, and diagnostic messages such as errors, warnings, and notes that occur during server startup and shutdown, and while the server is running. + [General](https://dev.mysql.com/doc/refman/8.0/en/query-log.html) – This log records the activity of `mysqld`, including the connect and disconnect activity for each client, and SQL queries received from clients. The general query log can be very useful when you suspect an error and want to know exactly what the client sent to `mysqld`. + [Slow query](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html) – This log provides a record of SQL queries that took a long time to perform. As a best practice, you should [publish database logs from Amazon RDS to Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Procedural.UploadtoCloudWatch.html). With CloudWatch Logs, you can perform real-time analysis of the log data, store the data in highly durable storage, and manage the data with the CloudWatch Logs agent. You can [access and watch your database logs](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Procedural.Watching.html) from the Amazon RDS console. You can also use CloudWatch Logs Insights to interactively search and analyze your log data in CloudWatch Logs. The following example illustrates a query on the audit log that checks how many times `CONNECT` events appear in the log, who connected, and which client (IP address) they connected from. The excerpt from the audit log could look like this: ``` 20221201 14:07:05,ip-10-22-1-51,rdsadmin,localhost,821,0,CONNECT,,,0,SOCKET 20221201 14:07:05,ip-10-22-1-51,rdsadmin,localhost,821,0,DISCONNECT,,,0,SOCKET 20221201 14:12:20,ip-10-22-1-51,rdsadmin,localhost,822,0,CONNECT,,,0,SOCKET 20221201 14:12:20,ip-10-22-1-51,rdsadmin,localhost,822,0,DISCONNECT,,,0,SOCKET 20221201 14:17:35,ip-10-22-1-51,rdsadmin,localhost,823,0,CONNECT,,,0,SOCKET 20221201 14:17:35,ip-10-22-1-51,rdsadmin,localhost,823,0,DISCONNECT,,,0,SOCKET 20221201 14:22:50,ip-10-22-1-51,rdsadmin,localhost,824,0,CONNECT,,,0,SOCKET 20221201 14:22:50,ip-10-22-1-51,rdsadmin,localhost,824,0,DISCONNECT,,,0,SOCKET ``` The example Log Insights query shows that `rdsadmin` connected to the database from `localhost` every 5 minutes, for a total of 22 times, as shown in the following illustration. These results indicate that the activity originated from internal Amazon RDS processes such as the monitoring system itself. ![\[Log Insights report\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/amazon-rds-monitoring-alerting/images/log-insights.png) Log events frequently include important messages that you want to count, such as warnings or errors about operations associated with MySQL and MariaDB DB instances. For example, if an operation fails, an error may occur and be recorded to the error log file as follows: `ERROR 1114 (HY000): The table zip_codes is full`. You might want to monitor these entries to understand the trend of your errors. You can [create custom CloudWatch metrics from Amazon RDS logs by using filters](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CreateMetricFilterProcedure.html) to enable automatic monitoring of Amazon RDS database logs to monitor a specific log for specific patterns, and to generate an alarm if there are violations of expected behavior. [For example](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CountOccurrencesExample.html), create a metric filter for the log group `/aws/rds/instance/database-1/error` that would monitor the error log and search for the [specific pattern](https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/FilterAndPatternSyntax.html), such as `ERROR`. Set the **Filter Pattern** to `ERROR` and **Metric Value** to `1`. The filter will detect every log record that has the keyword `ERROR`, and it will increment the count by 1 for every log event that contains "ERROR". After you create the filter, you can set an alarm to notify you in case errors are detected in the MySQL or MariaDB error log. To learn more about monitoring the slow query log and error log by creating a CloudWatch dashboard and using CloudWatch Logs Insights, see the blog post [Creating an Amazon CloudWatch dashboard to monitor Amazon RDS and Amazon Aurora MySQL.](https://aws.amazon.com/blogs/database/creating-an-amazon-cloudwatch-dashboard-to-monitor-amazon-rds-and-amazon-aurora-mysql/) # Audit trails The audit trail (or audit log) provides a security-relevant, chronological record of events in your AWS account. It includes events for Amazon RDS, which provide documentary evidence of the sequence of activities that have affected your database or your cloud environment. In Amazon RDS for MySQL or MariaDB, using the audit trail involves: + Monitoring the DB instance audit log + Monitoring Amazon RDS API calls in AWS CloudTrail For an Amazon RDS DB instance, the objectives of auditing typically include: + Enabling accountability for the following: + Modifications performed on the parameter or security configuration + Actions performed in a database schema, table, or row, or actions that affect specific content + Intrusion detection and investigation + Suspicious activity detection and investigation + Detection of authorization problems; for example, to identify access rights abuses by regular or privileged users The database audit trail tries to answer these typical questions: *Who viewed or modified sensitive data inside your database? When did this happen? Where did a specific user access the data from? Did privileged users abuse their unlimited access rights?* Both MySQL and MariaDB implement the DB instance audit trail feature by using the MariaDB Audit Plugin. This plugin records database activity such as users logging on to the database and queries running against the database. The record of database activity is stored in a log file. To access the audit log, the DB instance must use a custom option group with the `MARIADB_AUDIT_PLUGIN` option. For more information, see [MariaDB Audit Plugin support for MySQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html) in the Amazon RDS documentation. The records in the audit log are stored in a specific format, as defined by the plugin. You can find more details about the audit log format in the [MariaDB Server documentation](https://mariadb.com/kb/en/mariadb-audit-plugin-log-format/). The AWS Cloud audit trail for your AWS account is provided by the [AWS CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-user-guide.html) service. CloudTrail captures API calls for Amazon RDS as events. All Amazon RDS actions are logged. CloudTrail provides a record of actions in Amazon RDS performed by a user, role, or another AWS service. Events include actions taken in the AWS Management Console, AWS CLI, and AWS SDKs and APIs. ## Example In a typical audit scenario, you might need to combine AWS CloudTrail trails with the database audit log and Amazon RDS events monitoring. For example, you might have a scenario where the database parameters of your Amazon RDS DB instance (for example, `database-1`) have been modified and your task is to identify who did the modification, what was changed, and when the change happened. To accomplish the task, follow these steps: 1. List the Amazon RDS events that happened to the database instance `database-1` and determine whether there is an event in the category `configuration change` that has the message `Finished updating DB parameter group`. ``` $ aws rds describe-events --source-identifier database-1 --source-type db-instance { "Events": [ { "SourceIdentifier": "database-1", "SourceType": "db-instance", "Message": "Finished updating DB parameter group", "EventCategories": [ "configuration change" ], "Date": "2022-12-01T09:22:40.413000+00:00", "SourceArn": "arn:aws:rds:eu-west-3:111122223333:db:database-1" } ] } ``` 1. Identify which DB parameter group the DB instance is using: ``` $ aws rds describe-db-instances --db-instance-identifier database-1 --query 'DBInstances[*].[DBInstanceIdentifier,Engine,DBParameterGroups]' [ [ "database-1", "mariadb", [ { "DBParameterGroupName": "mariadb10-6-test", "ParameterApplyStatus": "pending-reboot" } ] ] ] ``` 1. [Use the AWS CLI to search for CloudTrail events ](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/view-cloudtrail-events-cli.html)in the Region where `database-1` is deployed, in the time period around the Amazon RDS event discovered in step 1, and where `EventName=ModifyDBParameterGroup`. ``` $ aws cloudtrail --region eu-west-3 lookup-events --lookup-attributes AttributeKey=EventName,AttributeValue=ModifyDBParameterGroup --start-time "2022-12-01, 09:00 AM" --end-time "2022-12-01, 09:30 AM" { "eventVersion": "1.08", "userIdentity": { "accountId": "111122223333", "accessKeyId": "AKIAIOSFODNN7EXAMPLE", "sessionContext": { "sessionIssuer": { "type": "Role", "principalId": "AIDACKCEVSQ6C2EXAMPLE", "arn": "arn:aws:iam::111122223333:role/Role1", "accountId": "111122223333", "userName": "User1" } } }, "eventTime": "2022-12-01T09:18:19Z", "eventSource": "rds.amazonaws.com", "eventName": "ModifyDBParameterGroup", "awsRegion": "eu-west-3", "sourceIPAddress": "AWS Internal", "userAgent": "AWS Internal", "requestParameters": { "parameters": [ { "isModifiable": false, "applyMethod": "pending-reboot", "parameterName": "innodb_log_buffer_size", "parameterValue": "8388612" }, { "isModifiable": false, "applyMethod": "pending-reboot", "parameterName": "innodb_write_io_threads", "parameterValue": "8" } ], "dBParameterGroupName": "mariadb10-6-test" }, "responseElements": { "dBParameterGroupName": "mariadb10-6-test" }, "requestID": "fdf19353-de72-4d3d-bf29-751f375b6378", "eventID": "0bba7484-0e46-4e71-93a8-bd01ca8386fe", "eventType": "AwsApiCall", "managementEvent": true, "recipientAccountId": "111122223333", "eventCategory": "Management", "sessionCredentialFromConsole": "true" } ``` The CloudTrail event reveals that `User1` with role `Role1` from AWS account 111122223333 modified the DB parameter group `mariadb10-6-test`, which was used by the DB instance `database-1` on `2022-12-01 at 09:18:19 h`. Two parameters were modified and set to the following values: + `innodb_log_buffer_size = 8388612` + `innodb_write_io_threads = 8` ## Additional CloudTrail and CloudWatch Logs features You can troubleshoot operational and security incidents over the past 90 days by viewing **Event history** on the CloudTrail console. To extend the retention period and take advantage of additional query capabilities, you can use [AWS CloudTrail Lake](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-lake.html). With AWS CloudTrail Lake, you can keep event data in an event data store for up to seven years. Additionally, the service supports complex SQL queries that offer a deeper and more customizable view of events than the views provided by simple key-value lookups in **Event history**. To monitor your audit trails, set alarms, and get notifications when specific activity occurs, you need to [configure CloudTrail to send its trail records to CloudWatch Logs](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/monitor-cloudtrail-log-files-with-cloudwatch-logs.html). After the trail records are stored as CloudWatch Logs, you can define metric filters to evaluate log events to match terms, phrases, or values, and assign metrics to metric filters. Furthermore, you can create CloudWatch alarms that are generated according to thresholds and time periods that you specify. For example, you can configure alarms that send notifications to responsible teams, so they can take the appropriate action. You can also configure CloudWatch to automatically perform an action in response to an alarm.