import os, json, zipfile
root = "/mnt/data/ucls-dns-packs"
os.makedirs(root, exist_ok=True)
# Paths
tele_dir = os.path.join(root, "telemetry")
dash_dir = os.path.join(root, "dashboards")
samples_dir = os.path.join(root, "samples")
os.makedirs(tele_dir, exist_ok=True)
os.makedirs(dash_dir, exist_ok=True)
os.makedirs(samples_dir, exist_ok=True)
# 1) Append PowerComm tables to main ClickHouse schema (create if missing)
schema_path = os.path.join(tele_dir, "clickhouse_schema.sql")
powercomm_sql = """
-- === PowerComm (PLC + PoE) ===
CREATE TABLE IF NOT EXISTS plc_spectrum_bins_ch (
ts DateTime,
endpoint_id String,
link_id String,
center_mhz Float32,
bin_width_khz Float32,
power_dbm Float32,
notch UInt8,
quality Float32
) ENGINE = MergeTree ORDER BY (endpoint_id, link_id, ts, center_mhz);
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);
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);
"""
mode = "a" if os.path.exists(schema_path) else "w"
with open(schema_path, mode, encoding="utf-8") as f:
f.write(powercomm_sql)
# 2) Write combined PowerComm dashboard
powercomm_dash = {
"dashboard": {
"id": None,
"title": "PowerComm — PLC & PoE",
"timezone": "browser",
"schemaVersion": 39,
"version": 1,
"panels": [
{
"type": "heatmap",
"title": "PLC Spectrum Heatmap (ClickHouse)",
"id": 1,
"datasource": {"type": "vertamedia-clickhouse-datasource", "uid": "CLICKHOUSE_DS"},
"targets": [{
"refId": "A",
"query": (
"SELECT ts, center_mhz, avg(power_dbm) AS value "
"FROM plc_spectrum_bins_ch "
"WHERE $__timeFilter(ts) "
"GROUP BY ts, center_mhz ORDER BY ts"
)
}],
"gridPos": {"h": 12, "w": 24, "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": 12}
},
{
"type": "table",
"title": "PoE Ports Near/Over Threshold",
"id": 3,
"datasource": {"type": "vertamedia-clickhouse-datasource", "uid": "CLICKHOUSE_DS"},
"targets": [{
"refId": "A",
"query": (
"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.admin_state, p.oper_state, p.endpoint_id "
"FROM poe_ports_ch AS p "
"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": 22}
}
]
},
"overwrite": True
}
with open(os.path.join(dash_dir, "ucls-powercomm.json"), "w", encoding="utf-8") as f:
json.dump(powercomm_dash, f, indent=2)
# 3) Update main Observability dashboard to include a PowerComm row
obs_path = os.path.join(dash_dir, "ucls-observability.json")
obs = {"title":"UCLS Observability","schemaVersion":39,"panels":[]} # fallback
if os.path.exists(obs_path):
with open(obs_path, "r", encoding="utf-8") as f:
try:
obs = json.load(f)
except Exception:
pass
base_id = max([p.get("id",0) for p in obs.get("panels",[])] + [0]) + 1
# Add a small row to summarize PowerComm
obs.setdefault("panels", []).extend([
{
"type":"stat","title":"Avg PLC Band Power (dBm)",
"id": base_id,
"datasource":{"type":"vertamedia-clickhouse-datasource","uid":"CLICKHOUSE_DS"},
"targets":[{"refId":"A","query":"SELECT avg(power_dbm) AS value FROM plc_spectrum_bins_ch WHERE $__timeFilter(ts)"}],
"gridPos":{"h":4,"w":8,"x":0,"y":base_id} # quick layout heuristic
},
{
"type":"stat","title":"Avg PoE Utilization (%)",
"id": base_id+1,
"datasource":{"type":"vertamedia-clickhouse-datasource","uid":"CLICKHOUSE_DS"},
"targets":[{"refId":"A","query":"SELECT avg(used_w / nullIf(budget_w,0) * 100) FROM poe_switch_budget_ch WHERE $__timeFilter(ts)"}],
"gridPos":{"h":4,"w":8,"x":8,"y":base_id}
},
{
"type":"table","title":"Recent PoE Events",
"id": base_id+2,
"datasource":{"type":"vertamedia-clickhouse-datasource","uid":"CLICKHOUSE_DS"},
"targets":[{"refId":"A","query":"SELECT ts, switch_id, port, poe_mode, poe_class, power_w, oper_state FROM poe_ports_ch WHERE $__timeFilter(ts) ORDER BY ts DESC LIMIT 50"}],
"gridPos":{"h":8,"w":24,"x":0,"y":base_id+4}
}
])
with open(obs_path, "w", encoding="utf-8") as f:
json.dump(obs, f, indent=2)
# 4) Copy sample CSVs into repo for quick test if not present
plc_csv = os.path.join(samples_dir, "plc_spectrum_bins_sample.csv")
if not os.path.exists(plc_csv):
with open(plc_csv, "w", encoding="utf-8") as f:
f.write("ts,endpoint_id,link_id,center_mhz,bin_width_khz,power_dbm,notch,quality\n"
"2025-08-19 10:00:00,E1,L1,2.0,0.5,-60,0,0.95\n"
"2025-08-19 10:00:30,E1,L1,3.0,0.5,-80,1,0.91\n")
poe_ports_csv = os.path.join(samples_dir, "poe_ports_sample.csv")
if not os.path.exists(poe_ports_csv):
with open(poe_ports_csv, "w", encoding="utf-8") as f:
f.write("ts,switch_id,switch_name,port,poe_mode,poe_class,power_w,voltage_v,current_ma,admin_state,oper_state,endpoint_id\n"
"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\n")
poe_budget_csv = os.path.join(samples_dir, "poe_switch_budget_sample.csv")
if not os.path.exists(poe_budget_csv):
with open(poe_budget_csv, "w", encoding="utf-8") as f:
f.write("ts,switch_id,model,budget_w,used_w,free_w\n"
"2025-08-19 10:00:00,SW1,Catalyst-9300-48U,1440,980,460\n")
# 5) Build a distribution zip with only the changed/added files for convenience
bundle_zip = "/mnt/data/ucls-spectrum-unified-all-powercomm.zip"
with zipfile.ZipFile(bundle_zip, "w", zipfile.ZIP_DEFLATED) as z:
z.write(schema_path, "telemetry/clickhouse_schema.sql")
z.write(os.path.join(dash_dir, "ucls-powercomm.json"), "dashboards/ucls-powercomm.json")
z.write(obs_path, "dashboards/ucls-observability.json")
z.write(plc_csv, "samples/plc_spectrum_bins_sample.csv")
z.write(poe_ports_csv, "samples/poe_ports_sample.csv")
z.write(poe_budget_csv, "samples/poe_switch_budget_sample.csv")
bundle_zip
'/mnt/data/ucls-spectrum-unified-all-powercomm.zip'- Unified bundle with updates:
Download ucls-spectrum-unified-all-powercomm.zip
What changed
- ClickHouse schema (appended)
telemetry/clickhouse_schema.sqlnow includes:
plc_spectrum_bins_ch— PLC carriers/notchespoe_ports_ch— per-port PoE draw & statespoe_switch_budget_ch— per-switch budgets
- New combined dashboard
dashboards/ucls-powercomm.json
- PLC Spectrum Heatmap
- Switch Power Budget (Used vs Free)
- PoE Ports Near/Over Threshold
- Main Observability dashboard refreshed
dashboards/ucls-observability.jsonnow has a PowerComm row:
- Avg PLC Band Power (dBm)(stat)
- Avg PoE Utilization (%)(stat)
- Recent PoE Events(table)
- Samples for quick smoke-test
samples/plc_spectrum_bins_sample.csvsamples/poe_ports_sample.csvsamples/poe_switch_budget_sample.csv
Fast path
# 1) Apply schema
clickhouse-client -mn < telemetry/clickhouse_schema.sql
# 2) (Optional) Load the sample data
clickhouse-client --query="INSERT INTO plc_spectrum_bins_ch FORMAT CSVWithNames" < samples/plc_spectrum_bins_sample.csv
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
# 3) Import dashboards:
# - dashboards/ucls-observability.json (now includes PowerComm row)
# - dashboards/ucls-powercomm.json (detailed view)
# Ensure Grafana datasource UID: CLICKHOUSE_DS
If you want, I can wire live exporters next:
- SNMP/LLDP/REST pollers for PoE (Cisco/Juniper/Aruba/MikroTik profiles)
- G.hn / IEEE-1901 modem parsers (carrier bitmap → bins)
- Auto-notching compliance checks with alerts when a PLC profile drifts into protected bands.
Key terms in plain language
Open a term for a concise explanation of language used on this page.
Cybersecurity
The practices and controls used to protect identities, devices, networks, applications, and data from unauthorized access, disruption, or manipulation.
Zero Trust
A security model that does not automatically trust a user or device because of its location. Access is continuously verified and limited to what is necessary.
SASE
Secure Access Service Edge combines networking and security capabilities in a cloud-delivered architecture so users and locations can receive consistent policy wherever they connect.
Identity and Access Management (IAM)
The systems and policies that determine who a user is, what resources they may access, and how that access is authenticated and reviewed.
Multi-Factor Authentication (MFA)
A login control requiring more than one form of verification, such as a password plus an authenticator app, security key, or biometric factor.
MDR / XDR
Security services and tools that monitor activity, investigate suspicious behavior, and help contain threats. MDR is managed detection and response; XDR correlates signals across multiple security layers.