159 lines
8.9 KiB
SQL
159 lines
8.9 KiB
SQL
-- =====================================================================
|
||
-- 设备API采集模块 建表 + 测试数据脚本
|
||
-- 适用数据库: 达梦 DM8
|
||
-- 说明:
|
||
-- 1. 先执行建表语句（表已存在时请先删除或忽略 DDL 部分）
|
||
-- 2. 再执行 INSERT 测试数据
|
||
-- 3. id 均为自增字段(IDENTITY)，INSERT 无需也不允许指定 id
|
||
-- =====================================================================
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 1. 建表: 设备API采集配置表
|
||
-- ---------------------------------------------------------------------
|
||
DROP TABLE IF EXISTS device_api_collect_log;
|
||
DROP TABLE IF EXISTS device_api_config;
|
||
|
||
CREATE TABLE device_api_config (
|
||
id BIGINT IDENTITY(1,1) PRIMARY KEY,
|
||
device_id INT, -- 设备ID，关联 device_device.id
|
||
device_type VARCHAR(50) NOT NULL, -- 设备类型: tianyan/weibu/xdr
|
||
device_name VARCHAR(100) NOT NULL, -- 设备名称
|
||
device_ip VARCHAR(200) NOT NULL, -- 设备IP地址
|
||
host_api VARCHAR(200) NOT NULL, -- 设备API URL地址
|
||
auth_config TEXT, -- 认证配置JSON
|
||
data_types VARCHAR(500) NOT NULL, -- 数据类型: weblog,alarm / attack,info
|
||
collect_interval_sec INT DEFAULT 300, -- 采集间隔（秒）
|
||
enabled INT DEFAULT 1, -- 是否启用: 0-禁用, 1-启用
|
||
remark VARCHAR(500), -- 备注
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE device_api_config IS '设备API采集配置表';
|
||
COMMENT ON COLUMN device_api_config.id IS '主键，自增';
|
||
COMMENT ON COLUMN device_api_config.device_id IS '设备ID，关联 device_device.id';
|
||
COMMENT ON COLUMN device_api_config.device_type IS '设备类型: tianyan/weibu/xdr';
|
||
COMMENT ON COLUMN device_api_config.device_name IS '设备名称';
|
||
COMMENT ON COLUMN device_api_config.device_ip IS '设备IP地址';
|
||
COMMENT ON COLUMN device_api_config.host_api IS '设备API URL地址';
|
||
COMMENT ON COLUMN device_api_config.auth_config IS '认证配置JSON';
|
||
COMMENT ON COLUMN device_api_config.data_types IS '数据类型: weblog,alarm / attack,info';
|
||
COMMENT ON COLUMN device_api_config.collect_interval_sec IS '采集间隔（秒）';
|
||
COMMENT ON COLUMN device_api_config.enabled IS '是否启用: 0-禁用, 1-启用';
|
||
COMMENT ON COLUMN device_api_config.remark IS '备注';
|
||
COMMENT ON COLUMN device_api_config.create_time IS '创建时间';
|
||
COMMENT ON COLUMN device_api_config.update_time IS '更新时间';
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 2. 建表: 设备API采集执行日志表
|
||
-- ---------------------------------------------------------------------
|
||
CREATE TABLE device_api_collect_log (
|
||
id BIGINT IDENTITY(1,1) PRIMARY KEY,
|
||
device_id INT NOT NULL, -- 设备ID，关联 device_device.id
|
||
device_type VARCHAR(50) NOT NULL, -- 设备类型
|
||
data_type VARCHAR(50) NOT NULL, -- 数据类型
|
||
start_time TIMESTAMP NOT NULL, -- 采集窗口开始时间
|
||
end_time TIMESTAMP NOT NULL, -- 采集窗口结束时间
|
||
total_count INT DEFAULT 0, -- 采集记录总数
|
||
success_count INT DEFAULT 0, -- 成功写入数
|
||
fail_count INT DEFAULT 0, -- 失败数
|
||
cost_ms BIGINT DEFAULT 0, -- 耗时（毫秒）
|
||
status VARCHAR(20) NOT NULL, -- 状态: SUCCESS/FAIL
|
||
error_msg TEXT, -- 错误信息
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE device_api_collect_log IS '设备API采集执行日志';
|
||
COMMENT ON COLUMN device_api_collect_log.id IS '主键，自增';
|
||
COMMENT ON COLUMN device_api_collect_log.device_id IS '设备ID，关联 device_device.id';
|
||
COMMENT ON COLUMN device_api_collect_log.device_type IS '设备类型';
|
||
COMMENT ON COLUMN device_api_collect_log.data_type IS '数据类型';
|
||
COMMENT ON COLUMN device_api_collect_log.start_time IS '采集窗口开始时间';
|
||
COMMENT ON COLUMN device_api_collect_log.end_time IS '采集窗口结束时间';
|
||
COMMENT ON COLUMN device_api_collect_log.total_count IS '采集记录总数';
|
||
COMMENT ON COLUMN device_api_collect_log.success_count IS '成功写入数';
|
||
COMMENT ON COLUMN device_api_collect_log.fail_count IS '失败数';
|
||
COMMENT ON COLUMN device_api_collect_log.cost_ms IS '耗时（毫秒）';
|
||
COMMENT ON COLUMN device_api_collect_log.status IS '状态: SUCCESS/FAIL';
|
||
COMMENT ON COLUMN device_api_collect_log.error_msg IS '错误信息';
|
||
COMMENT ON COLUMN device_api_collect_log.create_time IS '创建时间';
|
||
|
||
-- =====================================================================
|
||
-- 3. 测试数据: 设备API采集配置表（id 自增，不插入）
|
||
-- 覆盖: 天眼(启用/停用)、微步(启用)、XDR(暂缓停用)
|
||
-- =====================================================================
|
||
INSERT INTO device_api_config
|
||
(device_id, device_type, device_name, device_ip, host_api, auth_config, data_types,
|
||
collect_interval_sec, enabled, remark, create_time, update_time)
|
||
VALUES
|
||
-- 天眼-主设备（启用，采集 weblog+alarm）
|
||
(35, 'tianyan', '天眼态势感知平台', '10.56.170.107',
|
||
'https://10.56.170.107',
|
||
'{"login_key":"QuJm4NwF3GMaXb2Z","username":"xxadmin","branch_id":"QbJK%2FsLHi"}',
|
||
'weblog,alarm', 300, 1, '生产环境天眼主采集器',
|
||
'2026-08-27 09:00:00', '2026-08-27 09:00:00'),
|
||
-- 天眼-备设备（停用，仅测试）
|
||
(102, 'tianyan', '天眼态势感知平台-备', '10.56.170.107',
|
||
'https://10.56.170.107',
|
||
'{"login_key":"QuJm4NwF3GMaXb2Z","username":"xxadmin","branch_id":"QbJK%2FsLHi"}',
|
||
'weblog', 600, 0, '备用采集器，暂未启用',
|
||
'2026-08-27 09:05:00', '2026-08-27 09:05:00'),
|
||
-- 微步-TDP（启用，采集 attack+info）
|
||
(36, 'weibu', '微步威胁感知平台', '10.56.171.164',
|
||
'https://10.56.171.164',
|
||
'{"api_key":"57d8fb2b556a471ea36b8b4441fa1c0c","secret":"9d967876f51d4e16bbf8bde069d12a9b"}',
|
||
'attack,info', 300, 1, '生产环境微步采集器',
|
||
'2026-08-27 09:10:00', '2026-08-27 09:10:00'),
|
||
-- XDR（暂缓，联动码待联调）
|
||
(104, 'xdr', '深信服XDR平台', '192.168.30.31',
|
||
'https://192.168.30.31',
|
||
'{"auth_code":"<待联调时填入14段联动码>"}',
|
||
'alert', 300, 0, 'XDR采集暂缓，待联动码解码联调后启用',
|
||
'2026-08-27 09:15:00', '2026-08-27 09:15:00');
|
||
|
||
-- =====================================================================
|
||
-- 4. 测试数据: 设备API采集执行日志表（id 自增，不插入）
|
||
-- 覆盖: 成功、失败、不同设备/类型
|
||
-- =====================================================================
|
||
INSERT INTO device_api_collect_log
|
||
(device_id, device_type, data_type, start_time, end_time,
|
||
total_count, success_count, fail_count, cost_ms, status, error_msg, create_time)
|
||
VALUES
|
||
-- 天眼 weblog 采集成功
|
||
(101, 'tianyan', 'weblog',
|
||
'2026-08-27 08:55:00', '2026-08-27 09:00:00',
|
||
1280, 1280, 0, 3265, 'SUCCESS', NULL,
|
||
'2026-08-27 09:00:00'),
|
||
-- 天眼 alarm 采集成功
|
||
(101, 'tianyan', 'alarm',
|
||
'2026-08-27 08:55:00', '2026-08-27 09:00:00',
|
||
36, 36, 0, 1240, 'SUCCESS', NULL,
|
||
'2026-08-27 09:00:00'),
|
||
-- 微步 attack 采集成功（含少量失败）
|
||
(103, 'weibu', 'attack',
|
||
'2026-08-27 08:55:00', '2026-08-27 09:00:00',
|
||
520, 518, 2, 1890, 'SUCCESS', '2条消息Kafka推送失败',
|
||
'2026-08-27 09:00:00'),
|
||
-- 微步 info 采集失败（模拟接口超时）
|
||
(103, 'weibu', 'info',
|
||
'2026-08-27 08:55:00', '2026-08-27 09:00:00',
|
||
0, 0, 0, 30050, 'FAIL', 'searchBySql接口请求超时: read timed out',
|
||
'2026-08-27 09:00:00'),
|
||
-- XDR（停用状态，无采集记录; 以下为历史参考记录）
|
||
(104, 'xdr', 'alert',
|
||
'2026-08-26 09:00:00', '2026-08-26 09:05:00',
|
||
0, 0, 0, 150, 'FAIL', 'XDR采集器暂缓，待联动码解码与签名接口联调后实现',
|
||
'2026-08-26 09:05:00');
|
||
|
||
-- =====================================================================
|
||
-- 5. 校验查询（可选执行）
|
||
-- =====================================================================
|
||
-- 查询启用中的采集配置
|
||
-- SELECT id, device_type, device_name, data_types, collect_interval_sec
|
||
-- FROM device_api_config
|
||
-- WHERE enabled = 1;
|
||
|
||
-- 查询最近采集日志
|
||
-- SELECT device_type, data_type, total_count, success_count, fail_count,
|
||
-- status, error_msg, create_time
|
||
-- FROM device_api_collect_log
|
||
-- ORDER BY id DESC;
|