{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Hostname': 'VARCHAR(64)', 'Model': 'VARCHAR(64)', 'Mac': 'VARCHAR(17)', 'IpAddress': 'VARCHAR(45)', 'Firmware': 'VARCHAR(128)', 'FirmwareDate': 'VARCHAR(32)', 'Hardware': 'VARCHAR(32)', 'Serial': 'VARCHAR(64)', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Port': 'VARCHAR(16)', 'Label': 'VARCHAR(64)', 'Enabled': 'TINYINT(1) DEFAULT 1', 'Connected': 'TINYINT(1) DEFAULT 0', 'Speed': 'VARCHAR(16)', 'Duplex': 'VARCHAR(16)', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Port': 'VARCHAR(16)', 'PowerW': 'DECIMAL(6,2) DEFAULT 0', 'CurrentMa': 'DECIMAL(8,1) DEFAULT 0', 'VoltageV': 'DECIMAL(5,1) DEFAULT 0', 'PoeStatus': 'VARCHAR(16)', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
INSERT INTO data_switch_info
(Guid, Package, Hostname, Model, Mac, IpAddress,
Firmware, FirmwareDate, Hardware, Serial, Module)
VALUES
('{guid}', '{package}', '{hostname}', '{model}',
'{mac}', '{ip}', '{firmware}', '{firmware_date}',
'{hardware}', '{serial}', 'ObjServiceSwitch')
INSERT INTO data_switch_port
(Guid, Package, Port, Label, Enabled, Connected,
Speed, Duplex, Module)
VALUES
('{guid}', '{package}', '{port}', '{label}',
{enabled}, {connected},
'{speed}', '{duplex}', 'ObjServiceSwitch')
INSERT INTO data_switch_poe
(Guid, Package, Port, PowerW, CurrentMa,
VoltageV, PoeStatus, Module)
VALUES
('{guid}', '{package}', '{port}',
{power_w}, {current_ma}, {voltage_v},
'{poe_status}', 'ObjServiceSwitch')
SELECT p1.Port, p1.Connected, p1.Speed, p1.Duplex,
p1.CreateTime
FROM data_switch_port p1 INNER JOIN (
SELECT Port, MAX(CreateTime) AS MaxTime
FROM data_switch_port
WHERE Package = '{package}'
GROUP BY Port
) p2 ON p1.Port = p2.Port
AND p1.CreateTime = p2.MaxTime
ORDER BY p1.Port
SELECT e1.Port, e1.PowerW, e1.CurrentMa,
e1.VoltageV, e1.PoeStatus, e1.CreateTime
FROM data_switch_poe e1 INNER JOIN (
SELECT Port, MAX(CreateTime) AS MaxTime
FROM data_switch_poe
WHERE Package = '{package}'
GROUP BY Port
) e2 ON e1.Port = e2.Port
AND e1.CreateTime = e2.MaxTime
ORDER BY e1.Port
SELECT Port, AVG(PowerW) AS AvgW,
MAX(PowerW) AS MaxW,
COUNT(*) AS Samples,
DATE(CreateTime) AS Day
FROM data_switch_poe WHERE Package = '{package}'
AND PoeStatus = 'on'
AND CreateTime >= '{since}'
GROUP BY Port, DATE(CreateTime) ORDER BY Day DESC, Port