Use DescribeAuditLogRecords to query SQL audit logs - AnalyticDB - Alibaba Cloud - AnalyticDB

Queries the SQL audit logs of a cluster.

Operation description

SQL audit logs can be queried only when SQL audit is enabled, and only logs from the last 30 days are supported. If SQL audit is shutdown and then re-enabled, only logs generated after re-enabling can be queried. SQL audit logs do not record INSERT INTO VALUES, REPLACE INTO VALUES, or UPSERT INTO VALUES operations.
For the service registration of this service, see Service registration.

Try it now

Try this API in OpenAPI Explorer, no manual signing needed. Successful calls auto-generate SDK code matching your parameters. Download it with built-in credential security for local usage.

Test

RAM authorization

The table below describes the authorization required to call this API. You can define it in a Resource Access Management (RAM) policy. The table's columns are detailed below:

Action: The actions can be used in the Action element of RAM permission policy statements to grant permissions to perform the operation.
API: The API that you can call to perform the action.
Access level: The predefined level of access granted for each API. Valid values: create, list, get, update, and delete.
Resource type: The type of the resource that supports authorization to perform the action. It indicates if the action supports resource-level permission. The specified resource must be compatible with the action. Otherwise, the policy will be ineffective.
- For APIs with resource-level permissions, required resource types are marked with an asterisk (*). Specify the corresponding Alibaba Cloud Resource Name (ARN) in the Resource element of the policy.
- For APIs without resource-level permissions, it is shown as All Resources. Use an asterisk (*) in the Resource element of the policy.
Condition key: The condition keys defined by the service. The key allows for granular control, applying to either actions alone or actions associated with specific resources. In addition to service-specific condition keys, Alibaba Cloud provides a set of common condition keys applicable across all RAM-supported services.
Dependent action: The dependent actions required to run the action. To complete the action, the RAM user or the RAM role must have the permissions to perform all dependent actions.

Action

Access level

Resource type

Condition key

Dependent action

adb:DescribeAuditLogRecords

list

*DBClusterLakeVersion

