Data Management:Query and analyze the operations logs of DMS in Log Service

Step 1: Register the Log Service project with DMS

1. Log on to the DMS console V5.0 as a DMS administrator.

2. On the Home page of the DMS console, click the icon next to Database instance in the left-side navigation pane.

   Note

   If you log on to the DMS console in simple mode, click Database instance in the left-side navigation pane. In the instance list that appears, click the icon.

3. In the Add Instance dialog box, configure the following parameters to register the Log Service project with DMS.

   Section	Parameter	Description
   Data Source	N/A	Select SLS on the Alibaba Cloud tab.
   Basic Information	File and Log Storage	Select SLS.
   	Instance Region	Select the region in which the Log Service project resides.
   	Entry mode	Select the method that you use to register the Log Service project. Default value: Connection string address.
   	Connection string address	Enter the connection string address of the Log Service project. DMS automatically generates a connection string address after you configure the Instance Region parameter.
   	Project name	Enter the name of the Log Service project.
   	AccessKey ID	Enter the AccessKey ID of your Alibaba Cloud account. The AccessKey ID is used for identity authentication when you register the Log Service project with DMS. Note For more information about how to obtain the AccessKey ID, see Create an AccessKey pair.
   	AccessKey Secret	Enter the AccessKey secret that corresponds to the AccessKey ID. Note For more information about how to obtain the AccessKey secret, see Create an AccessKey pair.
   	Control Mode	Select the mode that you use to manage the Log Service project in DMS. You can set this parameter only to Flexible Management.
   Advanced Information	Environment type	Select the type of the environment in which the Log Service project is deployed. Valid values: Dev, Test, Product, Pre, SIT, UAT, Pet, and STAG. For more information, see the "Environment types" section of the Change the environment type of an instance topic.
   	Instance Name	Enter the name of the Log Service project in DMS. Note You can change the name of the Log Service project when you manage the Log Service project in the DMS console. For more information, see Modify database instances.
   	DBA	Select the database administrator (DBA) of the Log Service project. The DBA can manage permissions on the Log Service project in DMS.
   	query timeout(s)	Specify the timeout period for the execution of an SQL query statement. If the execution of an SQL query statement lasts longer than the specified timeout period, the execution of the statement is terminated to ensure database security.
   	export timeout(s)	Specify the timeout period for the execution of an SQL export statement. If the execution of an SQL export statement lasts longer than the specified timeout period, the execution of the statement is terminated to ensure database security.

   Note

   • After you configure the preceding parameters, click Test connection in the lower part of the dialog box. Wait until the connectivity test passes.

   • If the error message "The execution result of the 'getProject' command is null" appears, you must check whether the Log Service project is created by the Alibaba Cloud account that you use to log on to DMS.

4. Click Submit.

Step 2: Create a task to export the operation logs of DMS

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Security and Specifications > Operation Audit.

   Note

   If you log on to the DMS console in simple mode, click the icon in the upper-left corner of the console and choose All functions > Security and Specifications > Operation Audit.

3. Click Export logs. On the Export logs lab, click New Task in the upper-right corner.

4. In the Create Export Task dialog box, configure the following parameters.

   Parameter	Required	Description
   The task name.	Yes	The name of the export task. Specify a descriptive name for easy identification.
   Destination Log Service	Yes	The Log Service project that is used to manage resources.
   SLS Logstore	Yes	The Logstore to which you want to export the operation logs of DMS. Select the destination Logstore from the drop-down list. Note If the destination Logstore is not displayed in the drop-down list, click Sync dictionary. In the message that appears, click OK. Then, DMS automatically collects metadata of the Logstore.
   Function Module	Yes	The DMS features whose logs you want to export. You can view the features in the Function Module column on the Operation Logs tab. Some of the valid values are Instance management, User Management, Permissions, and Data Query. By default, All is selected.
   Scheduling Method	Yes	The scheduling method of the task. Valid values: One-time Tasks: After you create a one-time task, the task immediately exports the logs only once. Periodic Tasks: You can select Day, Week, or Month to export logs to the destination Logstore on a periodic basis. If a periodic task runs for the first time, all operation logs that are generated in DMS from the start time of log entries to the scheduled start time are exported. Only incremental logs are exported later. For more information, see the "Periodic scheduling" section of the Export the operation logs of DMS to Log Service topic.
   Log Time Range	No	Note This parameter is displayed if you select One-time Tasks as Scheduling Method. The time range within which the operation logs to be exported are generated. By default, if you do not configure this parameter, all logs generated in the last three years are exported.
   Log Start Time	No	Note This parameter is displayed if you select Periodic Tasks as Scheduling Method. Auto triggered tasks do not have an end time. The start time of the logs to be exported. If you do not configure this parameter, the default value is the date three years ago from the date when the task is created.

