一、需求
应相关人员需求，需要导出zabbix的审计日志，但发现zabbix前端页面根本没有导出功能，因此就需要从数据库入手。
二、方案
解决此需求，涉及到users表、auditlog表、auditlog_details表，需要对这3个表进行关联查询，考虑到查询性能问题，使用外连接查询中的左连接（LEFT JOIN），之所以用左连接是因为要将users表作为基表，auditlog表和auditlog_details表作为参考表。
3个表的用途如下：
- users表：用户信息表，存储账号、密码的表
- auditlog表：审计日志多表
- auditlog_details表：审计日志的详情表
三、查看表结构
当不知道表有哪些字段时，一定要通过查看表结构来分析有哪些字段，且你需要用到哪些字段。
# auditlog_details表 mysql> desc zabbix.auditlog_details; +---------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+-------+ | auditdetailid | bigint unsigned | NO | PRI | NULL | | | auditid | bigint unsigned | NO | MUL | NULL | | | table_name | varchar(64) | NO | | | | | field_name | varchar(64) | NO | | | | | oldvalue | text | NO | | NULL | | | newvalue | text | NO | | NULL | | +---------------+-----------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) # auditlog表 mysql> desc zabbix.auditlog; +--------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+---------+-------+ | auditid | bigint unsigned | NO | PRI | NULL | | | userid | bigint unsigned | NO | MUL | NULL | | | clock | int | NO | MUL | 0 | | | action | int | NO | | 0 | | | resourcetype | int | NO | MUL | 0 | | | note | varchar(128) | NO | | | | | ip | varchar(39) | NO | | | | | resourceid | bigint unsigned | YES | | NULL | | | resourcename | varchar(255) | NO | | | | +--------------+-----------------+------+-----+---------+-------+ 9 rows in set (0.00 sec) # users表 mysql> desc zabbix.users; +----------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-----------------+------+-----+---------+-------+ | userid | bigint unsigned | NO | PRI | NULL | | | alias | varchar(100) | NO | UNI | | | | name | varchar(100) | NO | | | | | surname | varchar(100) | NO | | | | | passwd | varchar(60) | NO | | | | | url | varchar(255) | NO | | | | | autologin | int | NO | | 0 | | | autologout | varchar(32) | NO | | 15m | | | lang | varchar(5) | NO | | en_GB | | | refresh | varchar(32) | NO | | 30s | | | type | int | NO | | 1 | | | theme | varchar(128) | NO | | default | | | attempt_failed | int | NO | | 0 | | | attempt_ip | varchar(39) | NO | | | | | attempt_clock | int | NO | | 0 | | | rows_per_page | int | NO | | 50 | | +----------------+-----------------+------+-----+---------+-------+ 16 rows in set (0.08 sec) mysql>
四、SQL语句
- 以下sql在Mysql可正常查询，Oracle下待测试验证
SELECT FROM_UNIXTIME(log.clock) '时间', log.alias '账号',log.name '别名',log.action_var '动作',log.resourcetype_var '资源类型',log.ip 'IP',log.resourcename '资源名称',log.table_name '操作的表',log.field_name '操作的字段',log.oldvalue '旧值',log.newvalue '新值',log.note '操作描述' FROM ( SELECT a.clock, u.alias, u.name, CASE a.action WHEN 0 THEN '增加' WHEN 1 THEN '更新' WHEN 2 THEN '删除' WHEN 3 THEN '登录' WHEN 4 THEN '登出' WHEN 5 THEN '启用' WHEN 6 THEN '禁用' ELSE '未知操作' END AS action_var, CASE a.resourcetype WHEN 0 THEN '用户' WHEN 2 THEN '配置Zabbix' WHEN 3 THEN '媒介类型' WHEN 4 THEN '主机' WHEN 5 THEN '动作' WHEN 6 THEN '图表' WHEN 7 THEN '图表元素' WHEN 11 THEN '用户组' WHEN 12 THEN '应用' WHEN 13 THEN '触发器' WHEN 14 THEN '主机组' WHEN 15 THEN '监控项' WHEN 16 THEN '图片' WHEN 17 THEN '值映射' WHEN 18 THEN '服务' WHEN 19 THEN '拓扑图' WHEN 20 THEN '聚合图形' WHEN 22 THEN 'Web场景' WHEN 23 THEN '发现规则' WHEN 24 THEN '幻灯片放映' WHEN 25 THEN '脚本' WHEN 26 THEN '代理' WHEN 27 THEN '维护期' WHEN 28 THEN '正则表达式' WHEN 29 THEN '宏' WHEN 30 THEN '模板' WHEN 31 THEN '触发器原型' WHEN 32 THEN '图标映射' WHEN 33 THEN '仪表板' WHEN 34 THEN '关联项事件' WHEN 35 THEN '图表原型' WHEN 36 THEN '监控项原型' WHEN 37 THEN '主机原型' WHEN 38 THEN '自动注册' WHEN 39 THEN '组件' ELSE '未知资源' END AS resourcetype_var, a.ip, a.resourcename, d.table_name, d.field_name, d.oldvalue, d.newvalue, a.note FROM zabbix.users u LEFT JOIN zabbix.auditlog a ON u.userid=a.userid LEFT JOIN zabbix.auditlog_details d ON a.auditid=d.auditid ) AS log;
五、效果