acs:adb:{#regionId}:{#accountId}:dbcluster/{#DBClusterId}

None

Request parameters

Parameter	Type	Required	Description	Example
DBClusterId	string	Yes	The cluster ID of the Data Lakehouse Edition cluster. Note You can call the DescribeDBClusters operation to query the cluster IDs of all clusters in a specified region.	amv-t4nj8619bz2w3****
RegionId	string	Yes	The region ID. Note You can call the DescribeRegions operation to query the region ID of the cluster.	cn-hangzhou
StartTime	string	No	The beginning of the time range to query. Specify the time in UTC in the yyyy-MM-ddTHH:mmZ format. Note SQL audit logs can be queried only when SQL audit is enabled, and only logs from the last 30 days are supported. If SQL audit is disabled and then re-enabled, only logs generated after re-enabling can be queried.	2022-08-12T04:17Z
EndTime	string	No	The end of the time range to query. Specify the time in UTC in the yyyy-MM-ddTHH:mmZ format. Note The end time must be later than the start time. The interval between the start time and the end time cannot exceed 24 hours.	2022-08-12T17:08Z
DBName	string	No	The name of the database on which the SQL statement is executed.	adb_demo
QueryKeyword	string	No	The keyword used to search the returned results.	adb
SqlType	string	No	The type of the SQL statement. Valid values: DELETE SELECT UPDATE INSERT INTO SELECT ALTER DROP CREATE Note Only one type can be specified per request. If this parameter is not specified, all types are queried by default.	SELECT
Succeed	string	No	Specifies whether the SQL statement is executed successfully. Valid values: true: Executed successfully. false: Execution failed.	true
HostAddress	string	No	The IP address and port number of the client that executes the SQL statement.	100.104.XX.XX:43908
OrderType	string	No	The order in which the results are sorted by SQL execution time. Valid values: asc: ascending order. desc: descending order.	asc
User	string	No	The username that executes the SQL statement.	test
Order	string	No	The sorting order based on specified fields. The value is in JSON format and is an ordered JSON array. Compound sorting is performed in the order of the input array. The array contains the `Field` and `Type` fields. Example: `[{"Field":"ExecutionStartTime","Type":"Desc"},{"Field":"ScanRows","Type":"Asc"}]`. `Field` specifies the field name for sorting. Valid values: HostAddress: the address of the client that connects to the database. UserName: the username. ExecutionStartTime: the execution start time of the SQL statement. QueryTime: the execution duration of the SQL statement. PeakMemoryUsage: the peak memory usage during the execution of the SQL statement. ScanRows: the number of rows scanned by tasks with data sources. ScanSize: the amount of scanned data. ScanTime: the total time consumed for scanning data. PlanningTime: the time consumed for generating the execution plan. WallTime: the cumulative CPU time of all operators across all nodes in the query. ProcessID: the process ID. `Type` specifies the sorting type. Valid values: Desc: descending order. Asc: ascending order.	[{"Field":"ExecuteTime","Type":"Desc"},{"Field":"HostAddress","Type":"Asc"}]
PageSize	integer	No	The number of entries per page. Valid values: 10 (default) 30 50 100	10
PageNumber	integer	No	The page number. The value must be a positive integer that does not exceed the maximum value of the Integer data type. Default value: 1.	1
ProxyUser	string	No	A reserved parameter.	None
EngineType	string	No	Valid values: XIHE : XIHE AGENT_SERVERLESS : AGENT_SERVERLESS	XIHE
		No	The engine type. Valid values: XIHE: audit logs of the default compute engine. AGENT_SERVERLESS: audit logs of the Serverless analytics feature. If this parameter is not specified, the default value is XIHE. Valid values: XIHE : XIHE AGENT_SERVERLESS : AGENT_SERVERLESS	XIHE

Response elements

Element	Type	Description	Example
	object	The list details.
TotalCount	string	The total number of entries.	6974
PageSize	string	The number of entries per page.	10
RequestId	string	The request ID.	8A564B7F-8C00-43C0-8EC5-919FBB70573
PageNumber	string	The page number.	1
DBClusterId	string	The cluster ID of the Data Lakehouse Edition cluster.	amv-t4nj8619bz2w3****
Items	array<object>	The list.
	object	The SQL audit log entries.
HostAddress	string	The IP address and port number of the client that executes the SQL statement.	100.104.XX.XX:43908
Succeed	string	Indicates whether the SQL statement is executed successfully. Valid values: true: Executed successfully. false: Execution failed.	true
SQLText	string	The details of the SQL statement.	SELECT * FROM adb_hdfs_import_source
TotalTime	string	The execution duration of the SQL statement. Unit: milliseconds (ms).	216
ConnId	string	The connection ID.	14356****
DBName	string	The name of the database on which the SQL statement is executed.	adb_demo
SQLType	string	The type of the SQL statement.	SELECT
ExecuteTime	string	The execution start time of the SQL statement in local time. Format: yyyy-MM-dd HH:mm:ss.	2022-08-12 10:10:00
ExecuteTimestamp	integer	The execution start time of the SQL statement. The value is a UNIX timestamp in milliseconds.	1774600396584
ProcessID	string	The task ID.	202106081752021720161662490345362390
User	string	The username that executes the SQL statement.	test
HasDiagnosticInfo	boolean	Indicates whether diagnostic information is included. Valid values: true : true false : false	true

Examples

Success response

JSON format

{
  "TotalCount": "6974",
  "PageSize": "10",
  "RequestId": "8A564B7F-8C00-43C0-8EC5-919FBB70573",
  "PageNumber": "1",
  "DBClusterId": "amv-t4nj8619bz2w3****",
  "Items": [
    {
      "HostAddress": "100.104.XX.XX:43908",
      "Succeed": "true",
      "SQLText": "SELECT * FROM adb_hdfs_import_source",
      "TotalTime": "216",
      "ConnId": "14356****",
      "DBName": "adb_demo",
      "SQLType": "SELECT",
      "ExecuteTime": "2022-08-12 10:10:00",
      "ExecuteTimestamp": 1774600396584,
      "ProcessID": "202106081752021720161662490345362390",
      "User": "test",
      "HasDiagnosticInfo": true
    }
  ]
}

Error codes

HTTP status code	Error code	Error message	Description
404	InvalidDBCluster.NotFound	The DBClusterId provided does not exist in our records.	The specified DBClusterId parameter does not exist. Make sure that the DBClusterId value is valid.

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.