Overview
You can call this API to query SQL audit data.
API details
Constraints
The caller must have an AccessKey for accessing APIs. 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 |
|---|---|---|---|---|
| UID | String | Yes | The user ID. | 1234567890 |
| callerType | String | Yes | The caller type. | USER |
| accessKeyId | String | Yes | The AccessKey ID. | xxxxxx |
| securityToken | String | Yes | The Security Token. | xxxxxx |
| callerUid | String | Yes | The caller UID. | xxxxxx |
| callerBid | String | Yes | The caller BID. | xxxxxx |
| stsTokenCallerUid | String | Yes | The STS Token caller UID. | xxxxxx |
| stsTokenCallerBid | String | Yes | The STS Token caller BID. | xxxxxx |
| acceptLanguage | String | Yes | The language. | zh-CN |
| mergedCallerBid | String | Yes | ||
| startTime | String | Yes | The start time of the query interval. | 2025-01-01T00:00:00Z |
| endTime | String | Yes | The end time of the query interval. | 2025-01-02T00:00:00Z |
| clientIp | String | No | The list of client IPs. | xx.xxx.xx.xxx |
| dbName | String | No | The list of databases. | 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 list of execution nodes. | i-bp18l4****str4uk03 |
| operatorType | String | No | The SQL type. Valid values: SELECT, CREATE, and others. | 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 list of usernames. | 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 sorting field. | elapsedTime |
| sortType | String | No | The sorting type. | desc |
| pageNumber | Integer | No | The page number. Default value: 1. | 1 |
| pageSize | Integer | No | The number of entries per page. Default value: 10. | 10 |
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 is successful. | true |
| operatorType | String | The operation type. | 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 client. | xx.xxx.xx.xxx |
| userName | String | The username. | doc_test |
| requestId | String | The ID of the request. | xxxxxx |
| success | Boolean | Indicates whether the request is successful. | true |
Examples
Request example
curl --digest -u 'ak:sk' \
-X GET \
'https://api-cloud.oceanbase.com/api/v2/instances/{instanceId}/tenants/{tenantId}/sqlaudit/data' \
-G -d 'instanceId=obxxx' \
-d 'tenantId=txxx' \
-d 'startTime=2025-01-01T00:00:00Z' \
-d 'endTime=2025-01-02T00:00:00Z' \
-d 'pageNumber=1' \
-d 'pageSize=10'
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
}