服务器巡检报告-基于categraf 采集数据-存入Prometheus-写入mysql后生成报告

zserverpiclass.py

# Create by：ember.zhang
# 为什么不问问神奇的海螺呢丶
import json
import pymysql
import requests
import pandas as pd
from jinja2 import Environment, FileSystemLoader
from sqlalchemy import create_engine

def schtml(cdate, zdatah, zjdata, rp):
env = Environment(loader=FileSystemLoader('/data/zz/zscript/serverpi/'))
template = env.get_template('zserverpitemp.html')
with open('/data/zz/zscript/serverpi/zserverpireport/%s' % rp, 'w+') as fout:
html_content = template.render(cdate=cdate,
zdatah=zdatah,
zjdata=zjdata)
fout.write(html_content)
print("服务器巡检报告已生成"+cdate)
# def schtml(cdate, zdatah, zjdata, rp):
# env = Environment(loader=FileSystemLoader('E:/MagicConch/montior/program/serverpi/'))
# template = env.get_template('zserverpi.html')
# with open('E:/MagicConch/montior/program/serverpi/zserverpireport/%s' % rp, 'w+') as fout:
# html_content = template.render(cdate=cdate,
# zdatah=zdatah,
# zjdata=zjdata)
# fout.write(html_content)
# 传入文件
def post_file(file, wx_url, id_url):
# wx_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=29d012fd-001c-4073-b5c1-b396ea1d9681'
data = {'file': open(file, 'rb')}
# 请求id_url(将文件上传微信临时平台),返回media_id
# id_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key=29d012fd-001c-4073-b5c1-b396ea1d9681&type=file'
response = requests.post(url=id_url, files=data)
print(response.text)
json_res = response.json()
media_id = json_res['media_id']
data = {"msgtype": "file",
"file": {"media_id": media_id}
}
# 发送文件
result = requests.post(url=wx_url, json=data)
return (result)

# 发送文字
def send_message(message, person_list, wx_url):
# wx_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=29d012fd-001c-4073-b5c1-b396ea1d9681&type'
data = {
"msgtype": "text",
"text": {
"content": message,
"mentioned_list": person_list # 需@人的姓名

}
}
r = requests.post(url=wx_url,
json=data) # url中的xxx是你的机器人webhook对应片段，不要轻易泄露出去否则任何人都可以控制你的机器人哦

# 读取mysql 巡检数据 返回DataFrame格式
def rmysql(v_sql):
db = pymysql.connect(host='10.12.8.120', user='root', password='root@mnxxxx', database='zapp', autocommit=True)
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute(v_sql)
# # 使用 fetchone() 方法获取单条数据.
result = cursor.fetchall() # 所有结果
column = [index[0] for index in cursor.description] # 列名
data = pd.DataFrame([list(i) for i in result], columns=column) # row是数据库返回的一条一条记录，其中的每一天和column写成字典，最后就是字典数组
db.close()
return data

# 写入数据库
def wmysql(v_sql):
db = pymysql.connect(host='10.12.8.120', user='root', password='root@mn8120', database='zapp', autocommit=True)
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute(v_sql)
# # 使用 fetchone() 方法获取单条数据.
# data = cursor.fetchone()
# return data
# 关闭数据库连接
db.close()

def rtdf(value,cname):
listz = []
for i in range(len(value)):
tt = value[i]['metric']
tt.update({'checktime': value[i]['value'][0]})
tt.update({cname: value[i]['value'][1]})
listz.append(tt)
return pd.json_normalize(listz)

class pm:
def __init__(self, serveraddr):
self.serveraddr = serveraddr
self.hh = 'http://' + self.serveraddr + ':7746' + '/api/v1/query?query='
self.engine = create_engine('mysql+mysqlconnector://root:root%40mnxxxx@10.12.8.120:3306/zapp')
self.getHost()
self.getCpu()
self.getMemory()
self.getDisk()

