Python 基于 Prometheus 数据生成巡检报告_prometheus巡检

作者：小惠珠哦 | 2024-07-27 20:59:13

踩

prometheus巡检

设计思路

1、基于Prometheus 开放接口获取数据（node_exporte/windows_exporter）
2、存放数据至mysql数据库
3、基于jinja2 编写模板生成巡检报告.html
全量代码

读取Prometheus 接口获取数据

import requests


def getMemory(serveraddr):
        hh = 'http://'+serveraddr+':7746'+'/api/v1/query?query='
        nesqlb ='node_memory_Buffers_bytes'
        nesqlc ='node_memory_Cached_bytes'
        nesqlf ='node_memory_MemFree_bytes'
        nesqlt ='node_memory_MemTotal_bytes'
        wesqlf ='windows_os_physical_memory_free_bytes'
        wesqlt ='windows_cs_physical_memory_bytes'
        url = [hh+nesqlb,hh+nesqlc,hh+nesqlf,hh+nesqlt,hh+wesqlf,hh+wesqlt]
        for i in url :
            response = requests.request('get', i )
            if response.status_code == 200:
                if len(response.json()['data']['result']) != 0:
                    value = list(response.json()['data']['result'])
                    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
'运行

Prometheus 数据处理便于拼接sql

def dtd(value):
    dlist=[]
    for n in  range(len(value)):
        value1=[]
        #hostip
        value1.append(value[n]['metric']['instance'][:-5]) 
        try :
            value1.append(value[n]['metric']['device'])
            value1.append(value[n]['metric']['fstype'])
            value1.append(value[n]['metric']['mountpoint'])
        #windows
        except Exception:
                value1.append(value[n]['metric']['volume'] )
                value1.append('/')
                value1.append('/')   
            
        #值
        value1.append(value[n]['value'][1])
        #时间
        value1.append(value[n]['value'][0])
        dlist.append(value1) 
    return [str(tuple(dlist[n])) for n in  range(len(dlist))]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
'运行

写入mysql 数据库封装函数


import pymysql

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

读取mysql 巡检数据返回DataFrame格式

import pymysql
import pandas as pd

