Skip to main content

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(64) COMMENT "Unique ID of the query",
  `timestamp` DATETIME NOT NULL COMMENT "Query start time",
  `queryType` VARCHAR(12) COMMENT "Query type (query, slow_query, connection)",
  `clientIp` VARCHAR(32) COMMENT "Client IP",
  `user` VARCHAR(64) COMMENT "Query username",
  `authorizedUser` VARCHAR(64) COMMENT "Unique identifier of the user, i.e., user_identity",
  `resourceGroup` VARCHAR(64) COMMENT "Resource group name",
  `catalog` VARCHAR(32) COMMENT "Catalog name",
  `db` VARCHAR(96) COMMENT "Database where the query runs",
  `state` VARCHAR(8) COMMENT "Query state (EOF, ERR, OK)",
  `errorCode` VARCHAR(512) COMMENT "Error code",
  `errorMessage` VARCHAR(1048576) NULL COMMENT "Error message returned to the client",
  `queryTime` BIGINT COMMENT "Query execution time (milliseconds)",
  `scanBytes` BIGINT COMMENT "Number of bytes scanned by the query",
  `scanRows` BIGINT COMMENT "Number of rows scanned by the query",
  `returnRows` BIGINT COMMENT "Number of rows returned by the query",
  `cpuCostNs` BIGINT COMMENT "CPU time consumed by the query (nanoseconds)",
  `memCostBytes` BIGINT COMMENT "Memory consumed by the query (bytes)",
  `stmtId` INT COMMENT "Incremental ID of the SQL statement",
  `isQuery` TINYINT COMMENT "Whether the SQL is a query (1 or 0)",
  `feIp` VARCHAR(128) COMMENT "FE IP that executed the statement",
  `stmt` VARCHAR(1048576) COMMENT "Original SQL statement",
  `digest` VARCHAR(32) COMMENT "Fingerprint of slow SQL",
  `planCpuCosts` DOUBLE COMMENT "CPU usage during query planning (nanoseconds)",
  `planMemCosts` DOUBLE COMMENT "Memory usage during query planning (bytes)",
  `pendingTimeMs` BIGINT COMMENT "Time the query waited in the queue (milliseconds)",
  `candidateMVs` VARCHAR(65533) NULL COMMENT "List of candidate materialized views",
  `hitMvs` VARCHAR(65533) NULL COMMENT "List of matched materialized views",
  `QueriedRelations` ARRAY<VARCHAR(65533)> NULL COMMENT "List of directly referenced tables and views",
  `warehouse` VARCHAR(32) NULL COMMENT "Warehouse name"
) 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__ uses expression partitioning. A partition for each day is created automatically when the audit logs of that day are loaded into the table, and only the 30 most recent partitions are kept (partition_live_number). After audit logs are loaded, you can check the partitions in the table using the following statement:

SHOW PARTITIONS FROM starrocks_audit_db__.starrocks_audit_tbl__;
note

If the table already exists from an earlier version, add the columns that were introduced since, otherwise the corresponding fields are silently dropped when the audit logs are loaded. For example, errorMessage is introduced in v4.2.0:

ALTER TABLE starrocks_audit_db__.starrocks_audit_tbl__
ADD COLUMN `errorMessage` VARCHAR(1048576) NULL COMMENT "Error message returned to the client" AFTER `errorCode`;

Add the column before you configure the filter property of AuditLoader to reference it, because that filter is evaluated against the columns of the table.

Download and configure AuditLoader​

  1. Download the AuditLoader installation package. The package is compatible with all available versions of StarRocks.

  2. 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 and password fields in the file.

  3. 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 value 127.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. The frontend_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 value 127.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 in password. If this parameter is specified, it indicates that the password is encrypted by this key, and you need to specify the encrypted string in password. The encrypted password can be generated in StarRocks using the AES_ENCRYPT function: SELECT TO_BASE64(AES_ENCRYPT('password','secret_key'));.
    • 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'.

  4. Zip the files back into a package.

    zip -q -m -r auditloader.zip auditloader.jar plugin.conf plugin.properties

  5. 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 or nginx) 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​

  1. You can check if the installation is successful via SHOW PLUGINS.

    In the following example, the Status of the plugin AuditLoader is INSTALLED, 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 3.3.11+. Load audit log to starrocks, and user can view the statistic of queries
        Version: 5.0.0
    JavaVersion: 11
    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)

  2. 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.

  3. 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

