http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> How can I filter only business user...
# questions-and-troubleshooting
z
@Rocky How can I filter only business user queries not system ones in auditloader table starrocks_audit_db__.starrocks_audit_tbl__?
r
To filter business user queries from system-generated or administrative noise in the 
starrocks_audit_tbl__
table, you can apply filters based on user identity, resource groups, and statement patterns. Because the audit table does not have a single "isSystem" flag, you typically identify business queries by excluding known system patterns. Recommended Filters 1. Filter by User Exclude the 
root
user or other administrative accounts if they are used only for maintenance. Business queries usually come from specific application users.
Copy code
sql
WHERE user NOT IN ('root', 'admin')
2. Filter by Resource Group StarRocks uses specific resource groups for internal tasks. For example, Materialized View (MV) refreshes are typically assigned to the 
default_mv_wg
group.
Copy code
sql
WHERE resourceGroup != 'default_mv_wg'
3. Filter by Database/Catalog Exclude queries targeting internal metadata databases or the audit database itself.
Copy code
sql
WHERE db NOT IN ('information_schema', '_statistics_', 'starrocks_audit_db__')
4. Filter by Statement Type (SQL Pattern) System queries and BI heartbeats often use 
SHOW
, 
SET
, or simple 
SELECT 1
statements. Business queries are typically 
SELECT
(DQL) or 
INSERT/UPDATE/DELETE
(DML) on business tables.
Copy code
sql
WHERE stmt NOT LIKE 'SHOW %'
  AND stmt NOT LIKE 'SET %'
  AND stmt NOT LIKE 'SELECT 1'
  AND stmt NOT LIKE 'DESC %'
  AND stmt NOT LIKE 'EXPLAIN %'
5. Filter by Client IP If your application or BI tool connects from a specific IP range, you can include only those. Conversely, exclude 
127.0.0.1
if it is only used for local health checks.
Copy code
sql
WHERE clientIp NOT LIKE '127.0.0.1%'
Example: "Clean" Business Query Filter A robust query to find actual business activity might look like this:
Copy code
sql
SELECT 
    timestamp, 
    user, 
    db, 
    queryTime, 
    stmt 
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE user NOT IN ('root')                           -- Exclude admin
  AND resourceGroup != 'default_mv_wg'               -- Exclude MV refreshes
  AND isQuery = 1                                    -- Focus on queries (1) vs connections (0)
  AND db NOT IN ('information_schema', '_statistics_') -- Exclude metadata lookups
  AND stmt NOT REGEXP '^(SHOW|SET|DESC|EXPLAIN|ADMIN)' -- Exclude command noise
ORDER BY timestamp DESC;
Note on 
fe.internal.log
 StarRocks actually logs many purely internal system operations (like heartbeat checks and background statistics collection) to a separate file called 
fe.internal.log
. These are not typically loaded into the 
starrocks_audit_tbl__
by the standard AuditLoader plugin, so your audit table is already partially filtered for user-initiated activity. The traffic you see in the audit table is generally everything coming through the MySQL/FE port. References • administration/management/audit_loader.md
z
@Rocky whats about isQuery flag?
r
The 
isQuery
flag in the 
starrocks_audit_tbl__
table is a TINYINT field used to distinguish between data queries and administrative commands. Meaning of the 
isQuery
flag: * 
isQuery = 1
: The statement is a standard data query (typically a 
SELECT
statement). These are usually the "business queries" you are looking for. * 
isQuery = 0
: The statement is a command or administrative action. This includes 
SHOW
, 
SET
, 
USE
, 
DESC
, 
EXPLAIN
, or connection management commands. How to use it for filtering: If you want to focus strictly on business-driven data retrieval and exclude the "noise" of BI tools checking metadata or users setting session variables, you should filter for 
isQuery = 1
. SQL Example:
Copy code
sql
SELECT 
    timestamp, 
    user, 
    db, 
    queryTime, 
    stmt 
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE isQuery = 1            -- Only actual data queries
  AND user != 'root'         -- Exclude the superuser
  AND queryType != 'connection' -- Exclude connection/disconnection events
ORDER BY timestamp DESC;
Key Differences to Note: * 
queryType
vs 
isQuery
: While 
isQuery
tells you if the SQL was a query, the 
queryType
column (which can be 
query
, 
slow_query
, or 
connection
) tells you the category of the log entry. Even a "slow_query" will have 
isQuery = 1
. * DML Statements: Note that statements like 
INSERT
, 
UPDATE
, or 
DELETE
might be categorized with 
isQuery = 0
or 
1
depending on your StarRocks version and how the 
AuditLoader
interprets them. Generally, 
isQuery = 1
is the safest way to find "Select-style" business activity. For the most accurate "business-only" view, it is best to combine 
isQuery = 1
with a filter to exclude the 
information_schema
and 
_statistics_
databases. References • administration/management/audit_loader.md References • administration/management/logs.md • administration/management/audit_loader.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md
Open in Slack
PreviousNext
Powered by