Project: subnet and IP usage report

Goal and what you will build

When a DHCP (Dynamic Host Configuration Protocol) scope runs out, new laptops get no address and the helpdesk gets a flood of tickets. When a server subnet fills up, projects stall waiting for re-addressing. Both are capacity problems you can spot weeks ahead. You will build a report that shows, for each subnet, how many usable addresses exist, how many are seen in use and how close it is to full. It also lists addresses seen on the network that do not belong to any planned subnet, which can reveal misconfigured or unauthorised devices.

Design

Python’s built-in ipaddress module does the hard work: it understands networks, prefix lengths, usable host counts and whether an address belongs to a network. Data comes from three places:

• Plan: a YAML file (or an IPAM, IP Address Management, export) listing each subnet with a name and site.
• ARP (Address Resolution Protocol) tables from the layer 3 switches or routers that act as gateways, collected with Netmiko and TextFSM.
• DHCP leases exported as CSV from your DHCP server.

# subnets.yaml
- {name: STAFF-LON,    cidr: 192.168.10.0/24}
- {name: STUDENTS-LON, cidr: 192.168.20.0/23}
- {name: SERVERS,      cidr: 10.10.0.0/26}

Build it step by step

1. Install the packages: pip install netmiko pyyaml textfsm.
2. Collect ARP entries from each gateway with show ip arp parsed by TextFSM.
3. Load DHCP leases from a CSV export with an ip column.
4. Merge everything into one set of unique addresses, then count how many fall inside each subnet.
5. Print a table sorted by percentage used and write it to CSV.
6. Check one subnet by hand with the subnet calculator to confirm the usable host count.

#!/usr/bin/env python3
import csv, ipaddress, os
import yaml
from netmiko import ConnectHandler

GATEWAYS = ["192.168.10.1", "10.10.0.1"]
ALERT_PCT = 80

def arp_ips(host):
    with ConnectHandler(device_type="cisco_ios", host=host,
                        username=os.environ["NET_USER"],
                        password=os.environ["NET_PASS"]) as conn:
        rows = conn.send_command("show ip arp", use_textfsm=True)
    return {ipaddress.ip_address(r["ip_address"]) for r in rows}

def dhcp_ips(path="dhcp_leases.csv"):
    if not os.path.exists(path):
        return set()
    with open(path, newline="", encoding="utf-8") as f:
        return {ipaddress.ip_address(r["ip"]) for r in csv.DictReader(f)}

def main():
    plan = yaml.safe_load(open("subnets.yaml", encoding="utf-8"))
    nets = [(p["name"], ipaddress.ip_network(p["cidr"])) for p in plan]
    seen = dhcp_ips()
    for gw in GATEWAYS:
        seen |= arp_ips(gw)
    report, matched = [], set()
    for name, net in nets:
        usable = net.num_addresses - 2 if net.prefixlen < 31 else net.num_addresses
        used = {ip for ip in seen if ip in net}
        matched |= used
        pct = round(len(used) / usable * 100, 1)
        report.append((pct, name, str(net), len(used), usable))
    print(f"{'Subnet':14}{'CIDR':18}{'Used':>6}{'Usable':>8}{'%':>7}")
    for pct, name, cidr, used, usable in sorted(report, reverse=True):
        flag = "  <-- check" if pct >= ALERT_PCT else ""
        print(f"{name:14}{cidr:18}{used:6}{usable:8}{pct:7}{flag}")
    unknown = sorted(seen - matched)
    if unknown:
        print("Addresses outside any planned subnet:", *unknown)

if __name__ == "__main__":
    main()

Tip: ARP only shows hosts that talked recently, and the gateway’s own address is included. Treat the result as a minimum, combine it with DHCP data, and remember that statically addressed devices that are switched off will not appear.

How to extend it and show it

• Pull subnets from an IPAM such as NetBox through its API, and push observed addresses back as a "last seen" field.
• Add IPv6 neighbour tables; the ipaddress module handles IPv6 the same way, though percentage use is less meaningful in a /64.
• Store results weekly and graph growth to predict when each subnet will fill.
• Look up the vendor of unknown addresses from their MAC prefix with the MAC lookup tool.

For your portfolio, publish the code with a fictional subnet plan and a sample report. In the README, explain why ARP and DHCP data complement each other, how usable hosts are calculated (and the /31 and /32 special cases), and how the report supports capacity planning and security. It is a good way to show subnetting knowledge in code form.