# 主机
def getHost(self):
wmysql(v_sql='''truncate table tm_host;''')
hsql = 'system_uptime'
url = self.hh + hsql
response = requests.request('get', url)
if response.status_code == 200:
hdf=rtdf(value=response.json()['data']['result'],cname='uptime')
hdf[['ident','uptime','checktime']].to_sql('tm_host', con=self.engine, index=False, if_exists='append')
wmysql(v_sql='''INSERT into tb_zhost (hostip,hostname,os,server,service,uptime,checktime)
SELECT SUBSTRING_INDEX(a.ident,'-',-1),
IF(left(a.ident,LENGTH(a.ident)-1-LENGTH(SUBSTRING_INDEX(a.ident,'-',-1)))='',a.ident,left(a.ident,LENGTH(a.ident)-1-LENGTH(SUBSTRING_INDEX(a.ident,'-',-1)))),
t.os,ifnull(bg.name,'/'),ifnull(t.note,'/'),concat(FORMAT(uptime/3600/24,2),'天') ,
CAST(from_unixtime( a.checktime,'%Y-%m-%d') as char) as checktime from tm_host a
inner join n9e_v6.target t on a.ident =t.ident
left join n9e_v6.target_busi_group tb on t.ident =tb.target_ident
LEFT join n9e_v6.busi_group bg on tb.group_id =bg.id
WHERE not exists
(SELECT 1 from tb_zhost b where CAST(from_unixtime(a.checktime,'%Y-%m-%d')as char) = CAST(b.checktime as char )and SUBSTRING_INDEX(a.ident,'-',-1) =b.hostip );''')
#cpu
def getCpu(self):
wmysql(v_sql='''truncate table tm_cpu;''')
csql = 'avg_over_time (cpu_usage_active [1d])'
url = self.hh + csql
response = requests.request('GET', url)
if response.status_code == 200:
cdf = rtdf(value=response.json()['data']['result'], cname='cpuuse')
cdf[['ident','cpuuse','checktime']].to_sql('tm_cpu', con=self.engine, index=False, if_exists='append')
wmysql(v_sql='''INSERT into tb_zcpu(hostip,cpuuse,checktime)
select SUBSTRING_INDEX(ident,'-',-1),
CONCAT(FORMAT(a.cpuuse,2),'%') as cpuuse,
CAST(from_unixtime( a.checktime,'%Y-%m-%d') as char) as checktime from tm_cpu a
WHERE not exists
(SELECT 1 from tb_zcpu b where CAST(from_unixtime(a.checktime,'%Y-%m-%d')as char) = cast(b.checktime as char )and SUBSTRING_INDEX(ident,'-',-1) =b.hostip );''')
# 内存
def getMemory(self):
wmysql(v_sql='''truncate table tm_mem;''')
musql = 'avg_over_time (mem_used [1d])'
mtsql = 'mem_total'
muurl = self.hh+musql
mturl = self.hh+mtsql
response = requests.request('get', muurl)
if response.status_code == 200:
mudf = rtdf(value=response.json()['data']['result'], cname='memuse')
response = requests.request('get', mturl)
if response.status_code == 200:
mtdf = rtdf(value=response.json()['data']['result'], cname='memtotal')
mdf=pd.merge(mudf,mtdf, on='ident',how='inner')[['ident','memuse','memtotal','checktime_x']]
mdf.to_sql('tm_mem', con=self.engine, index=False, if_exists='append')
wmysql(v_sql='''INSERT into tb_zmem(hostip,memuse,memtotal,useratio,checktime)
select SUBSTRING_INDEX(ident,'-',-1),
concat(FORMAT(memuse/1073741824,2),'G') as memuse,
concat(FORMAT(memtotal/1073741824,2),'G') as memtotal,
concat(FORMAT(memuse/memtotal *100,2),'%' ) as useratio,
CAST(from_unixtime( a.checktime_x,'%Y-%m-%d') as char) as checktime from tm_mem a
WHERE not exists
(SELECT 1 from tb_zmem b where CAST(from_unixtime(a.checktime_x,'%Y-%m-%d')as char) = cast(b.checktime as char ) and SUBSTRING_INDEX(ident,'-',-1) =b.hostip );''')
# 磁盘
def getDisk(self):
wmysql(v_sql='''truncate table tm_disk;''')
dtsql = 'disk_total'
dusql = 'disk_used'
dturl = self.hh+dtsql
duurl = self.hh+dusql
response = requests.request('get', dturl)
if response.status_code == 200:
dtdf = rtdf(value=response.json()['data']['result'], cname='disktotal')
response = requests.request('get', duurl)
if response.status_code == 200:
dudf = rtdf(value=response.json()['data']['result'], cname='diskuse')
ddf=pd.merge(dtdf,dudf, on=['ident','device','fstype','mode','path'],how='inner')[['ident','device','fstype','mode','path','diskuse','disktotal','checktime_x']]
ddf.to_sql('tm_disk', con=self.engine, index=False, if_exists='append')
wmysql('''INSERT into tb_zdisk(hostip,device,fstype,diskuse,disktotal,diskavail,useratio,mode,path,checktime)
select SUBSTRING_INDEX(ident,'-',-1),
a.device,
a.fstype,
concat(FORMAT(a.diskuse/1073741824,2),'G') as diskuse,
concat(FORMAT(a.disktotal/1073741824,2),'G') as disktotal,
concat(FORMAT((a.disktotal-a.diskuse)/1073741824,2),'G') as diskavail,
concat(FORMAT(a.diskuse/a.disktotal *100,2),'%' ) as useratio,
a.mode,
a.path,
CAST(from_unixtime( a.checktime_x,'%Y-%m-%d') as char) as checktime from tm_disk a
WHERE not exists
(SELECT 1 from tb_zdisk b where CAST(from_unixtime(a.checktime_x,'%Y-%m-%d')as char) = cast(b.checktime as char ) and SUBSTRING_INDEX(ident,'-',-1) =b.hostip and a.device=b.device );''')

