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 |
|---|---|---|---|---|
| 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. | oas_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. | - |
| data.sqlAuditDTOList | Array | The list of SQL audit records. | - |
| data.sqlAuditDTOList[].sqlID | String | The SQL ID. | D56D688E7D666115D0A88A3B182242C8 |
| data.sqlAuditDTOList[].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 |
| data.sqlAuditDTOList[].userName | String | The username. | oas_test |
| data.sqlAuditDTOList[].databaseName | String | The name of the database. | default_database |
| data.sqlAuditDTOList[].elapsedTime | Float | The execution time. | 19.0 |
| data.sqlAuditDTOList[].scanRows | Integer | The number of scanned rows. | 3519 |
| data.sqlAuditDTOList[].operatorType | String | The operation type. | SELECT |
| 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": "D56D688E7D666115D0A88A3B182242C8",
"userClientIp": "xx.xxx.xx.xxx",
"userName": "oas_test"
}
]
},
"requestId": "xxxxxx",
"success": true
}