354 lines
13 KiB
Python
354 lines
13 KiB
Python
#!/usr/bin/env python3
|
|
"""Cross-reference live DHCP leases against Gaiia paying accounts.
|
|
|
|
Flags three classes of unpaid internet access on customer-facing pools:
|
|
|
|
(1) UNMATCHED -- bound DHCP lease whose MAC has no matching Gaiia
|
|
inventory item assigned to any account.
|
|
(2) UNASSIGNED -- bound lease whose MAC IS in Gaiia inventory but the
|
|
item isn't assigned to an Account (sitting in
|
|
InventoryLocation, NetworkSite, etc.).
|
|
(3) UNPAID -- bound lease assigned to a Gaiia account that has
|
|
zero ACTIVE/TRIAL billing subscriptions.
|
|
|
|
Sources of truth:
|
|
- Gaiia GraphQL (GAIIA_KEY env)
|
|
- MikroTik tower routers via mikrotik-tool API-SSL
|
|
|
|
Usage:
|
|
GAIIA_KEY=... ./scripts/freeloaders.py
|
|
GAIIA_KEY=... ./scripts/freeloaders.py --json > out.json # full dump
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import json
|
|
import os
|
|
import re
|
|
import subprocess
|
|
import sys
|
|
from concurrent.futures import ThreadPoolExecutor
|
|
from typing import Any
|
|
|
|
REPO = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
|
|
sys.path.insert(0, os.path.join(REPO, "gaiia", "src"))
|
|
from gaiia import GaiiaClient # noqa: E402
|
|
|
|
# Routers that hand out customer DHCP. Skip `home` (graham's house) and `edge`
|
|
# (no customer pools) by default.
|
|
TOWER_ROUTERS = ["verona", "climax", "culleoka", "newhope", "lowry", "982", "494", "core"]
|
|
MTT = os.path.join(REPO, "mikrotik-tool", "mikrotik-tool")
|
|
|
|
CUSTOMER_KEYWORDS = ("cpe", "cgnat", "customer", "hotspot")
|
|
INFRA_KEYWORDS = ("tower", "infra", "mgmt", "management")
|
|
|
|
|
|
def norm_mac(s: str) -> str:
|
|
return re.sub(r"[^0-9a-f]", "", s.lower())
|
|
|
|
|
|
def parse_kv_line(line: str) -> dict[str, str]:
|
|
"""Parse a `.id=*X key=value key=value` line from mikrotik-tool api output."""
|
|
kv: dict[str, str] = {}
|
|
for part in re.split(r" +", line.strip()):
|
|
if "=" in part:
|
|
k, v = part.split("=", 1)
|
|
kv[k] = v
|
|
return kv
|
|
|
|
|
|
def fetch_leases(router: str) -> list[dict[str, Any]]:
|
|
proc = subprocess.run(
|
|
[MTT, "api", router, "/ip/dhcp-server/lease/print"],
|
|
capture_output=True, text=True, timeout=90,
|
|
cwd=os.path.join(REPO, "mikrotik-tool"),
|
|
)
|
|
if proc.returncode != 0:
|
|
print(f"!! {router}: {proc.stderr.strip()[:200]}", file=sys.stderr)
|
|
return []
|
|
out: list[dict[str, Any]] = []
|
|
for line in proc.stdout.splitlines():
|
|
if not line.startswith(".id="):
|
|
continue
|
|
kv = parse_kv_line(line)
|
|
if kv.get("status") != "bound":
|
|
continue
|
|
mac = kv.get("active-mac-address") or kv.get("mac-address") or ""
|
|
if not mac:
|
|
continue
|
|
out.append({
|
|
"router": router,
|
|
"mac": norm_mac(mac),
|
|
"mac_raw": mac,
|
|
"address": kv.get("active-address") or kv.get("address") or "",
|
|
"host": kv.get("host-name", ""),
|
|
"server": kv.get("active-server") or kv.get("server", ""),
|
|
"comment": kv.get("comment", ""),
|
|
})
|
|
return out
|
|
|
|
|
|
def paginate(client: GaiiaClient, query_with_after: str, query_without_after: str, path: str):
|
|
"""Iterate Connection nodes; uses a separate first-page query that omits $after
|
|
because the gaiia API rejects null cursor values."""
|
|
cursor: str | None = None
|
|
while True:
|
|
if cursor is None:
|
|
data = client.query(query_without_after)
|
|
else:
|
|
data = client.query(query_with_after, {"after": cursor})
|
|
node = data
|
|
for part in path.split("."):
|
|
node = node[part]
|
|
for n in node.get("nodes") or []:
|
|
yield n
|
|
pi = node.get("pageInfo") or {}
|
|
if not pi.get("hasNextPage"):
|
|
return
|
|
cursor = pi.get("endCursor")
|
|
if not cursor:
|
|
return
|
|
|
|
|
|
def fetch_gaiia() -> tuple[dict[str, dict], dict[str, dict]]:
|
|
mac_to_item: dict[str, dict] = {}
|
|
account_info: dict[str, dict] = {}
|
|
|
|
accts_with = """
|
|
query Q($after: String!) {
|
|
accounts(first: 100, after: $after) {
|
|
nodes {
|
|
id readableId displayName
|
|
status { name }
|
|
billingSubscriptions(first: 50) { nodes { id status } }
|
|
}
|
|
pageInfo { hasNextPage endCursor }
|
|
}
|
|
}"""
|
|
accts_first = """
|
|
query {
|
|
accounts(first: 100) {
|
|
nodes {
|
|
id readableId displayName
|
|
status { name }
|
|
billingSubscriptions(first: 50) { nodes { id status } }
|
|
}
|
|
pageInfo { hasNextPage endCursor }
|
|
}
|
|
}"""
|
|
|
|
items_with = """
|
|
query Q($after: String!) {
|
|
inventoryItems(first: 100, after: $after) {
|
|
nodes {
|
|
id
|
|
ipAddressV4
|
|
model { name }
|
|
fields { nodes { data modelField { name } } }
|
|
assignation {
|
|
assigneeType
|
|
assignee {
|
|
__typename
|
|
... on Account { id readableId displayName }
|
|
}
|
|
}
|
|
}
|
|
pageInfo { hasNextPage endCursor }
|
|
}
|
|
}"""
|
|
items_first = """
|
|
query {
|
|
inventoryItems(first: 100) {
|
|
nodes {
|
|
id
|
|
ipAddressV4
|
|
model { name }
|
|
fields { nodes { data modelField { name } } }
|
|
assignation {
|
|
assigneeType
|
|
assignee {
|
|
__typename
|
|
... on Account { id readableId displayName }
|
|
}
|
|
}
|
|
}
|
|
pageInfo { hasNextPage endCursor }
|
|
}
|
|
}"""
|
|
|
|
with GaiiaClient(timezone="America/Chicago") as g:
|
|
for a in paginate(g, accts_with, accts_first, "accounts"):
|
|
subs = (a.get("billingSubscriptions") or {}).get("nodes") or []
|
|
statuses = [s["status"] for s in subs]
|
|
account_info[a["id"]] = {
|
|
"readable": a["readableId"],
|
|
"name": a["displayName"],
|
|
"status": a["status"]["name"],
|
|
"paying": any(s in ("ACTIVE", "TRIAL") for s in statuses),
|
|
"sub_statuses": statuses,
|
|
}
|
|
|
|
for it in paginate(g, items_with, items_first, "inventoryItems"):
|
|
macs = []
|
|
for f in ((it.get("fields") or {}).get("nodes") or []):
|
|
mf = f.get("modelField") or {}
|
|
if mf.get("name", "").lower().startswith("mac") and f.get("data"):
|
|
macs.append(norm_mac(f["data"]))
|
|
assn = it.get("assignation") or {}
|
|
assignee = assn.get("assignee") or {}
|
|
atype = assignee.get("__typename")
|
|
for m in macs:
|
|
if len(m) != 12:
|
|
continue
|
|
rec = {
|
|
"item_id": it["id"],
|
|
"model": (it.get("model") or {}).get("name", ""),
|
|
"ip": it.get("ipAddressV4"),
|
|
"assignee_type": atype,
|
|
}
|
|
if atype == "Account":
|
|
rec["account_id"] = assignee["id"]
|
|
# Prefer Account-assigned items if MAC is duplicated in inventory
|
|
if m in mac_to_item and mac_to_item[m].get("assignee_type") == "Account":
|
|
continue
|
|
mac_to_item[m] = rec
|
|
|
|
return mac_to_item, account_info
|
|
|
|
|
|
def _name_tokens(s: str) -> set[str]:
|
|
return {w for w in re.split(r"[^a-z0-9]+", s.lower()) if len(w) >= 3}
|
|
|
|
|
|
def fuzzy_account_match(host: str, accounts: dict[str, dict]) -> dict | None:
|
|
"""Best-effort account lookup by DHCP host-name against displayName.
|
|
Requires >=2 token overlap (first+last name) to reduce false positives."""
|
|
if not host:
|
|
return None
|
|
htoks = _name_tokens(host)
|
|
if len(htoks) < 2:
|
|
return None
|
|
best = None
|
|
best_score = 0
|
|
for aid, a in accounts.items():
|
|
atoks = _name_tokens(a["name"])
|
|
score = len(htoks & atoks)
|
|
if score >= 2 and score > best_score:
|
|
best_score = score
|
|
best = {**a, "id": aid, "match_score": score}
|
|
return best
|
|
|
|
|
|
def classify(leases, mac_to_item, accounts):
|
|
unmatched_paying, unmatched_unpaid, unmatched_unknown = [], [], []
|
|
unassigned, unpaid = [], []
|
|
for lease in leases:
|
|
server = lease["server"].lower()
|
|
if not any(k in server for k in CUSTOMER_KEYWORDS):
|
|
continue
|
|
if any(k in lease["comment"].lower() for k in ("backhaul", "exclusion", "infra")):
|
|
continue
|
|
mac = lease["mac"]
|
|
item = mac_to_item.get(mac)
|
|
if not item:
|
|
# Fall back to name match on DHCP host-name
|
|
guess = fuzzy_account_match(lease["host"], accounts)
|
|
if guess is None:
|
|
unmatched_unknown.append(lease)
|
|
elif guess["paying"]:
|
|
unmatched_paying.append((lease, guess))
|
|
else:
|
|
unmatched_unpaid.append((lease, guess))
|
|
elif item.get("assignee_type") != "Account":
|
|
unassigned.append((lease, item))
|
|
else:
|
|
acct = accounts.get(item["account_id"])
|
|
if not acct or not acct["paying"]:
|
|
unpaid.append((lease, item, acct))
|
|
return unmatched_paying, unmatched_unpaid, unmatched_unknown, unassigned, unpaid
|
|
|
|
|
|
def main():
|
|
ap = argparse.ArgumentParser()
|
|
ap.add_argument("--json", action="store_true", help="emit machine-readable JSON")
|
|
ap.add_argument("--routers", help="comma-separated subset of routers")
|
|
args = ap.parse_args()
|
|
|
|
if not os.environ.get("GAIIA_KEY"):
|
|
sys.exit("GAIIA_KEY not set")
|
|
|
|
routers = args.routers.split(",") if args.routers else TOWER_ROUTERS
|
|
|
|
print("Fetching Gaiia inventory + accounts...", file=sys.stderr)
|
|
mac_to_item, accounts = fetch_gaiia()
|
|
paying = sum(1 for a in accounts.values() if a["paying"])
|
|
print(f" {len(accounts)} accounts ({paying} paying), "
|
|
f"{len(mac_to_item)} MACs in inventory "
|
|
f"({sum(1 for v in mac_to_item.values() if v.get('assignee_type')=='Account')} account-assigned)",
|
|
file=sys.stderr)
|
|
|
|
print(f"Fetching DHCP leases from {len(routers)} routers in parallel...", file=sys.stderr)
|
|
with ThreadPoolExecutor(max_workers=len(routers)) as ex:
|
|
results = list(ex.map(fetch_leases, routers))
|
|
leases = [l for sub in results for l in sub]
|
|
cust_leases = [l for l in leases if any(k in l["server"].lower() for k in CUSTOMER_KEYWORDS)]
|
|
print(f" {len(leases)} bound leases ({len(cust_leases)} on customer pools)", file=sys.stderr)
|
|
|
|
um_paying, um_unpaid, um_unknown, unassigned, unpaid = classify(leases, mac_to_item, accounts)
|
|
|
|
if args.json:
|
|
json.dump({
|
|
"summary": {
|
|
"accounts": len(accounts),
|
|
"paying_accounts": paying,
|
|
"bound_leases_total": len(leases),
|
|
"bound_leases_customer_pools": len(cust_leases),
|
|
"unmatched_paying_by_name": len(um_paying),
|
|
"unmatched_unpaid_by_name": len(um_unpaid),
|
|
"unmatched_unknown": len(um_unknown),
|
|
"unassigned_in_gaiia": len(unassigned),
|
|
"unpaid_account": len(unpaid),
|
|
},
|
|
"unmatched_paying": [{"lease": l, "account": a} for l, a in um_paying],
|
|
"unmatched_unpaid": [{"lease": l, "account": a} for l, a in um_unpaid],
|
|
"unmatched_unknown": um_unknown,
|
|
"unassigned": [{"lease": l, "item": i} for l, i in unassigned],
|
|
"unpaid": [{"lease": l, "item": i, "account": a} for l, i, a in unpaid],
|
|
}, sys.stdout, indent=2, default=str)
|
|
return
|
|
|
|
print()
|
|
print(f"### FREELOADER REPORT ###")
|
|
print()
|
|
print(f"!!! {len(unpaid)} CRITICAL: bound CPE on account with NO ACTIVE subscription !!!")
|
|
for l, i, a in sorted(unpaid, key=lambda x: (x[0]["router"], x[0]["address"])):
|
|
nm, st = a["name"], a["status"]
|
|
subs = ",".join(a["sub_statuses"]) if a["sub_statuses"] else "<none>"
|
|
print(f" {l['router']:9} {l['address']:16} {l['mac_raw']:17} "
|
|
f"host={l['host']!r} acct=#{a['readable']} {nm!r} status={st} subs=[{subs}]")
|
|
|
|
print()
|
|
print(f"!!! {len(um_unpaid)} CRITICAL: MAC unknown to Gaiia, but host-name matches a NON-paying account !!!")
|
|
for l, a in sorted(um_unpaid, key=lambda x: (x[0]["router"], x[0]["address"])):
|
|
subs = ",".join(a["sub_statuses"]) if a["sub_statuses"] else "<none>"
|
|
print(f" {l['router']:9} {l['address']:16} {l['mac_raw']:17} "
|
|
f"host={l['host']!r} -> acct=#{a['readable']} {a['name']!r} status={a['status']} subs=[{subs}]")
|
|
|
|
print()
|
|
print(f"--- {len(unassigned)} bound CPE: inventory item exists but assigned to InventoryLocation/etc (not an Account) ---")
|
|
for l, i in sorted(unassigned, key=lambda x: (x[0]["router"], x[0]["address"])):
|
|
print(f" {l['router']:9} {l['address']:16} {l['mac_raw']:17} "
|
|
f"item={i['item_id']} assignee_type={i.get('assignee_type')} host={l['host']!r}")
|
|
|
|
print()
|
|
print(f"--- {len(um_unknown)} UNKNOWN: bound lease, no MAC match and no fuzzy name match to any account ---")
|
|
for l in sorted(um_unknown, key=lambda x: (x["router"], x["address"])):
|
|
print(f" {l['router']:9} {l['address']:16} {l['mac_raw']:17} "
|
|
f"server={l['server']!r} host={l['host']!r}")
|
|
|
|
print()
|
|
print(f"(info) {len(um_paying)} bound leases not in Gaiia inventory but host-name matches a PAYING account (likely just inventory gap, not freeloaders)")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|