How to use the Search feature in DAS SQL Explorer and Audit-Database Autonomy Service(DAS)-阿里云帮助中心

Important

If you have enabled DAS Enterprise Edition V3, the Search feature is renamed to the Audit feature.

Prerequisites

The database instance that you want to manage is connected to DAS and its status is Normal Access.
SQL Explorer and Audit is enabled for the instance. For more information, see Enable SQL Explorer and Audit.
To use the Search feature as a RAM user, you must attach the AliyunHDMReadOnlyWithSQLLogArchiveAccess policy. For more information, see Authorize a RAM user by using a system policy.

Note
You can also use a custom policy to grant a RAM user permissions to use the Search feature, including data export. For more information, see Use a custom policy to authorize a RAM user to use the search and export features of SQL Explorer and Audit.

Supported databases and regions

For information about the databases and regions supported by each version of DAS Enterprise Edition, see Product editions.

Usage notes

In the Logs, a Status of Successful for an SQL statement indicates only that the statement was executed successfully without exceptions. For example, during a transaction rollback, if an individual SQL statement within the transaction executes successfully, its Status in the Logs reads Successful.
For ApsaraDB RDS for MySQL and PolarDB for MySQL instances, connections may be reused due to connection pooling. As a result, the IP addresses and ports displayed in the Logs may not match the actual client IP addresses and ports. For more information, see Connection pooling for ApsaraDB RDS for MySQL or Connection pooling for PolarDB for MySQL.
A parameter setting determines the maximum length of recorded SQL statements. Statements that exceed this length are truncated. Because a prefix is added during data collection and processing, the actual recorded length of an SQL statement is slightly less than the configured parameter value.
Note
- For ApsaraDB RDS for MySQL and PolarDB for MySQL versions 5.6 and 5.7, the recording length is controlled by the loose_rds_audit_max_sql_size parameter.
- For ApsaraDB RDS for MySQL and PolarDB for MySQL version 8.0, the recording length is controlled by the loose_rds_audit_log_event_buffer_size parameter.
When you click Enable Advanced Query, you can query by Transaction ID if your database instance meets the following version requirements:
- For an ApsaraDB RDS for MySQL instance, the major version must be 8.0 and the minor version must be 20210311 or later. Before you run a query, you must set the loose_rds_audit_log_version parameter to MYSQL_V3. For more information, see ApsaraDB RDS for MySQL 8.0 parameter overview.
- For a PolarDB for MySQL cluster, the version must be 8.0.1.1.15 or later.

Procedure

Log on to the DAS console.
In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .
Find the target instance and click the instance ID to open the instance details page.

In the left-side navigation pane, click SQL Explorer and Audit. On the page that appears, click Search and configure the following parameters.

If you have enabled DAS Enterprise Edition V3, click Audit on the page that appears.

Parameter	Description
Time Range	Select a time range for the query based on your business requirements. Queries covering more than 24 hours may be slow or time out due to the large volume of data captured by SQL Explorer and Audit. Note You can query only data generated within the retention period after DAS Enterprise Edition is enabled. To query records for a longer time range, export the logs for offline analysis. The export feature runs asynchronously and is suitable for large data volumes. When SQL Explorer and Audit uses hot and cold storage, the maximum time range for a single query or export is 24 hours.
Keyword	You can combine multiple keywords in a search. Separate keywords with a space. Fuzzy search is not supported. Note A keyword must contain at least four characters.
User	You can specify multiple usernames, such as `user1 user2 user3`.
Database	You can specify multiple database names, such as `DB1 DB2 DB3`.
Operation Type	Select one or more operation types to query.

Note

You can click Enable Advanced Query to enter more information and narrow the search scope.

Click Query to view the details of the SQL statements in the Logs.

You can click Export, select the required fields under Exported Fields, and then click Export SQL Records. You can set the Export Time Range to export records from a larger time range.

