IPAM at Scale — Source-of-Truth & Live Validation

Problem

Tracking which IP addresses are actually in use across a large multi-site network usually lives in a stale spreadsheet nobody trusts. The source-of-truth and reality steadily diverge: an address gets assigned on a device but never recorded, or recorded once and never cleaned up. Once an IPAM record can’t be trusted, engineers stop consulting it — and the drift compounds from there.

The goal wasn’t a prettier database. It was an IPAM the team would actually believe, because it was continuously checked against the live network.

Solution

Two pieces working together.

A generator that builds a structured Google Sheets IPAM workbook per site — subnets, assignments, utilization, and conflict detection — via Apps Script, so the data lives where the team already works. Standing up a new site’s IPAM is adding a row of variables, not a manual build.

A validator — “intent vs reality” — that takes the source-of-truth and compares it against what’s genuinely live on the network: ARP tables, DHCP leases, and device captures. It flags the three deltas that matter: recorded-but-absent, present-but-unrecorded, and conflicting assignments. Crucially, the validator works entirely from captured data over a file bridge, so it needs no direct or live access into the secured environment.

Architecture

Source-of-truth  (Sheets / NetBox)         Device captures
        │                                    (ARP, DHCP, show output)
        └──────────────┬─────────────────────────┘
                       ▼
        Python reconciliation engine
            ├── recorded-but-absent
            ├── present-but-unrecorded
            └── conflicts
                       │  delta report
                       ▼
        Apps Script generator → per-site IPAM Sheet

The reconciliation runs against files, not the network — captures are dropped in, deltas come out — so it’s safe to run against environments with no inbound access and no stored credentials.

Key Decisions

Meet the team in Sheets. Adoption beats elegance. A perfect IPAM tool nobody opens is worthless; a good-enough one in the spreadsheet they already use gets maintained.

Intent-vs-reality as a first-class output. The value isn’t the database — it’s the diff between the database and the live network. That diff is what rebuilds trust in the records.

File-bridge, no live access. The validator consumes captures rather than reaching into the network, so it runs safely against locked-down environments where inbound access and stored creds aren’t allowed.

Per-site isolation. Each site gets its own workbook and variable set, so there’s no cross-site contamination and a new site is a few minutes of setup.

Results

• IP records the team will actually trust, because they’re continuously reconciled to reality
• Stale, unrecorded, and conflicting addresses surfaced automatically instead of discovered during an incident
• Runs against secured networks with zero live access — entirely capture-driven
• The same generator stands up a new site’s IPAM workbook in minutes

How This Scales

• NetBox as canonical source — keep the source-of-truth in NetBox and use this purely as the validation layer.
• Scheduled reconciliation — nightly intent-vs-reality runs with a delta journal.
• Stale-lease reclaim — auto-flag and reclaim addresses live on neither ARP nor DHCP.
• Utilization forecasting — trend subnet utilization to warn before a scope runs dry.

Tech Stack

• Reconciliation: Python (intent-vs-reality engine, capture parsing)
• IPAM dashboard: Google Apps Script + Google Sheets (per-site workbooks)
• Reality sources: ARP, DHCP leases, device captures; NetBox / SNMP where available
• Transport: file bridge (no live network access required)