dataezse接入zabbix监控
常用查询
目录
zabbix 常用库表说明
https://www.cnblogs.com/yaoyaojcy/p/10367945.html
https://www.cnblogs.com/xuefy/p/11422104.html
zabbix 6.0以及以上版本
主机资源监控
-- 主机状态查询：
SELECT
host_status.分组,
host_status.ip ip地址,
host_status.HOST 主机名,
host_status.值 主机状态,
CONCAT( space_used.NAME, ': ', space_used.space_used, '%' ) 磁盘空间使用率,
swap_free.swap_free_GB SWAP剩余_GB,
CONCAT( mem_trends.min_mem, '%' ) 内存最小值,
CONCAT( mem_trends.avg_mem, '%' ) 内存平均值,
CONCAT( mem_trends.max_mem, '%' ) 内存最大值,
CONCAT( mem_now.mem_now, '%' ) 内存当前值,
CONCAT( CPU_trends.min_cpu, '%' ) CPU最小值,
CONCAT( CPU_trends.avg_cpu, '%' ) CPU平均值,
CONCAT( CPU_trends.max_cpu, '%' ) CPU最大值,
CONCAT( cpu_now.cpu_now, '%' ) CPU当前值,
CONCAT( io_trends.min_io, '%' ) IO最小值,
CONCAT( io_trends.avg_io, '%' ) IO平均值,
CONCAT( io_trends.max_io, '%' ) IO最大值,
CONCAT( io_now.io_now, '%' ) IO当前值
FROM
-- 主机状态
(
SELECT
a.ip,
b.NAME,
b.HOST,
b.hostid,
( CASE A.available WHEN '1' THEN '正常' ELSE '异常' END ) 值 ,
d.name 分组
FROM
interface a
LEFT JOIN HOSTS b ON a.hostid = b.hostid
left join hosts_groups c on a.hostid = c.hostid
left join hstgrp d on d.groupid=c.groupid
WHERE
b.flags <>2
-- AND d.NAME='ORACLE'
) host_status left join
-- 内存趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_mem,
ROUND(avg(t.value_avg),2) as avg_mem,
ROUND(max(t.value_max),2) as max_mem
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME in ( 'Memory utilization','Linux: Memory utilization' )
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) mem_trends on host_status.hostid=mem_trends.hostid
left join
-- CPU趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_cpu,
ROUND(avg(t.value_avg),2) as avg_cpu,
ROUND(max(t.value_max),2) as max_cpu
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME in ('CPU utilization','Linux: CPU utilization')
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) CPU_trends on host_status.hostid=CPU_trends.hostid
-- IO趋势
left join
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_io,
ROUND(avg(t.value_avg),2) as avg_io,
ROUND(max(t.value_max),2) as max_io
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization%'
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) io_trends on host_status.hostid=io_trends.hostid
-- CPU当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) cpu_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('CPU utilization','Linux: CPU utilization')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) cpu_now on host_status.hostid=cpu_now.hostid
-- 内存当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) mem_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('Memory utilization','Linux: Memory utilization')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) mem_now on host_status.hostid=mem_now.hostid
-- IO当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) io_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) io_now on host_status.hostid=io_now.hostid
-- swap 剩余
left join
(
SELECT
h.hostid,
round(hi.VALUE/1024/1024/1024,2) swap_free_GB
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('Free swap space','Linux: Free swap space')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) swap_free on host_status.hostid=swap_free.hostid
-- 磁盘使用率
left join
(
SELECT
h.hostid,
it.name,
max(round(hi.VALUE,2)) space_used
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Space utilization%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) space_used on host_status.hostid=space_used.hostid
where host_status.HOST not in ('prod-nc-db-rac01_2','prod-nc-db-rac01_2','prod-nc-db-rac02_3','prod-amb-db-01_2','prod-nc-db-rac02_2')
group by host_status.ip
order by host_status.分组,space_used.space_used desc;
主机资源监控（纯值）
-- 主机状态查询：
SELECT
host_status.分组,
host_status.ip ip地址,
host_status.HOST 主机名,
host_status.值 主机状态,
space_used.NAME 盘符,
space_used.space_used '磁盘空间使用率%',
swap_free.swap_free_GB SWAP剩余_GB,
swap_total.swap_total_GB SWAP总量_GB,
( CASE swap_total.swap_total_GB WHEN 0 THEN '未分配' ELSE ROUND((1-swap_free.swap_free_GB/swap_total.swap_total_GB)*100,2) END ) 'SWAP使用率%',
mem_trends.min_mem '内存最小值%',
mem_trends.avg_mem '内存平均值%',
mem_trends.max_mem '内存最大值%',
mem_now.mem_now '内存当前值%',
CPU_trends.min_cpu 'CPU最小值%',
CPU_trends.avg_cpu 'CPU平均值%',
CPU_trends.max_cpu 'CPU最大值%',
cpu_now.cpu_now 'CPU当前值%',
io_trends.min_io 'IO最小值%',
io_trends.avg_io 'IO平均值%',
io_trends.max_io 'IO最大值%',
io_now.io_now 'IO当前值%'
FROM
-- 主机状态
(
SELECT
a.ip,
b.NAME,
b.HOST,
b.hostid,
( CASE A.available WHEN '1' THEN '正常' ELSE '异常' END ) 值 ,
d.name 分组
FROM
interface a
LEFT JOIN HOSTS b ON a.hostid = b.hostid
left join hosts_groups c on a.hostid = c.hostid
left join hstgrp d on d.groupid=c.groupid
WHERE
b.flags <>2
-- AND d.NAME='ORACLE'
) host_status left join
-- 内存趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_mem,
ROUND(avg(t.value_avg),2) as avg_mem,
ROUND(max(t.value_max),2) as max_mem
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME in ( 'Memory utilization','Linux: Memory utilization' )
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) mem_trends on host_status.hostid=mem_trends.hostid
left join
-- CPU趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_cpu,
ROUND(avg(t.value_avg),2) as avg_cpu,
ROUND(max(t.value_max),2) as max_cpu
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME in ('CPU utilization','Linux: CPU utilization')
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) CPU_trends on host_status.hostid=CPU_trends.hostid
-- IO趋势
left join
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_io,
ROUND(avg(t.value_avg),2) as avg_io,
ROUND(max(t.value_max),2) as max_io
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization%'
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) io_trends on host_status.hostid=io_trends.hostid
-- CPU当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) cpu_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('CPU utilization','Linux: CPU utilization')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) cpu_now on host_status.hostid=cpu_now.hostid
-- 内存当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) mem_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('Memory utilization','Linux: Memory utilization')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) mem_now on host_status.hostid=mem_now.hostid
-- IO当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) io_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) io_now on host_status.hostid=io_now.hostid
-- swap 剩余
left join
(
SELECT
h.hostid,
round(hi.VALUE/1024/1024/1024,2) swap_free_GB
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('Free swap space','Linux: Free swap space')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) swap_free on host_status.hostid=swap_free.hostid
--
left join
(
SELECT
h.hostid,
round(hi.VALUE/1024/1024/1024,2) swap_total_GB
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME in('Total swap space' ,'Linux: Total swap space')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) swap_total on host_status.hostid=swap_total.hostid
-- 磁盘使用率
left join
(
SELECT
h.hostid,
it.name,
max(round(hi.VALUE,2)) space_used
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Space utilization%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) space_used on host_status.hostid=space_used.hostid
where host_status.HOST not in ('prod-nc-db-rac01_2','prod-nc-db-rac01_2','prod-nc-db-rac02_3','prod-amb-db-01_2','prod-nc-db-rac02_2')
group by host_status.ip
order by host_status.分组,space_used.space_used desc;
oracle状态监控
SELECT
host_status.分组 分组,
host_status.ip ip地址,
host_status.HOST 主机名,
host_status.值 主机状态,
db_status.db_stat 数据库状态,
adg_stat.adg_stat 备库状态,
CONCAT( space_used.NAME, ': ', space_used.space_used, '%' ) 磁盘空间使用率,
swap_free.swap_free_GB SWAP剩余_GB,
CONCAT( mem_trends.min_mem, '%' ) 内存最小值,
CONCAT( mem_trends.avg_mem, '%' ) 内存平均值,
CONCAT( mem_trends.max_mem, '%' ) 内存最大值,
CONCAT( mem_now.mem_now, '%' ) 内存当前值,
CONCAT( CPU_trends.min_cpu, '%' ) CPU最小值,
CONCAT( CPU_trends.avg_cpu, '%' ) CPU平均值,
CONCAT( CPU_trends.max_cpu, '%' ) CPU最大值,
CONCAT( cpu_now.cpu_now, '%' ) CPU当前值,
CONCAT( io_trends.min_io, '%' ) IO最小值,
CONCAT( io_trends.avg_io, '%' ) IO平均值,
CONCAT( io_trends.max_io, '%' ) IO最大值,
CONCAT( io_now.io_now, '%' ) IO当前值,
tablespace_stat.tablespace_stat 表空间状态,
asm_stat.asm_stat ASM状态,
max_processes.max_processes 最大进程数,
processes.processes 当前进程数,
max_sessions.max_sessions 最大会话数,
sessions.sessions 当前会话数,
sessions_active.sessions_active 并发数,
archivelog.archivelog 归档状态,
CONCAT( table_ratio.table_ratio, '%' ) 表命中率,
CONCAT( sqlarea_ratio.sqlarea_ratio, '%' ) SQLAREA命中率,
CONCAT( body_ratio.body_ratio, '%' ) BODY命中率,
CONCAT( trigger_ratio.trigger_ratio, '%' ) 触发器命中率
FROM
-- 主机状态
(
SELECT
a.ip,
b.NAME,
b.HOST,
b.hostid,
d.name 分组,
( CASE A.available WHEN '1' THEN '正常' ELSE '异常' END ) 值
FROM
interface a
LEFT JOIN HOSTS b ON a.hostid = b.hostid
left join hosts_groups c on a.hostid = c.hostid
left join hstgrp d on d.groupid=c.groupid
WHERE
b.flags <>2
AND d.NAME='ORACLE'
) host_status left join
-- 内存趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_mem,
ROUND(avg(t.value_avg),2) as avg_mem,
ROUND(max(t.value_max),2) as max_mem
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME = 'Memory utilization'
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) mem_trends on host_status.hostid=mem_trends.hostid
left join
-- CPU趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_cpu,
ROUND(avg(t.value_avg),2) as avg_cpu,
ROUND(max(t.value_max),2) as max_cpu
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME = 'CPU utilization'
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) CPU_trends on host_status.hostid=CPU_trends.hostid
-- IO趋势
left join
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_io,
ROUND(avg(t.value_avg),2) as avg_io,
ROUND(max(t.value_max),2) as max_io
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization%'
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) io_trends on host_status.hostid=io_trends.hostid
-- 数据库状态
left join
(
SELECT
h.hostid,
( CASE hi.VALUE WHEN '1' THEN '正常' ELSE '异常' END ) db_stat
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Alive'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) db_status on host_status.hostid=db_status.hostid
-- CPU当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) cpu_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'CPU utilization'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) cpu_now on host_status.hostid=cpu_now.hostid
-- 内存当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) mem_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Memory utilization'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) mem_now on host_status.hostid=mem_now.hostid
-- IO当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) io_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) io_now on host_status.hostid=io_now.hostid
-- 表空间状态
left join
(
SELECT
h.hostid,
( CASE hi.VALUE WHEN 'none' THEN '正常' ELSE hi.VALUE END ) tablespace_stat
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_text hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Tablespaces'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) tablespace_stat on host_status.hostid=tablespace_stat.hostid
-- ASM磁盘空间
left join
(
SELECT
h.hostid,
( CASE hi.VALUE WHEN 'The disk status is ok!' THEN '正常' ELSE hi.VALUE END ) asm_stat
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_text hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'ASM disk uasage status'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) asm_stat on host_status.hostid=asm_stat.hostid
-- 最大进程数
left join
(
SELECT
h.hostid,
hi.VALUE max_processes
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Max Processes'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) max_processes on host_status.hostid=max_processes.hostid
-- 当前进程数
left join
(
SELECT
h.hostid,
hi.VALUE processes
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Processes'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) processes on host_status.hostid=processes.hostid
-- 最大会话数
left join
(
SELECT
h.hostid,
hi.VALUE max_sessions
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Max Sessions'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) max_sessions on host_status.hostid=max_sessions.hostid
-- 当前会话数
left join
(
SELECT
h.hostid,
hi.VALUE sessions
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Sessions'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) sessions on host_status.hostid=sessions.hostid
-- 并发会话数
left join
(
SELECT
h.hostid,
hi.VALUE sessions_active
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Session Active'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) sessions_active on host_status.hostid=sessions_active.hostid
-- 归档状态
left join
(
SELECT
h.hostid,
hi.VALUE archivelog
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Archivelog'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) archivelog on host_status.hostid=archivelog.hostid
-- 触发器命中率
left join
(
SELECT
h.hostid,
round(hi.VALUE,2) trigger_ratio
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Hit ratio - TRIGGER'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) trigger_ratio on host_status.hostid=trigger_ratio.hostid
-- body命中率
left join
(
SELECT
h.hostid,
round(hi.VALUE,2) body_ratio
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Hit ratio - BODY'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) body_ratio on host_status.hostid=body_ratio.hostid
-- SQLAREA命中率
left join
(
SELECT
h.hostid,
round(hi.VALUE,2) sqlarea_ratio
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Hit ratio - SQLAREA'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) sqlarea_ratio on host_status.hostid=sqlarea_ratio.hostid
-- TABLE命中率
left join
(
SELECT
h.hostid,
round(hi.VALUE,2) table_ratio
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Hit ratio - TABLE/PROCEDURE'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) table_ratio on host_status.hostid=table_ratio.hostid
-- swap 剩余
left join
(
SELECT
h.hostid,
round(hi.VALUE/1024/1024/1024,2) swap_free_GB
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'Free swap space'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) swap_free on host_status.hostid=swap_free.hostid
-- 磁盘使用率
left join
(
SELECT
h.hostid,
it.name,
max(round(hi.VALUE,2)) space_used
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Space utilization%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) space_used on host_status.hostid=space_used.hostid
-- 备库状态
left join
(
SELECT
h.name,
h.hostid,
( CASE hi.VALUE
WHEN 'sync' THEN '同步'
WHEN hi.VALUE IS NULL THEN '同步'
ELSE hi.VALUE
END ) adg_stat
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_text hi ON hi.itemid = it.itemid
WHERE
it.NAME = 'ADG status'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) adg_stat on host_status.hostid=adg_stat.hostid
mysql资源监控
-- mysql 状态检查：
SELECT
host_status.分组 '分组',
host_status.ip 'ip地址',
host_status.HOST '主机名',
host_status.值 '主机状态',
mysql_version.mysql_version '数据库版本',
mysql_status.mysql_status 'MYSQL 状态',
CONCAT( space_used.NAME, ': ', space_used.space_used, '%' ) 磁盘空间使用率,
IO_thread_status.IO_thread_status 'IO thread status',
SQL_thread_status.SQL_thread_status 'SQL thread status',
Replication_delay.Replication_delay '复制延迟',
swap_free.swap_free_GB SWAP剩余_GB,
CONCAT( mem_trends.min_mem, '%' ) 内存最小值,
CONCAT( mem_trends.avg_mem, '%' ) 内存平均值,
CONCAT( mem_trends.max_mem, '%' ) 内存最大值,
CONCAT( mem_now.mem_now, '%' ) 内存当前值,
CONCAT( CPU_trends.min_cpu, '%' ) CPU最小值,
CONCAT( CPU_trends.avg_cpu, '%' ) CPU平均值,
CONCAT( CPU_trends.max_cpu, '%' ) CPU最大值,
CONCAT( cpu_now.cpu_now, '%' ) CPU当前值,
CONCAT( io_trends.min_io, '%' ) IO最小值,
CONCAT( io_trends.avg_io, '%' ) IO平均值,
CONCAT( io_trends.max_io, '%' ) IO最大值,
CONCAT( io_now.io_now, '%' ) IO当前值,
CONCAT( buffer_pool_now.buffer_pool_now, '%' ) buffer_pool当前值,
delete_num.delete_num 每秒（deleted）,
insert_num.insert_num 每秒（insert）,
select_num.select_num 每秒（select）,
update_num.update_num 每秒（update）,
qps.qps,
tps.tps,
max_connection.max_connection '历史最大连接数',
connected_ervery_secondes.connected_ervery_secondes '每秒连接数',
connected.connected '当前连接数',
conection_now.conection_now '并发',
CONCAT(buffer_pages_free.buffer_pages_free,'MB') 'buffer pool free',
CONCAT(buffer_pages_total.buffer_pages_total,'GB') 'buffer pool total',
innodb_open_files.innodb_open_files 'opened files',
opend_tables.opend_tables 'opened tables'
FROM
-- 主机状态
(
SELECT
a.ip,
b.NAME,
b.HOST,
b.hostid,
( CASE A.available WHEN '1' THEN '正常' ELSE '异常' END ) 值 ,
d.name 分组
FROM
interface a
LEFT JOIN HOSTS b ON a.hostid = b.hostid
left join hosts_groups c on a.hostid = c.hostid
left join hstgrp d on d.groupid=c.groupid
WHERE
b.flags <>2
-- AND d.NAME='ORACLE'
) host_status left join
-- 内存趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_mem,
ROUND(avg(t.value_avg),2) as avg_mem,
ROUND(max(t.value_max),2) as max_mem
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME in ( 'Memory utilization','Linux: Memory utilization' )
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) mem_trends on host_status.hostid=mem_trends.hostid
left join
-- CPU趋势
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_cpu,
ROUND(avg(t.value_avg),2) as avg_cpu,
ROUND(max(t.value_max),2) as max_cpu
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME in ('CPU utilization','Linux: CPU utilization')
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) CPU_trends on host_status.hostid=CPU_trends.hostid
-- IO趋势
left join
(
SELECT
it.NAME,
it.hostid,
FROM_UNIXTIME( t.clock ),
ROUND(min(t.value_min),2) as min_io,
ROUND(avg(t.value_avg),2) as avg_io,
ROUND(max(t.value_max),2) as max_io
FROM
trends t
JOIN items it ON t.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization%'
AND t.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86400 SECOND )))
GROUP BY it.hostid
) io_trends on host_status.hostid=io_trends.hostid
-- CPU当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) cpu_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('CPU utilization','Linux: CPU utilization')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) cpu_now on host_status.hostid=cpu_now.hostid
-- 内存当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) mem_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('Memory utilization','Linux: Memory utilization')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) mem_now on host_status.hostid=mem_now.hostid
-- IO当前值
left join
(
SELECT
h.hostid,
ROUND(max(hi.VALUE ),2) io_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Disk utilization'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) io_now on host_status.hostid=io_now.hostid
-- swap 剩余
left join
(
SELECT
h.hostid,
round(hi.VALUE/1024/1024/1024,2) swap_free_GB
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
it.NAME in ('Free swap space','Linux: Free swap space')
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) swap_free on host_status.hostid=swap_free.hostid
-- 磁盘使用率
left join
(
SELECT
h.hostid,
it.name,
max(round(hi.VALUE,2)) space_used
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
it.NAME like '%Space utilization%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) space_used on host_status.hostid=space_used.hostid
-- Buffer pool使用率
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,2) buffer_pool_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Buffer pool utilization'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) buffer_pool_now on host_status.hostid=buffer_pool_now.hostid
-- 每秒删除数量（delete）
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) delete_num
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Command Delete per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) delete_num on host_status.hostid=delete_num.hostid
-- 每秒新增数量（insert）
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) insert_num
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Command Insert per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) insert_num on host_status.hostid=insert_num.hostid
-- 每秒查询数量（sleect）
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) select_num
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Command Select per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) select_num on host_status.hostid=select_num.hostid
-- 每分钟修改数量（update）
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) update_num
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Command Update per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) update_num on host_status.hostid=update_num.hostid
-- QPS
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) qps
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Queries per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) qps on host_status.hostid=qps.hostid
-- TPS
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) tps
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Questions per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) tps on host_status.hostid=tps.hostid
-- max connecion
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) max_connection
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Max used connections'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) max_connection on host_status.hostid=max_connection.hostid
-- 每秒建立的连接数
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE,0) connected_ervery_secondes
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Connections per second'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) connected_ervery_secondes on host_status.hostid=connected_ervery_secondes.hostid
-- buffer pool pages total
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE*16/1024/1024,0) buffer_pages_total
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: InnoDB buffer pool pages total'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) buffer_pages_total on host_status.hostid=buffer_pages_total.hostid
-- buffer pool pages free
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
round(hi.VALUE*16/1024,0) buffer_pages_free
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: InnoDB buffer pool pages free'
AND hi.clock >=(
unix_timestamp(
DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) buffer_pages_free on host_status.hostid=buffer_pages_free.hostid
-- Innodb number open files
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE innodb_open_files
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Innodb number open files'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) innodb_open_files on host_status.hostid=innodb_open_files.hostid
-- MySQL: Open tables
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE opend_tables
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Open tables'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) opend_tables on host_status.hostid=opend_tables.hostid
-- 从库IO进程状态
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE IO_thread_status
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_str hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME like 'MySQL: Replication Slave IO Running%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) IO_thread_status on host_status.hostid=IO_thread_status.hostid
-- 从库SQL进程状态
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE SQL_thread_status
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_str hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME like 'MySQL: Replication Slave SQL Running%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) SQL_thread_status on host_status.hostid=SQL_thread_status.hostid
-- 从库延迟秒数
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE Replication_delay
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME like 'MySQL: Replication Seconds Behind Master%'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) Replication_delay on host_status.hostid=Replication_delay.hostid
-- MYSQL数据库状态
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
(case hi.VALUE when 1 then '正常' else '异常' end) mysql_status
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Status'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 8600 SECOND )))
GROUP BY
h.hostid
) mysql_status on host_status.hostid=mysql_status.hostid
-- 已建立线程连接数（包括活动的和不活动的连接）
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE connected
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Threads connected'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 8600 SECOND )))
GROUP BY
h.hostid
) connected on host_status.hostid=connected.hostid
-- 已建立线程连接数（包括活动的和不活动的连接）
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE conection_now
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_uint hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Threads running'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 600 SECOND )))
GROUP BY
h.hostid
) conection_now on host_status.hostid=conection_now.hostid
-- mysql 版本信息
left join
(
SELECT
h.hostid,
h.HOST 主机名,
i.ip,
it.NAME 监控指标,
FROM_UNIXTIME( max(hi.clock) ) 时间,
hi.VALUE mysql_version
FROM
HOSTS h
LEFT JOIN interface i ON h.hostid = i.hostid
LEFT JOIN items it ON h.hostid = it.hostid
LEFT JOIN history_str hi ON hi.itemid = it.itemid
WHERE
i.ip IS NOT NULL
AND it.NAME = 'MySQL: Version'
AND hi.clock >=(unix_timestamp(DATE_SUB( now(), INTERVAL 86600 SECOND )))
GROUP BY
h.hostid
) mysql_version on host_status.hostid=mysql_version.hostid
where host_status.HOST not in ('prod-nc-db-rac01_2','prod-nc-db-rac01_2','prod-nc-db-rac02_3','prod-amb-db-01_2','prod-nc-db-rac02_2')
AND host_status.分组 ='mysql'
group by host_status.HOST
order by host_status.分组,space_used.space_used desc;
CPU top10
select h.hostid,h.host 主机名,i.ip,it.name 监控指标,FROM_UNIXTIME(hi.clock) 时间,max(hi.value) 值 from hosts h
left join interface i on h.hostid=i.hostid
left join items it on h.hostid=it.hostid
left join history hi on hi.itemid=it.itemid
where i.ip is not null and it.name='CPU utilization' and hi.clock>=(unix_timestamp( DATE_SUB(now(), INTERVAL 300 second)))
group by h.hostid
order by FROM_UNIXTIME(hi.clock),max(hi.value) desc
DISK TOP 10
select h.hostid,h.host 主机名,i.ip,it.name 监控指标,FROM_UNIXTIME(hi.clock) 时间,max(hi.value) 值 from hosts h
left join interface i on h.hostid=i.hostid
left join items it on h.hostid=it.hostid
left join history hi on hi.itemid=it.itemid
where i.ip is not null and it.name like '%Disk utilization' and hi.clock>=(unix_timestamp( DATE_SUB(now(), INTERVAL 300 second)))
group by h.hostid
order by FROM_UNIXTIME(hi.clock),max(hi.value) desc
Memory TOP 10
select h.hostid,h.host 主机名,i.ip,it.name 监控指标,FROM_UNIXTIME(hi.clock) 时间,max(hi.value) 值 from hosts h
left join interface i on h.hostid=i.hostid
left join items it on h.hostid=it.hostid
left join history hi on hi.itemid=it.itemid
where i.ip is not null and it.name='Memory utilization' and hi.clock>=(unix_timestamp( DATE_SUB(now(), INTERVAL 300 second)))
group by h.hostid
order by FROM_UNIXTIME(hi.clock),max(hi.value) desc
SPACE USERD TOP 10
select h.hostid,h.host 主机名,i.ip ip ,it.name 盘符,FROM_UNIXTIME(hi.clock) 时间,max(hi.value) 值 from hosts h
left join interface i on h.hostid=i.hostid
left join items it on h.hostid=it.hostid
left join history hi on hi.itemid=it.itemid
where i.ip is not null and it.name like '%Space utilization' and hi.clock>=(unix_timestamp( DATE_SUB(now(), INTERVAL 300 second)))
group by h.hostid,h.host,h.name,i.ip,it.name,FROM_UNIXTIME(hi.clock)
order by max(hi.value) desc
问题告警级别分布
SELECT
COUNT(p.severity) count,
p.severity,
(
CASE p.severity
WHEN '0' THEN
'未定义'
WHEN '1' THEN
'信息'
WHEN '2' THEN
'警告'
WHEN '3' THEN
'一般严重'
WHEN '4' THEN
'严重'
WHEN p.severity = '5' THEN
'灾难'
ELSE
'未知'
END
) severityName
FROM
problem p
LEFT JOIN (
SELECT
s1.triggerid,
(
SELECT
s2.itemid
FROM
functions s2
WHERE
s2.triggerid = s1.triggerid
LIMIT 1
) itemid
FROM
functions s1
GROUP BY
s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
LEFT JOIN `interface` inf ON inf.hostid = h.hostid
WHERE
ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
AND FROM_UNIXTIME(p.clock) >=adddate(curdate(), '-1 days')
GROUP BY
p.severity
ORDER BY
p.severity ASC
问题列表
SELECT
p.eventid,
FROM_UNIXTIME(p.clock) as 时间 ,
p.clock ,
p. NAME 告警内容,
p.acknowledged,
p.severity,
(
CASE p.severity
WHEN '0' THEN
'未定义'
WHEN '1' THEN
'信息'
WHEN '2' THEN
'警告'
WHEN '3' THEN
'一般严重'
WHEN '4' THEN
'严重'
WHEN p.severity = '5' THEN
'灾难'
ELSE
'未知'
END
) 严重等级,
p.objectid,
i.`status`,
i.key_,
f.itemid,
h. HOST 主机名,
h. NAME,
inf.ip id地址,
inf. PORT
FROM
problem p
LEFT JOIN (
SELECT
s1.triggerid,
(
SELECT
s2.itemid
FROM
functions s2
WHERE
s2.triggerid = s1.triggerid
LIMIT 1
) itemid
FROM
functions s1
GROUP BY
s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
LEFT JOIN `interface` inf ON inf.hostid = h.hostid
WHERE
ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
AND FROM_UNIXTIME(p.clock) >=adddate(curdate(), '-0.5 days')
ORDER BY
clock DESC
问题主机组
SELECT
count(DISTINCT h.hostid) '问题总数',
(
SELECT
COUNT(hg.hostid)
FROM
hosts_groups hg
WHERE
hg.groupid = hs.groupid
) '主机数量',
(
SELECT
COUNT(hg.hostid)
FROM
hosts_groups hg
WHERE
hg.groupid = hs.groupid
) - count(DISTINCT h.hostid) '正服务器数',
hs. NAME '服务器组'
FROM
problem p
LEFT JOIN (
SELECT
s1.triggerid,
(
SELECT
s2.itemid
FROM
functions s2
WHERE
s2.triggerid = s1.triggerid
LIMIT 1
) itemid
FROM
functions s1
GROUP BY
s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
LEFT JOIN hosts_groups hg ON hg.hostid = h.hostid
LEFT JOIN hstgrp hs ON hs.groupid = hg.groupid
WHERE
ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
GROUP BY
hs. NAME,
hs.groupid
ORDER BY
'服务器组'
正常主机总数
select count(*) '监控主机数' from interface where available=1
主机问题排行
SELECT
COUNT(h. NAME) '问题总数',
h. NAME '主机名'
FROM
problem p
LEFT JOIN (
SELECT
s1.triggerid,
(
SELECT
s2.itemid
FROM
functions s2
WHERE
s2.triggerid = s1.triggerid
LIMIT 1
) itemid
FROM
functions s1
GROUP BY
s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
WHERE
ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
GROUP BY
h. NAME
ORDER BY
'问题总数' DESC