For SQL Explorer and Audit using hot and cold storage, select a CSV Separator that is not present in the SQL logs when you export data. For more information, see Configure and open an exported CSV file to ensure the data is displayed in separate columns.
Important
- You can export up to 10 million data entries within a seven-day period in a single task.
- For SQL Explorer and Audit using hot and cold storage, the system creates a task when you query or export data from cold storage. You can view the task progress and history on the Task list page.
- For Enterprise Edition V0 and V1, DAS retains export records for the last 2 days. For Enterprise Edition V2 and V3, DAS retains export records for the last 7 days.
- You can only query or export data generated after the audit logs feature was enabled.
You can filter the results by Time range, Keyword, User, Database, and Operation type (SELECT, INSERT, UPDATE, and DELETE). You can also click Enable Advanced Query to set additional conditions. The Logs section includes columns such as SQL statement, Database, Thread ID, User, Client IP, Operation, Status, Latency (ms), Execution time, Updated rows, and Scanned rows.

Configure and open an exported CSV file

If a data delimiter exists within your exported SQL logs, columns may be misaligned when you open the CSV file in a tool like Microsoft Excel. To prevent this, the Search feature for hot and cold storage allows you to select a CSV Separator when exporting. This section demonstrates how to correctly format the file for columnar display using Excel 2019 on Windows as an example.

When you create an export task, select a separator that is not present in your SQL logs, such as $.

The available CSV separators are |, $, and #. If your SQL text contains quotation marks, choose one of these special separators to avoid conflicts. You can also edit the Task Name. After you complete the settings, click Submit Task.
Open the exported CSV file by using Excel.
Select the column of data you want to split. On the Excel toolbar, choose Data > Text to Columns.
In the Convert Text to Columns Wizard, under Original data type, select Delimited and click Next.
In the Delimiters section, select Others, enter $ in the field, and then click Next.

You can see a preview of the columns in the Data preview section.
In the Column data format section, select Regular and click Complete.

FAQ

Q: For ApsaraDB RDS for MySQL and PolarDB for MySQL instances, why do some SQL queries return data but show 0 for scanned rows in the Logs section?

A: This occurs if the query_cache_type parameter is enabled on the database instance. The scanned rows count in the Logs section reflects the number of rows scanned by the InnoDB engine. When query_cache_type is enabled, MySQL attempts to cache query results. If a subsequent query is a cache hit, the result is returned directly from the cache without accessing the InnoDB engine. Therefore, data is returned, but the scanned rows count is 0.

For ApsaraDB RDS for MySQL, see Fast Query Cache.
For PolarDB for MySQL, see Fast Query Cache.

Q: Why can't I find failed SQL statements for my ApsaraDB RDS for PostgreSQL instance?

A: For ApsaraDB RDS for PostgreSQL instances, failed SQL statements are recorded in the instance's error logs, not in the audit logs. To query the error logs, see View logs.

Q: Why is the database name in the Logs section different from the one in the SQL statement?

A: The database name shown in the Logs section is retrieved from the session, whereas the database name in the SQL statement is user-specified. These can differ in scenarios such as cross-database queries or dynamic SQL, where the user's input or query design determines the database name in the statement.

Q: Does enabling SQL Explorer and Audit affect database performance? How significant is the impact?

A: Yes, but the impact is minimal and almost unnoticeable.

The impact on specific resources is as follows:

CPU and memory: Consumption is extremely low and can be ignored.
Storage space: The feature primarily stores audit information. However, DAS Enterprise Edition stores this data on the DAS side, so it does not consume your database instance's storage.
Network: There is no impact on network performance.
Disk performance: No impact. Audit data is written to DAS storage, not the database instance's disks.

API reference

You can call the following API operations to query SQL logs for ApsaraDB RDS for MySQL, ApsaraDB RDS for PostgreSQL, and ApsaraDB RDS for SQL Server instances:

API	Description
DescribeSQLLogRecords	Queries the SQL Explorer and Audit logs for an ApsaraDB RDS instance.
DescribeSQLLogFiles	Retrieves the list of files exported from SQL Explorer and Audit.