前段时间做服务器监控用到了一些zabbix数据库查询的东西,首先需要查询到ip对应的主机的操作系统(我是根据网神的监控来拿的),然后再查询zabbix数据mark一下。直接给xml吧
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.thinkgem.jeesite.soc.dao.HostUsageDao">
<sql id="HostUsageColumns">
a.taskId AS "taskId",
a.IP AS "IP",
a.categoryId AS "categoryId",
a.indexId AS "indexId",
a.valueType AS "valueType",
a.numericValue AS "numericValue",
a.textValue AS "textValue",
a.eventSeverity AS "eventSeverity",
a.time AS "time"
</sql>
<sql id="HostUsageJoins">
LEFT JOIN taskconf b ON a.indexId = b.indexId
AND a.TaskId = b.TaskId AND a.categoryId = b.categoryId
</sql>
<!-- 获取CPU利用率 -->
<select id="getCpuUsage" resultType="HostUsage">
SELECT b.indexName AS "indexName",
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxcpuutilization a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowscpuutilization a
</when>
<otherwise>
FROM md_windowscpuutilization a
</otherwise>
</choose>
<include refid="HostUsageJoins"/>
WHERE IP = #{IP}
ORDER BY time DESC
LIMIT 0,1
</select>
<!-- 获取CPU利用率趋势数据 -->
<select id="getCpuUsage_trend" resultType="HostUsage">
SELECT
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxcpuutilization a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowscpuutilization a
</when>
<otherwise>
FROM md_windowscpuutilization a
</otherwise>
</choose>
WHERE IP = #{IP}
<choose>
<when test="bTime!= null and bTime>0">
AND a.time >= #{bTime}
ORDER BY time DESC
</when>
<otherwise>
ORDER BY time DESC
LIMIT 0,30
</otherwise>
</choose>
</select>
<!-- 获取内存利用率 -->
<select id="getMemUsage" resultType="HostUsage">
SELECT b.indexName AS "indexName",
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxmemutilization a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsmemutilization a
</when>
<otherwise>
FROM md_windowsmemutilization a
</otherwise>
</choose>
<include refid="HostUsageJoins"/>
WHERE a.IP = #{IP}
ORDER BY a.time DESC
LIMIT 0,1
</select>
<!-- 获取内存利用率趋势数据-->
<select id="getMemUsage_trend" resultType="HostUsage">
SELECT
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxmemutilization a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsmemutilization a
</when>
<otherwise>
FROM md_windowsmemutilization a
</otherwise>
</choose>
WHERE IP = #{IP}
<choose>
<when test="bTime!= null and bTime>0">
AND a.time >= #{bTime}
ORDER BY time DESC
</when>
<otherwise>
ORDER BY time DESC
LIMIT 0,30
</otherwise>
</choose>
</select>
<!-- 获取存储利用率 -->
<select id="getStorageUsage" resultType="HostUsage">
SELECT b.indexName AS "indexName",
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxstorageutilization a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsstorageutilization a
</when>
<otherwise>
FROM md_windowsstorageutilization a
</otherwise>
</choose>
<include refid="HostUsageJoins"/>
WHERE a.IP = #{IP}
AND a.time = (SELECT time
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxstorageutilization c
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsstorageutilization c
</when>
<otherwise>
FROM md_windowsstorageutilization c
</otherwise>
</choose>
WHERE c.IP = #{IP}
ORDER BY time DESC
LIMIT 0,1)
</select>
<!-- 获取存储利用率趋势数据 -->
<select id="getStorageUsage_trend" resultType="HostUsage">
SELECT
a.taskId AS "taskId",
a.IP AS "IP",
a.categoryId AS "categoryId",
a.indexId AS "indexId",
a.valueType AS "valueType",
avg(a.numericValue) AS "numericValue",
a.textValue AS "textValue",
a.eventSeverity AS "eventSeverity",
a.time AS "time"
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxstorageutilization a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsstorageutilization a
</when>
<otherwise>
FROM md_windowsstorageutilization a
</otherwise>
</choose>
WHERE IP = #{IP}
<choose>
<when test="bTime!= null and bTime>0">
GROUP BY time
AND a.time >= #{bTime}
ORDER BY time DESC
</when>
<otherwise>
GROUP BY time
ORDER BY time DESC
LIMIT 0,30
</otherwise>
</choose>
</select>
<!-- 获取进口流量 -->
<select id="getInfluxUsage" resultType="HostUsage">
SELECT b.indexName AS "indexName",
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxinflux a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsinflux a
</when>
<otherwise>
FROM md_windowsinflux a
</otherwise>
</choose>
<include refid="HostUsageJoins"/>
WHERE IP = #{IP}
AND time = (SELECT time
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxinflux a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsinflux a
</when>
<otherwise>
FROM md_windowsinflux a
</otherwise>
</choose>
WHERE IP = #{IP}
ORDER BY time DESC
LIMIT 0,1)
</select>
<!-- 获取进口流量趋势数据 -->
<select id="getInfluxUsage_trend" resultType="HostUsage">
SELECT
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxinflux a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsinflux a
</when>
<otherwise>
FROM md_windowsinflux a
</otherwise>
</choose>
WHERE IP = #{IP}
<choose>
<when test="bTime!= null and bTime>0">
AND a.time >= #{bTime}
ORDER BY time DESC
</when>
<otherwise>
AND a.numericValue > 0
ORDER BY time DESC
LIMIT 0,30
</otherwise>
</choose>
</select>
<!-- 获取出口流量 -->
<select id="getOutfluxUsage" resultType="HostUsage">
SELECT b.indexName AS "indexName",
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxoutflux a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsoutflux a
</when>
<otherwise>
FROM md_windowsoutflux a
</otherwise>
</choose>
<include refid="HostUsageJoins"/>
WHERE IP = #{IP}
AND time = (SELECT time
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxoutflux a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsoutflux a
</when>
<otherwise>
FROM md_windowsoutflux a
</otherwise>
</choose>
WHERE IP = #{IP}
ORDER BY time DESC
LIMIT 0,1)
</select>
<select id="getOutfluxUsage_trend" resultType="HostUsage">
SELECT
<include refid="HostUsageColumns"/>
<choose>
<when test="osType == 'linux'.toString()">
FROM md_linuxoutflux a
</when>
<when test="osType == 'windows'.toString()">
FROM md_windowsoutflux a
</when>
<otherwise>
FROM md_windowsoutflux a
</otherwise>
</choose>
WHERE IP = #{IP}
<choose>
<when test="bTime!= null and bTime>0">
AND a.time >= #{bTime}
ORDER BY time DESC
</when>
<otherwise>
AND a.numericValue > 0
ORDER BY time DESC
LIMIT 0,30
</otherwise>
</choose>
</select>
<!-- 从网神获取主机操作系统类型 -->
<select id="getOsForSoc" resultType="HostUsage">
SELECT
b.`name` AS osType,
a.Ip AS IP
FROM monitortask a
JOIN monitor_dictionary b ON a.categoryId = b.categoryId
WHERE a.Ip = #{IP}
AND b.type = '主机'
</select>
</mapper>