zserverpimain.py

# Create by：ember.zhang
# 为什么不问问神奇的海螺呢丶
import zserverpiclass as z
from datetime import datetime

pm = z.pm(serveraddr='10.12.8.120')

datah=z.rmysql(v_sql='''
select hostip,hostname,os,`server`,service,uptime,
getha(hostip,NULL) as alarm,
checktime
from tb_zhost where checktime =CURDATE();
''')

datac=z.rmysql(v_sql='''
select hostip,cpuuse as cpuuse,checktime ,
if(replace(cpuuse,'%','')>80,1,0) as alarm
from tb_zcpu where checktime =CURDATE();
''')

datam=z.rmysql(v_sql='''
select hostip,memtotal,memuse,
useratio,checktime ,
IF(replace(useratio,'%','')>90,1,0) as alarm
from tb_zmem where checktime =CURDATE();
''')

datad = z.rmysql(v_sql='''
select hostip,device,fstype,diskuse,
disktotal,diskavail,useratio,mode,path,checktime ,
if(replace(useratio,'%','')>90,1,0) as alarm
from tb_zdisk where checktime =CURDATE();
''')

zjdata = []

for i in datam.to_dict('records'):
# print(i)
icdatc = datac[(datac['hostip'] == i['hostip'])].to_dict('records')

iddata = datad[(datad['hostip'] == i['hostip'])].to_dict('records')
# print(iddata)
idata = {'cpu': icdatc[0], 'mem': i, 'disk': iddata}
# print(idata)
zjdata.append(idata)

zdatah = datah.to_dict('records')
cdate = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
rp = 'serverpireport' + cdate.replace(' ', '').replace(':', '') + '.html'
z.schtml(zdatah=zdatah, zjdata=zjdata, cdate=cdate, rp=rp)

#0 8 * * * /usr/local/bin/python3 /data/zz/zscript/serverpi/zserverpimain.py >> /data/zz/zscript/serverpi/run.log 2>&1

# #发送巡检报告
# wx_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=8eb4a70d-39c6-40af-a6ee-88e79114f32e'
# id_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=8eb4a70d-39c6-40af-a6ee-88e79114f32e&type=file'
# z.post_file('/zscript/serverpi/zserverpireport/%s' % rp, wx_url=wx_url, id_url=id_url)
#
# #发送通知
# wx_url = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=8eb4a70d-39c6-40af-a6ee-88e79114f32e&type'
# z.send_message(message='今日巡检报告已生成，请注意查收', person_list='@all', wx_url=wx_url)

zserverpitemp.html

<!–
#Create by：ember.zhang
#为什么不问问神奇的海螺呢丶
–>
<html>
<head>
<title>服务器巡检报告 — {{ cdate }}</title>
<style type='text/css'>
* {
margin: 0;
padding: 0;
box-sizing: border-box;
}
body {
font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
font-size: 14px;
line-height: 1.6;
color: #333;
background-color: #f5f7fa;
padding: 20px;
}
.container {
width: 100%;
max-width: 1200px;
margin: 0 auto;
background-color: #fff;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0,0,0,0.1);
padding: 30px;
}
h2 {
text-align: center;
color: #2c3e50;
margin-bottom: 30px;
padding-bottom: 15px;
border-bottom: 2px solid #23a85b;
}
.index-table {
margin-bottom: 40px;
}
table {
width: 100%;
border-collapse: collapse;
margin: 15px 0;
border-radius: 6px;
overflow: hidden;
box-shadow: 0 1px 3px rgba(0,0,0,0.1);
}
th, td {
padding: 12px 15px;
text-align: left;
border-bottom: 1px solid #e0e0e0;
}
th {
background-color: #34db6c;
color: white;
font-weight: 600;
}
tr:last-child td {
border-bottom: none;
}
tr:hover td {
background-color: #f9f9f9;
}
.section-title {
color: #2c3e50;
margin: 30px 0 15px;
padding-left: 10px;
border-left: 4px solid #34db7a;
}
.node-section {
margin-bottom: 40px;
padding-bottom: 20px;
border-bottom: 1px dashed #e0e0e0;
}
.node-section:last-child {
border-bottom: none;
}
.back-to-top {
display: inline-block;
color: #3498db;
text-decoration: none;
font-weight: 500;
padding: 5px 10px;
border: 1px solid #3498db;
border-radius: 4px;
float: right;
transition: all 0.3s ease;
}
.back-to-top:hover {
background-color: #3498db;
color: white;
}
.status-warning {
background-color: #fdecea;
color: #e74c3c;
font-weight: 600;
text-align: center;
}
.status-normal {
background-color: #eafaf1;
color: #27ae60;
font-weight: 600;
text-align: center;
}
.category-header {
background-color: #2c3e50;
color: white;
font-weight: 600;
}
a {
color: #3498db;
text-decoration: none;
}
a:hover {
color: #2980b9;
text-decoration: underline;
}
.index-link {
font-weight: 500;
}
.disk-table th,
.mem-table th,
.cpu-table th {
background-color: #2980b9;
}
</style>
</head>
<body>
<div class="container">
<a name='index' />
<h2>服务器巡检报告 — {{ cdate }}</h2>

