Manage audit logs within StarRocks via AuditLoader
This topic describes how to manage StarRocks audit logs within a table via the plugin - AuditLoader.
StarRocks stores its audit logs in the local file fe/log/fe.audit.log rather than an internal database. The plugin AuditLoader allows you to manage audit logs directly within your cluster. Once installed, AuditLoader reads logs from the file, and loads them into StarRocks via HTTP PUT. You can then query the audit logs in StarRocks using SQL statements.
Create a table to store audit logs
Create a database and a table in your StarRocks cluster to store its audit logs. See CREATE DATABASE and CREATE TABLE for detailed instructions.
Because the fields of audit logs vary among different StarRocks versions, it is important to follow recommendations mentioned below to avoid compatibility issues during upgrade:
CAUTION
- All new fields should be marked as
NULL
.- Fields should NOT be renamed, as users may rely on them.
- Only backward compatible changes should be applied to field types, e.g.
VARCHAR(32)
->VARCHAR(64)
, to avoid errors during insert.AuditEvent
fields are resolved by name only. The order of columns within table doesn't matter, and can be changed by user in any time.AuditEvent
fields which doesn't exist in the table are ignored, so users can remove columns they don't need.
CREATE DATABASE starrocks_audit_db__;
CREATE TABLE starrocks_audit_db__.starrocks_audit_tbl__ (
`queryId` VARCHAR(36) COMMENT "Unique query ID",
`timestamp` DATETIME NOT NULL COMMENT "Query start time",
`queryType` VARCHAR(12) COMMENT "Query type (query, slow_query, connection)",
`clientIp` STRING COMMENT "Client IP address and optional port number",
`user` STRING COMMENT "User who initiates the query",
`authorizedUser` STRING COMMENT "user_identity in MySQL format ('user'@'host', 'user'@'%')",
`resourceGroup` STRING COMMENT "Resource group name",
`catalog` STRING COMMENT "Catalog name",
`db` STRING COMMENT "Database that the query scans",
`state` VARCHAR(8) COMMENT "Query state (EOF, ERR, OK)",
`errorCode` STRING COMMENT "Error code",
`queryTime` BIGINT COMMENT "Query time in milliseconds",
`scanBytes` BIGINT COMMENT "Size of the scanned data in bytes",
`scanRows` BIGINT COMMENT "Row count of the scanned data",
`returnRows` BIGINT COMMENT "Row count of the result",
`cpuCostNs` BIGINT COMMENT "CPU resources consumption time, in nanoseconds",
`memCostBytes` BIGINT COMMENT "Memory cost in bytes",
`stmtId` BIGINT COMMENT "Incremental SQL statement ID",
`isQuery` TINYINT COMMENT "If the SQL is a query (0 and 1)",
`feIp` STRING COMMENT "IP address of FE that executes the SQL",
`stmt` VARCHAR(1048576) COMMENT "Original SQL statement. Since AuditLoader v3.0.1",
`digest` VARCHAR(32) COMMENT "Slow SQL fingerprint, calculated only if enable_compute_all_query_digest=true. Since AuditLoader v3.0.1",
`planCpuCosts` DOUBLE COMMENT "CPU resources consumption time for planning in nanoseconds. Since AuditLoader v3.0.1",
`planMemCosts` DOUBLE COMMENT "Memory cost for planning in bytes. Since AuditLoader v3.0.1",
`pendingTimeMs` BIGINT COMMENT "Time spent in query queue if query_queue_enable=true, in milliseconds. Since AuditLoader v4.2.0",
`candidateMvs` STRING COMMENT "Names of Materialized Views marked as candidates, separated with comma. Since StarRocks v3.2.0 and AuditLoader v4.2.0",
`hitMVs` STRING COMMENT "Names of Materialized Views rewritten by query optimizer, separated with comma. Since StarRocks v3.2.0 and AuditLoader v4.2.0",
`warehouse` STRING COMMENT "Warehouse name. Since StarRocks v3.3.0 and AuditLoader v4.2.1",
) ENGINE = OLAP
DUPLICATE KEY (`queryId`, `timestamp`, `queryType`)
COMMENT "Audit log table"
PARTITION BY date_trunc('day', `timestamp`)
PROPERTIES (
"replication_num" = "1",
"partition_live_number" = "30"
);
starrocks_audit_tbl__
is created with dynamic partitions. By default, the first dynamic partition is created 10 minutes after the table is created. Audit logs can then be loaded into the table. You can check the partitions in the table using the following statement:
SHOW PARTITIONS FROM starrocks_audit_db__.starrocks_audit_tbl__;
After a partition is created, you can move on to the next step.
Download and configure AuditLoader
-
Download the AuditLoader installation package. The package is compatible with all available versions of StarRocks.
-
Unzip the installation package.
unzip auditloader.zip
The following files are inflated:
- auditloader.jar: the JAR file of AuditLoader.
- plugin.properties: the properties file of AuditLoader. You do not need to modify this file.
- plugin.conf: the configuration file of AuditLoader. In most cases, you only need to modify the
user
andpassword
fields in the file.
-
Modify plugin.conf to configure AuditLoader. You must configure the following items to make sure AuditLoader can work properly:
frontend_host_port
: FE IP address and HTTP port, in the format<fe_ip>:<fe_http_port>
. It is recommended to set it to its default value127.0.0.1:8030
. Each FE in StarRocks manages its own Audit Log independently, and after installing the plugin, each FE will start its own background thread to fetch and save Audit Logs, and write them via Stream Load. Thefrontend_host_port
configuration item is used to provide the IP and port of the HTTP protocol for the background Stream Load task of the plug-in, and this parameter does not support multiple values. The IP part of the parameter can use the IP of any FE in the cluster, but it is not recommended because if the corresponding FE crashes, the audit log writing task in the background of other FEs will also fail due to the failure of communication. It is recommended to set it to the default value127.0.0.1:8030
, so that each FE uses its own HTTP port to communicate, thus avoiding the impact on the communication in case of an exception of the other FEs (all the write tasks will be forwarded to the FE Leader node to be executed eventually).database
: name of the database you created to host audit logs.table
: name of the table you created to host audit logs.user
: your cluster username. You MUST have the privilege to load data (LOAD_PRIV) into the table.password
: your user password.secret_key
: the key (string, must not be longer than 16 bytes) used to encrypt the password. If this parameter is not set, it indicates that the password in plugin.conf will not be encrypted, and you only need to specify the plaintext password inpassword
. If this parameter is specified, it indicates that the password is encrypted by this key, and you need to specify the encrypted string inpassword
. The encrypted password can be generated in StarRocks using theAES_ENCRYPT
function:SELECT TO_BASE64(AES_ENCRYPT('password','secret_key'));
.enable_compute_all_query_digest
: whether to generate Hash SQL fingerprint for all queries (StarRocks only enable SQL fingerprint for slow queries by default). Note that the fingerprint calculation in the plugin is different from that of FE, which will normalize the SQL statement, while the plugin does not. The fingerprint calculation will consume additional computing resources if this feature is enabled.filter
: the filter conditions for audit log loading. This parameter is based on the WHERE parameter in Stream Load, i.e.-H “where: <condition>”
, defaults to an empty string. Example:filter=isQuery=1 and clientIp like '127.0.0.1%' and user='root'
.
-
Zip the files back into a package.
zip -q -m -r auditloader.zip auditloader.jar plugin.conf plugin.properties
-
Dispatch the package to all machines that host FE nodes. Make sure all packages are stored in an identical path. Otherwise, the installation fails. Remember to copy the absolute path to the package after you dispatched the package.
NOTE
You can also distribute auditloader.zip to an HTTP service accessible to all FEs (for example,
httpd
ornginx
) and install it using the network. Note that in both cases the auditloader.zip needs to be persisted in the path after the installation is performed, and the source files should not be deleted after installation.
Install AuditLoader
Execute the following statement along with the path you copied to install AuditLoader as a plugin in StarRocks:
INSTALL PLUGIN FROM "<absolute_path_to_package>";
Example of installation from a local package:
INSTALL PLUGIN FROM "<absolute_path_to_package>";
If you want install the plugin via a network path, you need to provide the md5 of the package in the properties of the INSTALL statement.
Example:
INSTALL PLUGIN FROM "http://xx.xx.xxx.xxx/extra/auditloader.zip" PROPERTIES("md5sum" = "3975F7B880C9490FE95F42E2B2A28E2D");
See INSTALL PLUGIN for detailed instructions.
Verify the installation and query audit logs
-
You can check if the installation is successful via SHOW PLUGINS.
In the following example, the
Status
of the pluginAuditLoader
isINSTALLED
, meaning installation is successful.mysql> SHOW PLUGINS\G
*************************** 1. row ***************************
Name: __builtin_AuditLogBuilder
Type: AUDIT
Description: builtin audit logger
Version: 0.12.0
JavaVersion: 1.8.31
ClassName: com.starrocks.qe.AuditLogBuilder
SoName: NULL
Sources: Builtin
Status: INSTALLED
Properties: {}
*************************** 2. row ***************************
Name: AuditLoader
Type: AUDIT
Description: Available for versions 2.5+. Load audit log to starrocks, and user can view the statistic of queries
Version: 4.2.1
JavaVersion: 1.8.0
ClassName: com.starrocks.plugin.audit.AuditLoaderPlugin
SoName: NULL
Sources: /x/xx/xxx/xxxxx/auditloader.zip
Status: INSTALLED
Properties: {}
2 rows in set (0.01 sec) -
Execute some random SQLs to generate audit logs, and wait for 60 seconds (or the time you have specified in the item
max_batch_interval_sec
when you configure AuditLoader) to allow AuditLoader to load audit logs into StarRocks. -
Check the audit logs by querying the table.
SELECT * FROM starrocks_audit_db__.starrocks_audit_tbl__;
The following example shows when audit logs are loaded into the table successfully:
mysql> SELECT * FROM starrocks_audit_db__.starrocks_audit_tbl__\G
*************************** 1. row ***************************
queryId: 01975a33-4129-7520-97a2-05e641cec6c9
timestamp: 2025-06-10 14:16:37
queryType: query
clientIp: xxx.xx.xxx.xx:65283
user: root
authorizedUser: 'root'@'%'
resourceGroup: default_wg
catalog: default_catalog
db:
state: EOF
errorCode:
queryTime: 3
scanBytes: 0
scanRows: 0
returnRows: 1
cpuCostNs: 33711
memCostBytes: 4200
stmtId: 102
isQuery: 1
feIp: xxx.xx.xxx.xx
stmt: SELECT * FROM starrocks_audit_db__.starrocks_audit_tbl__
digest:
planCpuCosts: 908
planMemCosts: 0
pendingTimeMs: -1
candidateMvs: null
hitMVs: null
warehouse: default_warehouse
1 row in set (0.01 sec)
Troubleshooting
If no audit logs are loaded to the table after the dynamic partition is created and the plugin is installed, you can check whether plugin.conf is configured properly or not. To modify it, you must first uninstall the plugin:
UNINSTALL PLUGIN AuditLoader;
Logs of AuditLoader are printed in fe.log of each FE, you can retrieve them by searching the keyword audit
in fe.log. After all configurations are set correctly, you can follow the above steps to install AuditLoader again.