Finding Hot Directories by IOPS and Capacity Using the VAST Audit Database

This document is a recipe, not a setup guide: it assumes AuditDB querying is already working (auditing enabled with VAST DB as a destination, a user with the right identity policy, and either Trino or the vastdb Python SDK connected). For all of that, see How To Audit DB Query Guide, the primary reference for getting AuditDB access set up and queryable, including the permission model and the tx.audit_log() SDK pattern used below.

What follows solves one specific problem: ranking directories by IOPS (operation count) and capacity, truncated to a configurable number of path levels, in both SQL (Trino) and the Python SDK.

1. Trino: Ranking Directories by GiB and Operation Count

The AuditDB table is addressed with a fixed catalog/schema pair, "vast-audit-log-bucket|vast_audit_log_schema".vast_audit_log_table, regardless of cluster. This is the same name whether you're querying from Trino directly, from a dashboard tool like Superset, or from a notebook.

Build the directory prefix with regexp_extract(); the leading / is now optional so it also produces a real prefix for S3 object keys, which silently collapsed to NULL under the old pattern.

SELECT
    regexp_extract(path.path, '^/?(?:[^/]*/){0,3}') AS directory,
    SUM(num_bytes) / POWER(1024, 3) AS total_gib,
    SUM(num_ops) AS total_ops
FROM "vast-audit-log-bucket|vast_audit_log_schema".vast_audit_log_table
WHERE path.path IS NOT NULL
GROUP BY 1
ORDER BY total_gib DESC
LIMIT 10;

Output (3 levels deep, example cluster):

                       directory                       |     total_gib      | total_ops
-------------------------------------------------------+--------------------+------------
 /example-user/nfs1/vast/                              |  50610.12424850464 | 2332065741
 /audit-test/shared/                                   |   62.5769499829039 |     257462
 /clickhouse/wikistat-insert-204-20260722T170131Z/xos/ | 19.829966090619564 |          1
 /clickhouse/wikistat-insert-204-20260722T170131Z/oak/ | 15.355074187740684 |          1

Adjusting Directory Depth

Each {0,N} in the regex bounds how many directory levels are captured. Change N to change the depth:

2. VAST DB Python SDK: Same Query via SDK + DuckDB

In current versions of VASTDB, GROUP BY aggregations are not pushed to the server. Therefore, pull the relevant columns with predicate pushdown, then aggregate without materializing the full result: select() returns a pyarrow.RecordBatchReader, a lazy iterator, and DuckDB can scan that reader directly and run the same GROUP BY used in the Trino query above.

Using this combination of the SDK+DuckDB is an easy way to get a speedup, so use it whenever you know you'll have a large number of records being returned by the filter/predicate.

Use the nested path struct column, the same field Trino queries as path.path. Avoid the flat
view_path column: it is not the operation's target path, it's the VIEW/export the operation
went through. For more details on this, see the
Troubleshooting section below.

import os
import duckdb
import vastdb
from vastdb.config import QueryConfig
endpoint = os.environ["VASTDB_ENDPOINT"]
session = vastdb.connect(
    endpoint=endpoint,
    access=os.environ["AWS_ACCESS_KEY_ID"],
    secret=os.environ["AWS_SECRET_ACCESS_KEY"],
)
config = QueryConfig(
    data_endpoints=[endpoint] * 4,
    num_splits=4,
)
with session.transaction() as tx:
    reader = tx.audit_log().select(
        columns=["path", "num_bytes", "num_ops"],
        config=config,
    )
    summary = duckdb.sql("""
        WITH flat AS (
            SELECT struct_extract(path, 'path') AS directory_path, num_bytes, num_ops
            FROM reader
        )
        SELECT
            regexp_extract(directory_path, '^/?(?:[^/]*/){0,3}') AS directory,
            SUM(num_bytes) / POWER(1024, 3) AS total_gib,
            SUM(num_ops) AS total_ops
        FROM flat
        WHERE directory_path IS NOT NULL
        GROUP BY 1
        ORDER BY total_gib DESC
        LIMIT 10
    """).df()
print(summary.to_string(index=False))

Note:

tx.audit_log() opens the AuditDB's managed table directly. It won't show up in tx.bucket() or a bucket listing, that's expected for a system-managed bucket.

Handing the reader straight to DuckDB keeps the query off the client's heap: eg, on a query vs 25.3 million rows, read_all() followed by a pandas groupby costs about 16.5 GB of memory and 112 seconds, while the same window streamed through DuckDB runs in about 6 seconds using roughly 1.3 GB.

