{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Hostname': 'VARCHAR(128)', 'RouterOs': 'VARCHAR(32)', 'CpuLoad': 'INT DEFAULT 0', 'CpuCount': 'INT DEFAULT 0', 'FreeMem': 'BIGINT DEFAULT 0', 'TotalMem': 'BIGINT DEFAULT 0', 'MemPct': 'DECIMAL(5,1) DEFAULT 0', 'FreeHdd': 'BIGINT DEFAULT 0', 'TotalHdd': 'BIGINT DEFAULT 0', 'UptimeSecs': 'BIGINT DEFAULT 0', 'BadBlocks': 'INT DEFAULT 0', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Interface': 'VARCHAR(64)', 'Running': 'TINYINT(1) DEFAULT 0', 'RxBytes': 'BIGINT DEFAULT 0', 'TxBytes': 'BIGINT DEFAULT 0', 'RxPackets': 'BIGINT DEFAULT 0', 'TxPackets': 'BIGINT DEFAULT 0', 'RxErrors': 'INT DEFAULT 0', 'TxErrors': 'INT DEFAULT 0', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'MacAddress': 'VARCHAR(17)', 'DeviceName': 'VARCHAR(128)', 'IpAddress': 'VARCHAR(45)', 'Hostname': 'VARCHAR(255)', 'Active': 'TINYINT(1) DEFAULT 0', 'LastSeen': 'DATETIME', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
{'Package': 'VARCHAR(255) NOT NULL', 'Person': 'VARCHAR(64) NOT NULL', 'Tier': "VARCHAR(16) NOT NULL DEFAULT 'resident'", 'Status': "VARCHAR(8) NOT NULL DEFAULT 'away'", 'MacAddress': 'VARCHAR(17)', 'IpAddress': 'VARCHAR(45)', 'ArrivedAt': 'DATETIME', 'DepartedAt': 'DATETIME', 'LastSeen': 'DATETIME', 'UpdateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)', 'PRIMARY_KEY': 'Package, Person'}
{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Target': 'VARCHAR(64)', 'Reachable': 'TINYINT(1) DEFAULT 0', 'LatencyMs': 'DECIMAL(8,2) DEFAULT 0', 'PacketLossPct': 'DECIMAL(5,1) DEFAULT 0', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
{'Package': 'VARCHAR(255) NOT NULL', 'MacAddress': 'VARCHAR(17) NOT NULL', 'DeviceName': "VARCHAR(128) NOT NULL DEFAULT ''", 'Location': "VARCHAR(64) NOT NULL DEFAULT 'unknown'", 'UpdateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'PRIMARY_KEY': 'Package, MacAddress'}
{'Package': 'VARCHAR(255) NOT NULL', 'Person': 'VARCHAR(64) NOT NULL', 'Tier': "VARCHAR(16) NOT NULL DEFAULT 'resident'", 'MacAddresses': 'TEXT', 'Hostnames': 'TEXT', 'UpdateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'PRIMARY_KEY': 'Package, Person'}
INSERT INTO data_network_router
(Guid, Package, Hostname, RouterOs,
CpuLoad, CpuCount,
FreeMem, TotalMem, MemPct,
FreeHdd, TotalHdd, UptimeSecs,
BadBlocks, Module)
VALUES
('{guid}', '{package}', '{hostname}',
'{router_os}',
{cpu_load}, {cpu_count},
{free_mem}, {total_mem}, {mem_pct},
{free_hdd}, {total_hdd}, {uptime_secs},
{bad_blocks}, 'ObjServiceNetwork')
INSERT INTO data_network_interface
(Guid, Package, Interface, Running,
RxBytes, TxBytes,
RxPackets, TxPackets,
RxErrors, TxErrors, Module)
VALUES
('{guid}', '{package}', '{interface}',
{running},
{rx_bytes}, {tx_bytes},
{rx_packets}, {tx_packets},
{rx_errors}, {tx_errors},
'ObjServiceNetwork')
INSERT INTO data_network_presence
(Guid, Package, MacAddress, DeviceName,
IpAddress, Hostname, Active, LastSeen, Module)
VALUES
('{guid}', '{package}', '{mac_address}',
'{device_name}', '{ip_address}',
'{hostname}', {active},
'{last_seen}', 'ObjServiceNetwork')
INSERT INTO data_network_health
(Guid, Package, Target, Reachable,
LatencyMs, PacketLossPct, Module)
VALUES
('{guid}', '{package}', '{target}',
{reachable}, {latency_ms},
{packet_loss_pct}, 'ObjServiceNetwork')
INSERT INTO data_household
(Package, Person, Tier, Status, MacAddress,
IpAddress, ArrivedAt, LastSeen, Module)
VALUES
('{package}', '{person}', '{tier}', 'home',
'{mac_address}', '{ip_address}',
'{now}', '{now}', 'ObjServiceNetwork')
ON DUPLICATE KEY UPDATE
Tier = '{tier}',
Status = 'home',
MacAddress = '{mac_address}',
IpAddress = '{ip_address}',
ArrivedAt = IF(Status != 'home', '{now}', ArrivedAt),
LastSeen = '{now}',
UpdateTime = CURRENT_TIMESTAMP
INSERT INTO data_household
(Package, Person, Tier, Status,
DepartedAt, LastSeen, Module)
VALUES
('{package}', '{person}', '{tier}', 'away',
'{now}', '{now}', 'ObjServiceNetwork')
ON DUPLICATE KEY UPDATE
Tier = '{tier}',
Status = 'away',
DepartedAt = IF(Status != 'away', '{now}', DepartedAt),
LastSeen = '{now}',
UpdateTime = CURRENT_TIMESTAMP
SELECT Person, Status, MacAddress, IpAddress,
ArrivedAt, DepartedAt, LastSeen
FROM data_household WHERE Package = '{package}' ORDER BY Person
SELECT Target, Reachable, LatencyMs,
PacketLossPct, CreateTime
FROM data_network_health WHERE Package = '{package}' ORDER BY CreateTime DESC LIMIT {limit}
SELECT MacAddress, DeviceName, IpAddress,
Hostname, LastSeen
FROM data_network_presence WHERE Package = '{package}'
AND Active = 1
AND LastSeen >= NOW() - INTERVAL 10 MINUTE
ORDER BY DeviceName
SELECT Target, AVG(LatencyMs) AS AvgMs,
MAX(PacketLossPct) AS MaxLoss,
COUNT(*) AS Samples,
DATE(CreateTime) AS Day
FROM data_network_health WHERE Package = '{package}'
AND Target IN ('8.8.8.8', '1.1.1.1')
AND CreateTime >= '{since}'
GROUP BY Target, DATE(CreateTime) ORDER BY Day DESC
SELECT MacAddress, DeviceName, Location FROM data_network_device WHERE Package = '{package}' ORDER BY DeviceName
INSERT INTO data_network_device
(Package, MacAddress, DeviceName, Location)
VALUES
('{package}', '{mac_address}', '{device_name}', '{location}')
ON DUPLICATE KEY UPDATE
DeviceName = '{device_name}',
Location = '{location}',
UpdateTime = CURRENT_TIMESTAMP
DELETE FROM data_network_device WHERE Package = '{package}'
AND MacAddress = '{mac_address}'
SELECT Person, Tier, MacAddresses, Hostnames FROM data_network_member WHERE Package = '{package}' ORDER BY Person
INSERT INTO data_network_member
(Package, Person, Tier, MacAddresses, Hostnames)
VALUES
('{package}', '{person}', '{tier}',
'{mac_addresses}', '{hostnames}')
ON DUPLICATE KEY UPDATE
Tier = '{tier}',
MacAddresses = '{mac_addresses}',
Hostnames = '{hostnames}',
UpdateTime = CURRENT_TIMESTAMP
DELETE FROM data_network_member WHERE Package = '{package}'
AND Person = '{person}'