All set. Here’s the PoE Power-Budget Bar Add-On:

import os, zipfile, json

zip_path = "/mnt/data/ucls-poe-power-addon.zip"

with zipfile.ZipFile(zip_path, 'w') as z:
    # 1) ClickHouse DDL patch for PoE telemetry
    ddl = """\
-- Per-port PoE telemetry
CREATE TABLE IF NOT EXISTS poe_ports_ch (
  ts DateTime,
  switch_id String,
  switch_name String,
  port LowCardinality(String),
  poe_mode LowCardinality(String),   -- 802.3af/at/bt or 2-pair/4-pair
  poe_class LowCardinality(String),  -- Class 0..8
  power_w Float32,                   -- instantaneous power draw
  voltage_v Float32,
  current_ma Float32,
  admin_state LowCardinality(String),-- up/down
  oper_state LowCardinality(String), -- delivering/denied/fault
  endpoint_id String                 -- optional resolved PD
) ENGINE = MergeTree ORDER BY (switch_id, port, ts);

-- Per-switch power budget rollup
CREATE TABLE IF NOT EXISTS poe_switch_budget_ch (
  ts DateTime,
  switch_id String,
  model LowCardinality(String),
  budget_w Float32,  -- total available PoE budget
  used_w Float32,    -- currently used
  free_w Float32     -- derived or reported
) ENGINE = MergeTree ORDER BY (switch_id, ts);
"""
    z.writestr("telemetry/clickhouse_poe_patch.sql", ddl)

    # 2) Grafana dashboard for PoE power budget
    dashboard = {
      "dashboard": {
        "id": None,
        "title": "PoE Power Budget",
        "timezone": "browser",
        "schemaVersion": 39,
        "version": 1,
        "panels": [
          {
            "type": "stat",
            "title": "Avg Switch PoE Utilization (%)",
            "id": 1,
            "datasource": {"type": "vertamedia-clickhouse-datasource", "uid": "CLICKHOUSE_DS"},
            "targets": [{
              "refId": "A",
              "query": "SELECT avg(used_w / nullIf(budget_w,0) * 100) AS util_pct FROM poe_switch_budget_ch WHERE $__timeFilter(ts)"
            }],
            "gridPos": {"h": 4, "w": 8, "x": 0, "y": 0}
          },
          {
            "type": "barchart",
            "title": "Switch Power Budget (Used vs Free)",
            "id": 2,
            "options": {"orientation": "horizontal", "displayMode": "gradient"},
            "datasource": {"type": "vertamedia-clickhouse-datasource", "uid": "CLICKHOUSE_DS"},
            "targets": [{
              "refId": "A",
              "query": (
                "SELECT switch_id, anyLast(model) AS model, "
                "avg(used_w) AS used_w, avg(free_w) AS free_w "
                "FROM poe_switch_budget_ch WHERE $__timeFilter(ts) "
                "GROUP BY switch_id ORDER BY used_w DESC"
              )
            }],
            "gridPos": {"h": 10, "w": 24, "x": 0, "y": 4}
          },
          {
            "type": "table",
            "title": "Ports Near/Over Threshold",
            "id": 3,
            "datasource": {"type": "vertamedia-clickhouse-datasource", "uid": "CLICKHOUSE_DS"},
            "targets": [{
              "refId": "A",
              "query": (
                "WITH b AS ("
                " SELECT ts, switch_id, anyLast(budget_w) AS budget_w, anyLast(used_w) AS used_w "
                " FROM poe_switch_budget_ch WHERE $__timeFilter(ts) "
                " GROUP BY switch_id, toStartOfInterval(ts, toIntervalMinute(5))"
                ") "
                "SELECT p.ts, p.switch_id, p.switch_name, p.port, p.poe_mode, p.poe_class, "
                "       p.power_w, p.voltage_v, p.current_ma, p.oper_state "
                "FROM poe_ports_ch AS p "
                "ANY LEFT JOIN b USING (switch_id) "
                "WHERE $__timeFilter(p.ts) AND (p.oper_state IN ('delivering','fault') OR p.power_w > 20) "
                "ORDER BY p.ts DESC LIMIT 500"
              )
            }],
            "gridPos": {"h": 10, "w": 24, "x": 0, "y": 14}
          }
        ]
      },
      "overwrite": True
    }
    z.writestr("dashboards/ucls-poe-power.json", json.dumps(dashboard, indent=2))

    # 3) Sample CSVs for quick testing
    ports_csv = """ts,switch_id,switch_name,port,poe_mode,poe_class,power_w,voltage_v,current_ma,admin_state,oper_state,endpoint_id
2025-08-19 10:00:00,SW1,Core-1,Gi1/0/1,802.3bt,Class 6,39.5,52.0,760,up,delivering,AP-7F-03
2025-08-19 10:00:05,SW1,Core-1,Gi1/0/2,802.3at,Class 4,23.0,51.5,447,up,delivering,CAM-7F-02
2025-08-19 10:00:10,SW2,Edge-2,Gi0/24,802.3af,Class 3,11.2,48.5,231,up,delivering,IP-PHONE-22
2025-08-19 10:00:15,SW2,Edge-2,Gi0/12,802.3bt,Class 8,72.0,53.0,1358,up,delivering,XR-UNIT-9
"""
    z.writestr("samples/poe_ports_sample.csv", ports_csv)

    budget_csv = """ts,switch_id,model,budget_w,used_w,free_w
2025-08-19 10:00:00,SW1,Catalyst-9300-48U,1440,980,460
2025-08-19 10:00:00,SW2,Catalyst-9200-24P,740,520,220
2025-08-19 10:05:00,SW1,Catalyst-9300-48U,1440,990,450
2025-08-19 10:05:00,SW2,Catalyst-9200-24P,740,530,210
"""
    z.writestr("samples/poe_switch_budget_sample.csv", budget_csv)