def rmysql(v_sql):
    db = pymysql.connect(host='xxx',user='root',password='xxxx',database='xxx',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写成字典，最后就是字典数组
    return data
# 关闭数据库连接
    db.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

基于jinja2 封装生成html文件函数


from jinja2 import Environment, FileSystemLoader 

def schtml( cdate,zdatah, zjdata):
    env = Environment(loader=FileSystemLoader('./'))
    template = env.get_template('zserverpitemp.html')  
    rp='serverpireport'+str(cdate).replace(' ','').replace(':','')+'.html'
    print(rp)
    with open(rp,'w+') as fout:   
        html_content = template.render(cdate=cdate , 
                                        zdatah=zdatah,
                                        zjdata=zjdata)
        fout.write(html_content)
1
2
3
4
5
6
7
8
9
10
11
12
13
'运行

jinja2模板

<!-- 
#Create by：zhangyongze
#为什么不问问神奇的海螺呢丶
 -->
<html>
<head>
    <title>服务器巡检报告 -- {{ cdate }} </title>
    <style type='text/css'>
        body, td
        {
            font-family: verdana;
            font-size: 12px;
            line-height: 150%;
        }
        table
        {
            width: 100%;
            background-color: #ccc;
            margin: 5px 0;
        }
        td
        {
            background-color: #fff;
            padding: 3px;
            padding-left: 10px;
        }
        thead td
        {
            text-align: center;
            font-weight: bold;
            background-color: #eee;
        }
        a:link, a:visited, a:active
        {
            color: #015FB6;
            text-decoration: none;
        }
        a:hover
        {
            color: #E33E06;
        }
    </style>
</head>
<body style='text-align: center;'>
    <div style='width: 800px; margin: 20px auto; text-align: left;'>
        <a name='index' />
        <h2 style='text-align: center; line-height: 50px;'>
            服务器巡检报告 -- {{ cdate }}</h2>
            
        <table cellspacing='1' cellpadding='0'>
            <thead><tr>
                <td style='width: 40px;'>节点IP：</td>
                <td>主机名</td>
                <td>操作系统</td>
                <td>检查时间</td>
            </tr></thead>
            {% for ihc in zdatah %}
            <tr>
                
                <td><a href='#{{ ihc["hostip"] }}'>{{ ihc["hostip"] }}</a></td>
                <td>{{ ihc["hostname"] }}</td>
                <td>{{ ihc["systemos"] }}</td>
                <td>{{ ihc["inserttime"] }}</td>
                
            </tr> 
            {% endfor %}
        </table>
       
        {% for ic in zjdata %} 

        <a name='{{ ic["cpu"]["hostip"] }}' />
        <div style='margin-top: 30px;'>
            <a href='#index' style='float: right; margin-top: 6px;'>返回目录</a>
        <b>节点IP: {{ ic["cpu"]["hostip"] }}</b>
        </div>

         
        <table cellspacing='1' cellpadding='0'>
            <thead>
                <tr><td  colspan="9"  >CPU</tr>
            <tr>
            <td style='width: 40px;'>节点IP</td>
            <td>CPU使用率</td>
            <td>检查时间</td>
        </tr></thead>
		<tr><td style='text-align: center;'>{{ ic["cpu"]["hostip"] }}</td>
                {% if ic["cpu"]["alarm"] ==1  %}
                <td align='center' style="background-color: #E33E06;" >{{ ic["cpu"]["cpuuse"] }}</td>
                {% else %}
                <td align='center' style="color:chartreuse;" >{{ ic["cpu"]["cpuuse"] }}</td>
                {% endif  %}
                <td align='center'>{{ ic["cpu"]["checktime"] }}</td>
            </tr>
        </table>

     
        <table cellspacing='1' cellpadding='0'><thead>
            <tr><td  colspan="9"  >内存</tr>
            <tr>
                <td style='width: 40px;'>节点IP</td>
            <td>缓冲</td>
            <td>缓存</td>
            <td>空闲</td>
            <td>总量</td>
            <td>已用</td>
            <td>可用</td>
            <td>使用率</td>
            <td>检查时间</td>
        </tr></thead>
			<tr><td style='text-align: center;'>{{ ic["mem"]["hostip"] }}</td>
                <td>{{ ic["mem"]["buffers"] }}</td>
                <td align='center'>{{ ic["mem"]["cached"] }}</td>
                <td align='center'>{{ ic["mem"]["memfree"] }}</td>
                <td align='center'>{{ ic["mem"]["memtotal"] }}</td>
                <td align='center'>{{ ic["mem"]["memuse"] }}</td>
                <td align='center'>{{ ic["mem"]["available"] }}</td>
                {% if ic["mem"]["alarm"] ==1  %}
                <td align='center' style="background-color: #E33E06;" >{{ ic["mem"]["useratio"] }}</td>
                {% else %}
                <td align='center' style="color:chartreuse;" >{{ ic["mem"]["useratio"] }}</td>
                {% endif  %}
                <td>{{ ic["mem"]["checktime"] }}</td>
            </tr>
        </table>

     
        <table cellspacing='1' cellpadding='0'><thead>
            <tr><td  colspan="9" >磁盘</tr>
            <tr>
            <td style='width: 40px;'>节点IP</td>
            <td>文件系统</td>
            <td>类型</td>
            <td>总量</td>
            <td>已用</td>
            <td>可用</td>
            <td>使用率</td>
            <td>挂载点</td>
            <td>检查时间</td>
        </tr>{% for icd in ic["disk"] %} 
    </thead>
			<tr>
                <td style='text-align: center;'>{{ icd["hostip"] }}</td>
                <td>{{ icd["device"] }}</td>
                <td align='center'>{{ icd["fstype"] }}</td>
                <td align='center'>{{ icd["size"] }}</td>
                <td align='center'>{{ icd["used"] }}</td>
                <td align='center'>{{ icd["avail"] }}</td>
                {% if icd["alarm"] ==1  %}
                <td  style="background-color: #E33E06;">{{ icd["useratio"] }}</td>
                {% else %}
                <td style="color: chartreuse;">{{ icd["useratio"] }}</td>
                {% endif  %}

                <td>{{ icd["mountedon"] }}</td>
                <td>{{ icd["checktime"] }}</td>
                
            </tr>
            {% endfor %}
        </table>
        {% endfor %}
    </div>
</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

Mysql数据库结构

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_targetc
-- ----------------------------
DROP TABLE IF EXISTS `tb_targetc`;
CREATE TABLE `tb_targetc`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `cpuuse` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '缓冲区',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'cpu指标' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tb_targetd
-- ----------------------------
DROP TABLE IF EXISTS `tb_targetd`;
CREATE TABLE `tb_targetd`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `device` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件系统',
  `fstype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件系统类型',
  `size` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '容量',
  `used` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '已用',
  `avail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '可用',
  `useratio` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '已用百分比',
  `mountedon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '挂载点',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 64 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '磁盘指标' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tb_targeth
-- ----------------------------
DROP TABLE IF EXISTS `tb_targeth`;
CREATE TABLE `tb_targeth`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `hostip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `hostname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机名',
  `systemos` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作系统',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '应用服务器（1）or数据库服务器（0）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '主机信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tb_targetm
-- ----------------------------
DROP TABLE IF EXISTS `tb_targetm`;
CREATE TABLE `tb_targetm`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `buffers` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '缓冲区',
  `cached` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '缓存',
  `memFree` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '可用',
  `memTotal` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '总量',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '内存指标' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_da
-- ----------------------------
DROP TABLE IF EXISTS `tm_da`;
CREATE TABLE `tm_da`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `device` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件系统',
  `fstype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件系统类型',
  `avail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '可用',
  `mountedon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '挂载点',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_磁盘可用空间' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_ds
-- ----------------------------
DROP TABLE IF EXISTS `tm_ds`;
CREATE TABLE `tm_ds`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `device` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件系统',
  `fstype` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件系统类型',
  `size` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '容量',
  `mountedon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '挂载点',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_磁盘总容量' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_hn
-- ----------------------------
DROP TABLE IF EXISTS `tm_hn`;
CREATE TABLE `tm_hn`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `hostip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `hostname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_主机名' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_ho
-- ----------------------------
DROP TABLE IF EXISTS `tm_ho`;
CREATE TABLE `tm_ho`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `hostip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `systemos` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作系统',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_主机操作系统' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_mb
-- ----------------------------
DROP TABLE IF EXISTS `tm_mb`;
CREATE TABLE `tm_mb`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `buffers` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '缓冲区',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_缓冲区内存' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_mc
-- ----------------------------
DROP TABLE IF EXISTS `tm_mc`;
CREATE TABLE `tm_mc`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `cached` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '缓存',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_缓存区内存' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_mf
-- ----------------------------
DROP TABLE IF EXISTS `tm_mf`;
CREATE TABLE `tm_mf`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `memFree` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '可用',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_可用内存' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for tm_mt
-- ----------------------------
DROP TABLE IF EXISTS `tm_mt`;
CREATE TABLE `tm_mt`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `hostip` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '主机ip',
  `memTotal` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '总量',
  `checktime` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查时间',
  `inserttime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '写入时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '临时表_总内存' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop】

Python 基于 Prometheus 数据生成巡检报告_prometheus巡检

设计思路

读取Prometheus 接口获取数据

Prometheus 数据处理 便于拼接sql

写入mysql 数据库封装函数

读取mysql 巡检数据 返回DataFrame格式

基于jinja2 封装生成html文件函数

jinja2模板

Mysql数据库结构

Prometheus 数据处理便于拼接sql

读取mysql 巡检数据返回DataFrame格式