sudo /usr/local/kaiwudb/bin/kwbase sql \
  --certs-dir=/etc/kaiwudb/certs \
  --host=127.0.0.1:26257

CREATE DATABASE IF NOT EXISTS smart_ops;
USE smart_ops;

CREATE TABLE servers (
    hostname VARCHAR(50) PRIMARY KEY, -- 主機(jī)名 (唯一)
    ip_address VARCHAR(20),           -- IP地址
    rack_id VARCHAR(20),              -- 機(jī)架編號
    os_version VARCHAR(50),           -- 操作系統(tǒng)
    cpu_cores INT,                    -- CPU核數(shù)
    mem_gb INT                        -- 內(nèi)存大小
);

-- 模擬資產(chǎn)數(shù)據(jù)
INSERT INTO servers VALUES 
('web-01', '192.168.1.101', 'Rack-A01', 'Ubuntu 22.04', 16, 32),
('web-02', '192.168.1.102', 'Rack-A01', 'Ubuntu 22.04', 16, 32),
('db-01',  '192.168.1.201', 'Rack-B02', 'CentOS 7.9',   32, 128),
('db-02',  '192.168.1.202', 'Rack-B02', 'CentOS 7.9',   32, 128);

CREATE TABLE server_metrics (
    ts TIMESTAMP NOT NULL,          -- 時間戳
    hostname VARCHAR(50) NOT NULL,  -- 主機(jī)名 (Tag)
    cpu_usage DOUBLE,               -- CPU使用率 (%)
    mem_usage DOUBLE,               -- 內(nèi)存使用率 (%)
    disk_io_read DOUBLE,            -- 磁盤讀 (MB/s)
    disk_io_write DOUBLE,           -- 磁盤寫 (MB/s)
    net_in DOUBLE,                  -- 網(wǎng)絡(luò)入 (Mbps)
    net_out DOUBLE,                 -- 網(wǎng)絡(luò)出 (Mbps)
    PRIMARY KEY (ts, hostname)
);

import random
from datetime import datetime, timedelta

# 配置
FILENAME = "ops_data.sql"
HOSTS = ['web-01', 'web-02', 'db-01', 'db-02']
START_TIME = datetime.now() - timedelta(hours=6)
INTERVAL_SECONDS = 5 # 5秒一個點(diǎn)
TOTAL_POINTS = int(6 * 3600 / INTERVAL_SECONDS)

print(f"正在生成 {len(HOSTS)} 臺服務(wù)器，過去 6 小時的監(jiān)控數(shù)據(jù)...")

with open(FILENAME, "w") as f:
    f.write("USE smart_ops;\n")
    f.write("INSERT INTO server_metrics (ts, hostname, cpu_usage, mem_usage, disk_io_read, disk_io_write, net_in, net_out) VALUES\n")
    
    records = []
    
    for host in HOSTS:
        # 模擬不同角色的負(fù)載特征
        base_cpu = 20 if 'web' in host else 40
        base_mem = 40 if 'web' in host else 70
        
        for i in range(TOTAL_POINTS):
            ts = (START_TIME + timedelta(seconds=i*INTERVAL_SECONDS)).strftime('%Y-%m-%d %H:%M:%S')
            
            # 波動
            cpu = base_cpu + random.uniform(-10, 30)
            if cpu > 100: cpu = 100
            
            mem = base_mem + random.uniform(-5, 10)
            if mem > 100: mem = 100
            
            disk_r = random.uniform(0, 100)
            disk_w = random.uniform(0, 50)
            
            # 數(shù)據(jù)庫服務(wù)器 IO 更高
            if 'db' in host:
                disk_r *= 2
                disk_w *= 3
                
            net_in = random.uniform(10, 1000)
            net_out = random.uniform(10, 1000)
            
            records.append(f"('{ts}', '{host}', {round(cpu,1)}, {round(mem,1)}, {round(disk_r,1)}, {round(disk_w,1)}, {round(net_in,1)}, {round(net_out,1)})")

    # 批量寫入
    batch_size = 1000
    total = len(records)
    for i, record in enumerate(records):
        if (i + 1) % batch_size == 0 or i == total - 1:
            f.write(f"{record};\n")
            if i < total - 1:
                f.write("INSERT INTO server_metrics (ts, hostname, cpu_usage, mem_usage, disk_io_read, disk_io_write, net_in, net_out) VALUES\n")
        else:
            f.write(f"{record},\n")

print(f"生成完畢！總記錄數(shù): {total}")
print(f"請運(yùn)行: time sudo /usr/local/kaiwudb/bin/kwbase sql --certs-dir=/etc/kaiwudb/certs --host=127.0.0.1:26257 < {FILENAME}")

python3 gen_ops_data.py
time sudo /usr/local/kaiwudb/bin/kwbase sql --certs-dir=/etc/kaiwudb/certs --host=127.0.0.1:26257 < ops_data.sql

USE smart_ops;

-- 簡單的平均值可能掩蓋毛刺，P95 更能反映真實(shí)壓力
-- 注意：如果當(dāng)前版本暫不支持 percentile_cont，可以使用 avg/max 替代
SELECT 
    hostname,
    avg(cpu_usage) as avg_cpu,
    max(cpu_usage) as max_cpu
FROM server_metrics
WHERE ts > now() - interval '1 hour'
GROUP BY hostname;

USE smart_ops;

SELECT 
    s.rack_id,
    sum(m.net_in) as total_net_in,
    sum(m.net_out) as total_net_out
FROM server_metrics m
JOIN servers s ON m.hostname = s.hostname
WHERE m.ts > now() - interval '5 minute' -- 實(shí)時流量
GROUP BY s.rack_id;

USE smart_ops;

SELECT 
    s.hostname,
    s.ip_address,
    max(m.ts) as last_heartbeat
FROM servers s
LEFT JOIN server_metrics m ON s.hostname = m.hostname
GROUP BY s.hostname, s.ip_address
HAVING max(m.ts) < now() - interval '5 minute' OR max(m.ts) IS NULL;