Overview
You can call this API to query SQL audit data.
API details
Constraints
The caller must have an AccessKey for accessing the multi-cloud API. For information about how to obtain the AccessKey ID and AccessKey secret, see Manage AccessKeys.
Request path
GET /api/v2/instances/{instanceId}/tenants/{tenantId}/sqlaudit/data
Request parameters
Path
| Parameter | Type | Required | Description | Example value |
|---|---|---|---|---|
| instanceId | String | Yes | The ID of the cluster. | ob317v4uif**** |
| tenantId | String | Yes | The ID of the tenant. | t4louaeei**** |
Query
| Parameter | Type | Required | Description | Example value |
|---|---|---|---|---|
| startTime | String | Yes | The start time of the interval. | 2025-01-01T00:00:00Z |
| endTime | String | Yes | The end time of the interval. | 2025-01-02T00:00:00Z |
| clientIp | String | No | The IP address of the client. | xx.xxx.xx.xxx |
| dbName | String | No | The name of the database. | default_database |
| executeTimeMin | Integer | No | The minimum execution time, in milliseconds. | 10 |
| executeTimeMax | Integer | No | The maximum execution time, in milliseconds. | 1000 |
| nodeIp | String | No | The IP address of the execution node. | i-bp18l4****str4uk03 |
| operatorType | String | No | The type of the SQL statement. Valid values: SELECT, CREATE, and so on. | SELECT |
| scanRowsMin | Integer | No | The minimum number of scanned rows. | 0 |
| scanRowsMax | Integer | No | The maximum number of scanned rows. | 10000 |
| userName | String | No | The name of the user. | doc_test |
| searchKeyWord | String | No | The query keyword. | update |
| searchKeyWordMethod | String | No | The query relationship. Valid values: and and or. | and |
| sortField | String | No | The field to sort by. | elapsedTime |
| sortType | String | No | The sort type. | desc |
| pageNumber | Integer | No | The page number. Default value: 1. | 1 |
| pageSize | Integer | No | The number of records per page. Default value: 10. | 10 |
| openRequest | unknown | Yes | The open request parameter. | - |
Response parameters
| Parameter | Type | Description | Example value |
|---|---|---|---|
| data | Object | The SQL audit data. | - |
| sqlAuditDTOList | Array | The list of SQL audit records. | - |
| affectedRows | Integer | The number of affected rows. | 0 |
| clientIp | String | The IP address of the client. | xx.xxx.xx.xxx |
| databaseName | String | The name of the database. | default_database |
| elapsedTime | Float | The execution time. | 19.0 |
| isSuccess | Boolean | Indicates whether the request was successful. | true |
| operatorType | String | The type of the SQL statement. | SELECT |
| querySQL | String | The SQL statement. | SELECT table_schema as schema_name, 'VIEW' as type, table_name as name FROM information_schema.tables where table_type = 'VIEW' ORDER BY schema_name, name |
| requestTime | String | The request time. | 2025-03-20T08:43:41Z |
| retCode | Integer | The return code. | 0 |
| scanRows | Integer | The number of scanned rows. | 3519 |
| sqlID | String | The SQL ID. | D56D688E7D666115D0A88A3B182242C8 |
| userClientIp | String | The IP address of the user's client. | xx.xxx.xx.xxx |
| userName | String | The name of the user. | doc_test |
| requestId | String | The request ID. | xxxxxx |
| success | Boolean | Indicates whether the request was successful. | true |
Examples
Request example
curl --digest -u '<Your AccessKey ID:AccessKey secret>' \
-X GET \
'https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/sqlaudit/data?openRequest={openRequest}' \
-G \
-d 'startTime={startTime}' \
-d 'endTime={endTime}' \
-d 'pageNumber={pageNumber}' \
-d 'pageSize={pageSize}'
Response example
JSON format
{
"data": {
"sqlAuditDTOList": [
{
"affectedRows": 0,
"clientIp": "xx.xxx.xx.xxx",
"databaseName": "default_database",
"elapsedTime": 19.0,
"isSuccess": 1,
"key": 1,
"operatorType": "SELECT",
"querySQL": "SELECT table_schema as schema_name, 'VIEW' as type, table_name as name FROM information_schema.tables where table_type = 'VIEW' ORDER BY schema_name, name",
"requestTime": "2025-03-20T08:43:41Z",
"requestTimestamp": 0,
"retCode": 0,
"scanRows": 3519,
"sqlID": "D56D688E7D666115D0A88A3B********",
"userClientIp": "xx.xxx.xx.xxx",
"userName": "doc_test"
}
]
},
"requestId": "xxxxxx",
"success": true
}