QueriedRelations: ["default_catalog.db1.tbl1","default_catalog.db1.view1"] …………




## Perform SQL-based analysis on audit logs

This section provides some examples of SQL-based analysis on audit logs.

### Analyze CPU consumption

The following SQL aggregates total CPU time per user (`cpuCostNs`) over the last 30 days, converts it to seconds, and calculates the percentage of total CPU usage.

```SQL
SELECT 
user,
SUM(cpuCostNs) / 1e9 AS total_cpu_seconds,                  -- Query the total CPU time.
(
    SUM(cpuCostNs) /
    (
        SELECT SUM(cpuCostNs)
        FROM starrocks_audit_db__.starrocks_audit_tbl__
        WHERE state IN ('EOF','OK')
          AND timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    )
) * 100 AS cpu_usage_percentage                             -- Calculate the percentage of total CPU usage per user.
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE state IN ('EOF','OK')                                     -- Include queries that are finished only.
AND timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)    -- Query the data of the last 30 days.
GROUP BY user
ORDER BY total_cpu_seconds DESC
LIMIT 20;                                                       -- List the top 20 users with the most CPU resource consumption.

Analyze memory usage​

The following SQL computes the maximum memory usage per user (memCostBytes) for a single query over the last 30 days.

SELECT 
    user,
    MAX(memCostBytes) / (1024 * 1024) AS max_mem_mb            -- Max memory usage (in MB) per query.
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE state IN ('EOF','OK')                                    -- Include queries that are finished only.
  AND timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)   -- Query the data of the last 30 days.
GROUP BY user
ORDER BY max_mem_mb DESC
LIMIT 20;                                                      -- List the top 20 users with the most memory resource consumption.

Analyze query concurrency​

The following SQL analyzes per-minute query concurrency over the last 30 days and extracts the maximum observed concurrency per user.

WITH UserConcurrency AS (
    SELECT 
        user,
        DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i') AS minute_bucket,
        COUNT(*) AS query_concurrency
    FROM starrocks_audit_db__.starrocks_audit_tbl__
    WHERE state IN ('EOF', 'OK')                              -- Include queries that are finished only.
      AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)       -- Query the data of the last 30 days.
      AND LOWER(stmt) LIKE '%select%'                         -- Include SELECT statements only.
    GROUP BY user, minute_bucket
    HAVING query_concurrency > 1                              -- Exclude scenarios where concurrency is less than one query per minute.
)
SELECT 
    user,
    minute_bucket,
    query_concurrency / 60.0 AS query_concurrency_per_second  -- Query the per-second concurrency.
FROM (
    SELECT 
        user,
        minute_bucket,
        query_concurrency,
        ROW_NUMBER() OVER (
            PARTITION BY user
            ORDER BY query_concurrency DESC
        ) AS rn
    FROM UserConcurrency
) ranked
WHERE rn = 1                                                  -- Keep the highest record for each user.
ORDER BY query_concurrency_per_second DESC
LIMIT 50;                                                     -- List the top 50 users with the highest concurrency.

Analyze memory usage of materialized view refresh operations​

The following SQL identifies memory-intensive materialized view refresh operations, typically characterized by INSERT OVERWRITE statements.

SELECT 
    user,
    MAX(memCostBytes) / (1024 * 1024) AS max_mem_mb             -- Max memory usage (in MB) per query.
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE state IN ('EOF','OK')                                     -- Include queries that are finished only.
  AND timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)    -- Query the data of the last 30 days.
  AND LOWER(stmt) LIKE '%insert overwrite%'                     -- Include materialized view refresh operations only.
GROUP BY user
ORDER BY max_mem_mb DESC
LIMIT 20;                                                       -- List the top 20 users with the most memory resource consumption.

Troubleshooting​

If no audit logs are loaded to the table after 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, 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.