The QueryConfig above fans the read out across four workers instead of sending it through a single connection to a single CNode. Both data_endpoints and num_splits have to be set together (repeating the pool's DNS name four times is enough).

Output:

directory                  total_gib    total_ops
/example-user/nfs1/vast/   50610.124249 2332065741
/audit-test/shared/        62.576950     257462

Filtering by Time Window

Both num_ops and num_bytes are cumulative totals over whatever window you query, unbounded by default (all retained audit history). To get a meaningful IOPS figure rather than a lifetime operation count, add a time predicate and divide by the window length.

The predicate pushes down to the server rather than filtering after the fetch: on a 25.35-million-row day, a 5-minute predicate returns 85,131 rows in 0.59 seconds, against 12 seconds to read the full day unfiltered. Pass it to select(), not as a filter on the result:

from ibis.expr.types import relations as _
from datetime import datetime, timedelta, timezone
window = timedelta(hours=24)
cutoff = datetime.now(timezone.utc) - window
with session.transaction() as tx:
    reader = tx.audit_log().select(
        columns=["path", "num_bytes", "num_ops"],
        predicate=(_.time > cutoff),
        config=config,
    )
    summary = duckdb.sql("""
        WITH flat AS (
            SELECT struct_extract(path, 'path') AS directory_path, num_bytes, num_ops
            FROM reader
        )
        SELECT
            regexp_extract(directory_path, '^/?(?:[^/]*/){0,3}') AS directory,
            SUM(num_bytes) / POWER(1024, 3) AS total_gib,
            SUM(num_ops) AS total_ops
        FROM flat
        WHERE directory_path IS NOT NULL
        GROUP BY 1
        ORDER BY total_gib DESC
        LIMIT 10
    """).df()
summary["avg_ops_per_sec"] = summary["total_ops"] / window.total_seconds()

Example: last 10 minutes only, window = timedelta(minutes=10), then filter with predicate=(_.time > cutoff) as above using that window.

The equivalent Trino version adds AND time > current_timestamp - interval '24' hour to the WHERE clause. For the last 10 minutes, use AND time > current_timestamp - interval '10' minute:

SELECT
    regexp_extract(path.path, '^/?(?:[^/]*/){0,3}') AS directory,
    SUM(num_bytes) / POWER(1024, 3) AS total_gib,
    SUM(num_ops) AS total_ops
FROM "vast-audit-log-bucket|vast_audit_log_schema".vast_audit_log_table
WHERE path.path IS NOT NULL
  AND time > current_timestamp - interval '10' minute
GROUP BY 1
ORDER BY total_gib DESC
LIMIT 10;

Tips for Scale and Accuracy

• GiB vs. GB: dividing by POWER(1024, 3) (or 1024 ** 3 in Python) yields GiB (binary), not decimal GB. Use 1000 ** 3 if you need decimal GB instead.

• Bound your time range. Without a time filter, num_ops/num_bytes totals cover the full audit retention window, not a rate. See Filtering by Time Window above.

• Avoid read_all() on this table. A 24-hour window of 25.3 million rows costs about 16.5 GB of client memory and 112 seconds to materialize with read_all() + to_pandas(); the same window streamed straight into DuckDB runs in about 6 seconds using roughly 1.3 GB. Add a time predicate as well: a 5-minute window returns in well under a second, compared with 12 seconds for the full day unfiltered.

Troubleshooting

Issue: AccessDenied or empty results, or general AuditDB connectivity problems

1. Not covered here; see the permissions and setup steps in How To Audit DB Query Guide.

Conclusion

Both the Trino and VAST DB Python SDK paths query the same underlying AuditDB table, so you can pick whichever fits your existing tooling: Trino for dashboards (Superset, Grafana) and ad hoc SQL, the Python SDK for scripted reports or notebooks. Use the regexp_extract() approach shown above over a naive array-slice version in both cases, it's the one verified against production audit data to include shallow directories and S3 object keys correctly.

Links and References

Official Documentation

• 6. Auditing

Tool Resources

• VAST DB Python SDK documentation

• vastdb_sdk on GitHub

• VAST DB Python SDK 2.0.14.1 and VAST 5.4.x

Community and Support

• How To Audit DB Query Guide: primary reference for AuditDB setup, permissions, and SDK/Trino access

• Query Audit Log via Jupyter and Trino (Field Docs)

• VAST Grafana Audit Dashboard

Citations

Related articles (as listed on the KB page):

• How To Audit DB Query Guide -- Solutions and Integrations > Database

• Querying the VAST Catalog -- Solutions and Integrations > Database

• Capacity Analysis with vastpy-cli -- Platform Knowledge > How-To