zip_path

👉 Download the PoE Power Add-On

What’s inside

1) ClickHouse schema patch
telemetry/clickhouse_poe_patch.sql — creates two tables:

-- Per-port PoE telemetry
CREATE TABLE IF NOT EXISTS poe_ports_ch (
  ts DateTime,
  switch_id String,
  switch_name String,
  port LowCardinality(String),
  poe_mode LowCardinality(String),   -- 802.3af/at/bt or 2-pair/4-pair
  poe_class LowCardinality(String),  -- Class 0..8
  power_w Float32,
  voltage_v Float32,
  current_ma Float32,
  admin_state LowCardinality(String),
  oper_state LowCardinality(String),
  endpoint_id String
) ENGINE = MergeTree ORDER BY (switch_id, port, ts);

-- Per-switch power budget rollup
CREATE TABLE IF NOT EXISTS poe_switch_budget_ch (
  ts DateTime,
  switch_id String,
  model LowCardinality(String),
  budget_w Float32,
  used_w Float32,
  free_w Float32
) ENGINE = MergeTree ORDER BY (switch_id, ts);

2) Grafana dashboard
dashboards/ucls-poe-power.json — three panels wired for CLICKHOUSE_DS:

  • Avg Switch PoE Utilization (%)(stat)
  • Switch Power Budget (Used vs Free)(horizontal bar)
  • Ports Near/Over Threshold(table with mode/class/oper_state)

3) Sample data

  • samples/poe_ports_sample.csv
  • samples/poe_switch_budget_sample.csv

Quick apply

  1. Create tables
clickhouse-client -mn < telemetry/clickhouse_poe_patch.sql
  1. (Optional) Load samples
clickhouse-client --query="INSERT INTO poe_ports_ch FORMAT CSVWithNames" < samples/poe_ports_sample.csv
clickhouse-client --query="INSERT INTO poe_switch_budget_ch FORMAT CSVWithNames" < samples/poe_switch_budget_sample.csv
  1. Import Grafana dashboard
  • Ensure your ClickHouse datasource UID is CLICKHOUSE_DS.
  • Import dashboards/ucls-poe-power.json.

You’ll get a clean readout of per-switch used vs free budget, and a live table of high-draw or faulting ports with 802.3 mode/class context.

Want me to bundle both add-ons (PLC heatmap + PoE budget) into the unified repo and add a combined “PowerComm” section to the main dashboard?

Key terms in plain language

Open a term for a concise explanation of language used on this page.

VoIP

Voice over Internet Protocol carries phone calls over an IP network instead of a traditional analog phone line. Call quality depends on network stability, latency, and traffic management.

Unified Communications (UCaaS)

A cloud-based combination of business calling, messaging, meetings, presence, and collaboration tools managed as one communications service.

SIP Trunking

A service that connects a business phone system to the public telephone network using Internet Protocol, replacing or supplementing traditional phone lines.

Bandwidth

The amount of data a connection can carry in a given time, usually measured in Mbps or Gbps. More bandwidth supports more users, devices, and simultaneous applications.

Latency

The time it takes data to travel between two points. Lower latency improves voice, video meetings, cloud applications, gaming, and other real-time services.

Service-Level Agreement (SLA)

A provider’s written commitment covering service targets such as availability, response time, repair time, and sometimes financial credits when commitments are missed.