<!– 索引表 –>
<table class="index-table">
<thead>
<tr>
<th style='width: 100px;'>节点IP</th>
<th>主机名</th>
<th>操作系统</th>
<th>所属项目</th>
<th>服务</th>
<th>运行时间</th>
<th>告警</th>
<th>检查时间</th>
</tr>
</thead>
{% for ihc in zdatah %}
<tr>
<td><a href='#{{ ihc["hostip"] }}' class="index-link">{{ ihc["hostip"] }}</a></td>
<td>{{ ihc["hostname"] }}</td>
<td>{{ ihc["os"] }}</td>
<td>{{ ihc["server"] }}</td>
<td>{{ ihc["service"] }}</td>
<td>{{ ihc["uptime"] }}</td>
{% if ihc["alarm"] == 1 %}
<td class="status-warning">Warning</td>
{% else %}
<td class="status-normal">Normal</td>
{% endif %}
<td>{{ ihc["checktime"] }}</td>
</tr>
{% endfor %}
</table>

<!– 节点详情 –>
{% for ic in zjdata %}
<div class="node-section">
<a name='{{ ic["cpu"]["hostip"] }}' />
<div>
<a href='#index' class="back-to-top">返回目录</a>
<h3 class="section-title">节点IP: {{ ic["cpu"]["hostip"] }}</h3>
</div>

<!– CPU信息 –>
<table class="cpu-table">
<thead>
<tr>
<th class="category-header" colspan="3">CPU信息</th>
</tr>
<tr>
<th style='width: 100px;'>节点IP</th>
<th>CPU使用率</th>
<th>检查时间</th>
</tr>
</thead>
<tr>
<td style='text-align: center;'>{{ ic["cpu"]["hostip"] }}</td>
{% if ic["cpu"]["alarm"] == 1 %}
<td class="status-warning">{{ ic["cpu"]["cpuuse"] }}</td>
{% else %}
<td class="status-normal">{{ ic["cpu"]["cpuuse"] }}</td>
{% endif %}
<td>{{ ic["cpu"]["checktime"] }}</td>
</tr>
</table>

<!– 内存信息 –>
<table class="mem-table">
<thead>
<tr>
<th class="category-header" colspan="5">内存信息</th>
</tr>
<tr>
<th style='width: 100px;'>节点IP</th>
<th>总量</th>
<th>已用</th>
<th>使用率</th>
<th>检查时间</th>
</tr>
</thead>
<tr>
<td style='text-align: center;'>{{ ic["mem"]["hostip"] }}</td>
<td>{{ ic["mem"]["memtotal"] }}</td>
<td>{{ ic["mem"]["memuse"] }}</td>
{% if ic["mem"]["alarm"] == 1 %}
<td class="status-warning">{{ ic["mem"]["useratio"] }}</td>
{% else %}
<td class="status-normal">{{ ic["mem"]["useratio"] }}</td>
{% endif %}
<td>{{ ic["mem"]["checktime"] }}</td>
</tr>
</table>

