---
title: "Finding Hot Directories by IOPS and Capacity Using the VAST Audit Database"
slug: "finding-hot-directories-by-iops-and-capacity-using-the-vast-audit-database"
description: "Learn to rank directories by IOPS and capacity using the VAST Audit Database with SQL and Python SDK for efficient data analysis and reporting."
updated: 2026-11-03T19:03:32Z
published: 2026-11-03T19:03:32Z
canonical: "docs.vastdata.com/finding-hot-directories-by-iops-and-capacity-using-the-vast-audit-database"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

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

# 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](/develop/docs/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.

```sql
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):

```plaintext
                       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:

| Depth | Pattern |
| --- | --- |
| 2 levels | `'^/?(?:[^/]*/){0,2}'` |
| 3 levels | `'^/?(?:[^/]*/){0,3}'` |
| 4 levels | `'^/?(?:[^/]*/){0,4}'` |

## 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](/kb/docs/finding-hot-directories-by-iops-and-capacity-using-the-vast-audit-database#troubleshooting) section below.

```python
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]
> 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:

```plaintext
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:

```python
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`:

```sql
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](/kb/docs/finding-hot-directories-by-iops-and-capacity-using-the-vast-audit-database#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](/develop/docs/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](/kb/docs/6-auditing)

### Tool Resources

- [VAST DB Python SDK documentation](https://vastdb-sdk.readthedocs.io/en/latest/)
- [vastdb_sdk on GitHub](https://github.com/vast-data/vastdb_sdk)
- [VAST DB Python SDK 2.0.14.1 and VAST 5.4.x](/develop/docs/vast-db-python-sdk-2-0-14-1-and-vast-5-4-x)

### Community and Support

- [How To Audit DB Query Guide](/develop/docs/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)](https://vast-data.github.io/data-platform-field-docs/vast_catalog/jupyter/auditlog.html)
- [VAST Grafana Audit Dashboard](https://grafana.com/grafana/dashboards/22292-vast-audit-dashboard/)

## Citations

| Source | Summary |
| --- | --- |
| [How to Audit DB Query Guide](/develop/docs/how-to-audit-db-query-guide) | Primary setup reference this doc defers to; also the source for the `tx.audit_log()` SDK pattern used in the Python example. |
| [Auditing](/kb/docs/6-auditing) | Auditing overview: AuditDB as a log destination and confirmation that `num_ops`/`num_bytes` are queryable fields. |
| [Query Audit Log via Jupyter and Trino](https://vast-data.github.io/data-platform-field-docs/vast_catalog/jupyter/auditlog.html) | Full audit table column reference used to confirm `path`, `num_ops`, `num_bytes`, `time` columns; nested `path`/`name` columns can error in some client tooling, but `view_path` is not an equivalent substitute (see Troubleshooting). |

Related articles (as listed on the KB page):

- [How To Audit DB Query Guide](/develop/docs/how-to-audit-db-query-guide) -- Solutions and Integrations > Database
- [Querying the VAST Catalog](/develop/docs/querying-the-vast-catalog) -- Solutions and Integrations > Database
- [Capacity Analysis with vastpy-cli](/kb/docs/capacity-analysis-with-vastpy-cli) -- Platform Knowledge > How-To