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().

SELECT
    regexp_extract(path.path, '^/([^/]*/)?([^/]*/)?([^/]*/)?') 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 ([^/]*/)? group in the regex matches one directory level. Add or remove groups to change the depth:

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

The Python SDK does not push GROUP BY aggregation to the server. Instead, pull the relevant columns with predicate pushdown, then aggregate client-side with pandas, the same pattern used for catalog reporting.

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. On a live cluster with 3 configured views it collapsed every row to one of 3 values
(/rogers-sphere, /, /kyle) instead of real per-object paths, silently producing a
single-bucket "top directory" of / with the right totals but no useful breakdown. See
Troubleshooting below.

import os
import re
import vastdb

session = vastdb.connect(
    endpoint=os.environ["VASTDB_ENDPOINT"],
    access=os.environ["AWS_ACCESS_KEY_ID"],
    secret=os.environ["AWS_SECRET_ACCESS_KEY"],
)

with session.transaction() as tx:
    table = tx.audit_log().select(
        columns=["path", "num_bytes", "num_ops"]
    ).read_all()

df = table.to_pandas()
# pyarrow struct columns come back as a Python dict per row; pull the "path" sub-field.
df["full_path"] = df["path"].apply(lambda p: p.get("path") if isinstance(p, dict) else p)

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.

Truncate each path to N directory levels, mirroring the same regex logic used in the Trino query above so the two methods agree:

def directory_prefix(path: str, levels: int = 3) -> str:
    """Truncate a path to its first N directory levels."""
    pattern = "^/" + "(?:[^/]*/)?" * levels
    match = re.match(pattern, path)
    return match.group(0) if match else path

df["directory"] = df["full_path"].apply(lambda p: directory_prefix(p, levels=3))

summary = (
    df.groupby("directory")
      .agg(total_bytes=("num_bytes", "sum"), total_ops=("num_ops", "sum"))
      .reset_index()
)
summary["total_gib"] = summary["total_bytes"] / (1024 ** 3)
summary = summary.sort_values("total_gib", ascending=False).head(10)

print(summary[["directory", "total_gib", "total_ops"]].to_string(index=False))

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:

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:
    table = tx.audit_log().select(
        columns=["path", "num_bytes", "num_ops"],
        predicate=(_.time > cutoff)
    ).read_all()

df = table.to_pandas()
df["full_path"] = df["path"].apply(lambda p: p.get("path") if isinstance(p, dict) else p)
# ... apply directory_prefix() to full_path and groupby as above, then:
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, '^/([^/]*/)?([^/]*/)?([^/]*/)?') 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.

• Unbounded reads are slow. A predicate-filtered, narrow-window query returns in under a second; an unbounded read_all() over a busy window can run for many minutes. Cap with a time predicate or limit_rows, or iterate in batches for large windows.

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() / directory_prefix() approach over the naive array-slice version in both cases, it's the one verified against production audit data to include shallow directories correctly.

Links and References

Official Documentation

• 6. Auditing

Tool Resources

• VAST DB Python SDK documentation

• vastdb_sdk on GitHub

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