<!– 磁盘信息 –>
<table class="disk-table">
<thead>
<tr>
<th class="category-header" colspan="10">磁盘信息</th>
</tr>
<tr>
<th style='width: 100px;'>节点IP</th>
<th>文件系统</th>
<th>类型</th>
<th>总量</th>
<th>已用</th>
<th>可用</th>
<th>使用率</th>
<th>模式</th>
<th>路径</th>
<th>检查时间</th>
</tr>
</thead>
{% for icd in ic["disk"] %}
<tr>
<td style='text-align: center;'>{{ icd["hostip"] }}</td>
<td>{{ icd["device"] }}</td>
<td>{{ icd["fstype"] }}</td>
<td>{{ icd["disktotal"] }}</td>
<td>{{ icd["diskuse"] }}</td>
<td>{{ icd["diskavail"] }}</td>
{% if icd["alarm"] == 1 %}
<td class="status-warning">{{ icd["useratio"] }}</td>
{% else %}
<td class="status-normal">{{ icd["useratio"] }}</td>
{% endif %}
<td>{{ icd["mode"] }}</td>
<td>{{ icd["path"] }}</td>
<td>{{ icd["checktime"] }}</td>
</tr>
{% endfor %}
</table>
</div>
{% endfor %}
</div>
</body>
</html>

zapp db相关表及函数

— zapp.tb_zcpu definition

CREATE TABLE `tb_zcpu` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '主机ip',
`cpuuse` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '使用率',
`checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
`inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '写入时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=746 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='cpu信息';

— zapp.tb_zdisk definition

CREATE TABLE `tb_zdisk` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '主机ip',
`device` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '文件系统',
`fstype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '文件系统类型',
`diskuse` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '总量',
`disktotal` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '已用',
`diskavail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '可用',
`useratio` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '已用百分比',
`mode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '模式',
`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '路径',
`checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
`inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '写入时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2922 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='磁盘信息';

— zapp.tb_zhost definition

CREATE TABLE `tb_zhost` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`hostip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '主机ip',
`hostname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '主机名',
`os` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '操作系统',
`server` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '所属项目',
`service` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '运行服务',
`uptime` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '运行时间',
`checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
`inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '数据写入时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=714 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='主机信息';

— zapp.tb_zmem definition

CREATE TABLE `tb_zmem` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '主机ip',
`memuse` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '已用',
`memtotal` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '总量',
`useratio` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '已用百分比',
`checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '检查时间',
`inserttime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '写入时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=714 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='内存信息';

— zapp.tm_cpu definition

CREATE TABLE `tm_cpu` (
`ident` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`cpuuse` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

— zapp.tm_disk definition

CREATE TABLE `tm_disk` (
`ident` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`device` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`fstype` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`mode` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`path` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`diskuse` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`disktotal` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`checktime_x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

— zapp.tm_host definition

CREATE TABLE `tm_host` (
`ident` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`os` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`server` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`service` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`uptime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

— zapp.tm_mem definition

CREATE TABLE `tm_mem` (
`ident` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`memuse` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`memtotal` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`checktime_x` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

— `zapp`.`getalarm`
CREATE DEFINER=`root`@`%` FUNCTION `zapp`.`getalarm`(inhostip VARCHAR(100),indate date) RETURNS tinyint
BEGIN

DECLARE outalarm TINYINT;

IF indate is NULL THEN
set indate =CURRENT_DATE() ;
end if;

WITH za as (
select
if(FORMAT(cpuuse,2)>80,1,0) as alarm
from tb_targetc where DATE_FORMAT(inserttime,'%Y-%m-%d') =indate
and hostip =inhostip
UNION all
select
IF(FORMAT((memTotal–(buffers+cached+memFree))/memTotal *100,2)>80,1,0) as alarm
from tb_targetm where DATE_FORMAT(inserttime,'%Y-%m-%d') =indate
and hostip =inhostip
UNION all
select
if(FORMAT(useratio*100,2)>80,1,0) as alarm
from tb_targetd where DATE_FORMAT(inserttime,'%Y-%m-%d') =indate
and hostip =inhostip
)
select if(sum(alarm) >0,1,0) into outalarm from za
;

RETURN outalarm;
END;
— `zapp`.`getha`
CREATE DEFINER=`root`@`%` FUNCTION `zapp`.`getha`(inhostip VARCHAR(100),indate date) RETURNS tinyint
BEGIN

DECLARE outalarm TINYINT;

IF indate is NULL THEN
set indate =CURRENT_DATE() ;
end if;

WITH za as (
select
if(replace(cpuuse,'%','')>80,1,0) as alarm
from tb_zcpu where checktime =indate
and hostip =inhostip
UNION all
select
IF(replace(useratio,'%','')>90,1,0) as alarm
from tb_zmem where checktime =indate
and hostip =inhostip
UNION all
select
if(replace(useratio,'%','')>90,1,0) as alarm
from tb_zdisk where checktime =indate
and hostip =inhostip
)
select if(sum(alarm) >0,1,0) into outalarm from za
;

RETURN outalarm;
END;