5. Click OK. A log export task is created. The system also creates index fields such as dbId, dbName, and dbUser in your Logstore for querying and analyzing data.

   • If a one-time task is in the Successful state, the logs are exported.

     Note

     The indexes in the Logstore take effect at a later time after they are created. Therefore, a one-time task starts to be executed about 90 seconds after the task is created.

   • An auto triggered task exports logs multiple times and is in the Pending Scheduling state before and after the logs are exported. You can view the task logs to determine whether a task is successful.

   You can perform the following operations in the Actions column of a task:

   • Query: Click Query. You are redirected to the SQL Console page. On the page that appears, click Query. In the Execution section in the lower part of the page, you can view the logs that are exported to the Logstore.

   • Task Logs: Click Task Logs to view information such as the task start time, task end time, number of log entries to be exported, and task status.

   • Pause: Click Pause. In the dialog box that appears, click OK. Then, the auto triggered task is paused.

   • Restart: Click Restart. In the dialog box that appears, click OK to restart the suspended task.

     Note

     • You cannot perform the Restart operation on a one-time task.

     • You can perform all the preceding operations on an auto triggered task.

For more information about how to create an export task, see Export the operation logs of DMS to Log Service.

Step 3: Query and analyze the exported operations logs of DMS in the Log Service console

1. Log on to the Log Service console.
2. In the Projects section, click the project that you want to manage.
3. On the Log Storage > Logstores tab, click the Logstore that you want to manage.

4. Enter a query statement in the search box.

   A query statement consists of a search statement and an analytic statement in the Search statement|Analytic statement format. For more information, see Search syntax and Aggregate function.

   You can query and analyze only the following information in the Log Service console.

   Note

   In this example, the dmstest Logstore is used.

   • Execute the following SQL statement to query the user who failed to log on to a specific database the most times:

     __topic__ : DMS_LOG_DELIVERY AND subModule : LOGIN | SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, COUNT(*) cnt FROM dmstest WHERE state = '0' GROUP BY operUserId, operUserName ORDER BY cnt DESC LIMIT 10;

   • Execute the following SQL statement to query the user whose source IP address is not allowed to access the specific instance: In this example, 127.0.0.1 is used.

     Note

     The source IP address of an instance is the IP address that you use when you register the instance with DMS. This IP address is used to identify the source of the instance.

     __topic__ : DMS_LOG_DELIVERY | SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, COUNT(*) cnt FROM dmstest WHERE state = '0' and requestIp in ('127.0.0.1') GROUP BY operUserId, operUserName ORDER BY cnt DESC LIMIT 10;

   • Execute the following SQL statement to query the user who logs on to the DMS console the most times:

     __topic__ : DMS_LOG_DELIVERY| SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, COUNT(*) cnt FROM dmstest GROUP BY operUserId, operUserName ORDER BY cnt DESC LIMIT 10;

   • Execute the following SQL statement to query the users who access and manage multiple databases on the same day:

     __topic__: DMS_LOG_DELIVERY | SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, date_trunc('day', gmtCreate) time, dbId, COUNT(*) qpd from dmstest GROUP BY time, operUserId, operUserName, dbId ORDER BY time, qpd DESC;

   • Execute the following SQL statement to query the users who failed to perform operations on a database in DMS:

     __topic__ : DMS_LOG_DELIVERY AND moudleName : SQL_CONSOLE | SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, actionDesc as sqlStatement, subModule as sqlType, remark as failReason FROM dmstest WHERE state = '-1' order by id;

   • Execute the following SQL statement to query the user who downloads sensitive data the most times:

     __topic__ : DMS_LOG_DELIVERY AND moudleName : DATA_EXPORT | SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, COUNT(*) cnt FROM dmstest WHERE hasSensitiveData = 'true' GROUP BY operUserId, operUserName ORDER BY cnt DESC LIMIT 10;

   • Execute the following SQL statement to query the SQL statements that are executed to delete and modify sensitive data in batches:

     __topic__ : DMS_LOG_DELIVERY | SELECT subModule, COUNT(*) cnt, COUNT(affectRows) affectRow FROM dmstest WHERE subModule != '' GROUP BY subModule ORDER BY cnt DESC;

   • Execute the following SQL statement to query the users who enable or disable the data watermark feature during data export:

     __topic__ : DMS_LOG_DELIVERY AND moudleName : DATA_EXPORT | SELECT targetId as orderId, concat(cast(operUserId as varchar), '(', operUserName, ')') user, COUNT(*) cnt FROM dmstest where actionDesc like '%enable data watermark: false' GROUP BY targetId, operUserId, operUserName ORDER BY cnt DESC LIMIT 10;

     Note

     • '%enable data watermark: true' is used in the query statement to query the users who enable the data watermark feature.

     • '%enable data watermark: false' is used in the query statement to query the users who disable the data watermark feature.

   • Execute the following SQL statement to query the users who download the SQL result set in the Execution History section on the SQL Console page:

     __topic__ : DMS_LOG_DELIVERY AND moudleName : SQL_CONSOLE_EXPORT | SELECT concat(cast(operUserId as varchar), '(', operUserName, ')') user, COUNT(*) cnt FROM dmstest GROUP BY operUserId, operUserName ORDER BY cnt DESC LIMIT 10;

For more information about the query statements, see Query and analyze logs.