采用Prometheus+Grafana 监控H3C交换机状态 前言{callout color="#f0ad4e"}采用zabbix监控交换机流量，经常出现获取数据超时。但是通过snmpwalk是可以获取数据。原先通过snmpv3解决了这个问题。最近又出现了，而找不到原因。研究了一下prometheus+grafana通过snmp-exporter获取交换机数据,作为zabbix的补充。{/callout}部署过程docker基本环境docker和docker-compose一键安装脚本docker-compose.yml{message type="success" content="项目启动配置文件"/}version: "3.8" networks: net: driver: bridge services: snmp-exporter: image: prom/snmp-exporter:latest container_name: ly-monitor-snmp-exporter restart: always ports: - "9116:9116" volumes: - "./myapp/snmp/snmp.yml:/etc/snmp_exporter/snmp.yml" networks: - net prometheus: image: prom/prometheus:latest container_name: ly-monitor-prometheus restart: always user: "0" ports: - "9090:9090" volumes: - "./myapp/prometheus/:/prometheus" command: - "--storage.tsdb.retention.time=180d" - "--config.file=/prometheus/prometheus.yml" - "--web.enable-lifecycle" networks: - net depends_on: - snmp-exporter grafana: image: grafana/grafana:latest restart: always container_name: ly-monitor-grafana user: "0" ports: - "3000:3000" environment: - "GF_SECURITY_ADMIN_PASSWORD=xxxxxx" #grafana登录密码，自己设置 - "GF_RENDERING_SERVER_URL=http://renderer:8081/render" - "GF_RENDERING_CALLBACK_URL=http://grafana:3000/" - "GF_LOG_FILTERS=rendering:debug" volumes: - ./data/grafana/:/var/lib/grafana networks: - net depends_on: - prometheus - renderer renderer: image: grafana/grafana-image-renderer:latest container_name: ly-monitor-renderer ports: - "8081:8081" environment: - "ENABLE_METRICS=true" - "RENDERING_MODE=clustered" - "RENDERING_CLUSTERING_MODE=context" - "RENDERING_CLUSTERING_MAX_CONCURRENCY=5" networks: - netsnmp-exporter配置{message type="success" content="配置文件snmp.yml，需要根据交换机mib自己生成。"/}DockerfileFROM golang:latest RUN sed -i "s@http://deb.debian.org@http://mirrors.aliyun.com@g" /etc/apt/sources.list && \ rm -Rf /var/lib/apt/lists/* && \ apt-get update && \ apt-get install -y libsnmp-dev p7zip-full unzip ENV GO111MODULE on ENV GOPROXY https://goproxy.cn RUN go install github.com/prometheus/snmp_exporter/generator@latest WORKDIR "/opt"generator.yml隐藏内容，请前往内页查看详情prometheus.yaml{message type="success" content="Prometheus的主配置文件"/}# my global config global: scrape_interval: 60s # Set the scrape interval to every 15 seconds. Default is every 1 minute. evaluation_interval: 60s # Evaluate rules every 15 seconds. The default is every 1 minute. # scrape_timeout is set to the global default (10s). # Alertmanager configuration alerting: alertmanagers: - static_configs: - targets: # - alertmanager:9093 # Load rules once and periodically evaluate them according to the global 'evaluation_interval'. rule_files: # - "first_rules.yml" # - "second_rules.yml" # A scrape configuration containing exactly one endpoint to scrape: # Here it's Prometheus itself. scrape_configs: # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config. - job_name: 'snmp' scrape_timeout: 30s # 采集超时10s scrape_interval: 1m # 采集频率1m static_configs: - targets: - 10.10.10.62 #交换机ip metrics_path: /snmp params: module: [H3C] #修改成2.4步自己设置的mib名称 relabel_configs: - source_labels: [__address__] target_label: __param_target - source_labels: [__param_target] target_label: instance - target_label: __address__ replacement: snmp-exporter:9116 # 安装snmp_exporter主机的ip和端口号snmp.yml生成#构建镜像 cd src/generator/ && docker build -t generator . && cd - #获取mibs并放到 .src/generator/mibs 目录下 #生成snmp.yaml docker run -it --rm -v /opt/ly-monitor/src/:/opt --privileged generator /bin/bash export MIBDIRS=/opt/generator/mibs cd generator/ && generator generate exit #移动到目标位置 /bin/cp -f src/generator/snmp.yml myapp/snmp/启动服务docker-compose up -d访问http://ip:3000/login {card-default label="效果图" width="95%"}{/card-default}FAQsnmp_exporter获取数据报错{message type="success" content=""IPMI&SSH\xe2\x80—\xe2—Man" is not valid UTF-8"/}{card-default label="抱错截图" width="75%"}{/card-default}{callout color="#f0ad4e"}经过排查是因为 ifAlias 获取项有特殊字符导致的。在generator.yml中去掉此项,重新生成snmp.yaml{/callout}snmpwalk -v 3 -t 2 -u hcuser -l authPriv -a sha -A hcxxxx234 -x des -X hcyyyy234 192.168.4.62 ifAlias{card-default label="结果" width="75%"}{/card-default}

grafana之mysql数据源设置变量 前言{callout color="#f0ad4e"}grafana配置完成后，经常在mysql的数据库内读取数据。有时候采用变量的形式，通过一下方法设置变量。{/callout}添加变量{message type="success" content="Dashborad -> settings -> Variables"/}DropCountsSELECT 'DropCounts' AS __text, ROUND((${__to}+1000-${__from})/6000000) AS __value FROM report limit 1{card-default label="添加变量" width="75%"}{/card-default}OUT_ITEMSSELECT 'OUT_ITEMS' AS __text, '(401,402, 467,468)' as __value{card-default label="OUT_ITEMS" width="75%"}{/card-default}调用变量变量调用变量SELECT 'O_OUTRATE' AS __text, SUM(value) as __value FROM (SELECT date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time, value FROM history WHERE itemid IN ${OUT_ITEMS} AND $__unixEpochFilter(clock) ORDER BY time ) AS temp GROUP BY time ORDER by __value DESC LIMIT ${DROPCOUNTS},1{card-default label="配置说明" width="75%"}{/card-default}Time series 调用SELECT unix_timestamp(time) as time_sec, ${O_OUTRATE} as value, 'Out端口流量95值' as metric FROM (SELECT date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time FROM history WHERE itemid IN ${OUT_ITEMS} AND $__unixEpochFilter(clock) ORDER BY time) AS temp GROUP BY time;{card-default label="效果" width="75%"}{/card-default}

centos8下grafana的安装与使用 {card-default label="grafana" width="75%"}{/card-default}前言{callout color="#f0ad4e"}centos8 已经停止维护了，不建议线上环境使用。但某些特定环境下，比如线上服务器存在业务，而系统一直未更新。需要安装一套grafana，用于数据展示。特此记录，在centos8下,grafana安装与使用过程。{/callout}安装yum -y install urw-fonts initscripts wget https://dl.grafana.com/oss/release/grafana-8.0.6-1.x86_64.rpm yum install -y grafana-8.0.6-1.x86_64.rpm 启动systemctl enable --now grafana-server绘图插件{message type="success" content="Grafana Image Renderer"/}# 安装支持库 yum install nss libatk atk libatk-bridge-2.0.so.0 at-spi2-atk cups-libs libdrm libXcomposite libXdamage mesa-libgbm libpango-1.0.so.0 pango alsa-lib libxshmfence #安装 grafana-cli plugins install grafana-image-renderer #重启服务 systemctl restart grafana-serverFAQ中文字体不显示#修改grafana配置文件 vim /etc/grafana/grafana.ini [plugin.grafana-image-renderer] rendering_timezone = Asia/Shanghai rendering_language = zh yum -y install fontconfig mkdir /usr/share/fonts/chinese && cd /usr/share/fonts/chinese #将SIMSUM上传到此目录 wget http://dl.itbunan.xyz/SIMSUN.TTC #查看字体是否生效 fc-list :lang=zh

grafana的Dashboard面板添加阈值报警 前言{callout color="#f0ad4e"}grafana使用一段时间以后，又有了新的需求。当Dashboard面板展示的流量小于某个阈值时，立刻通知相关人员。grafana的告警触发以panel为基础，即每个panel单独配置告警信息，包括告警规则、触发条件、告警通知通道及内容等。配置比较简单，容易上手。特此记录一下。{/callout}告警通道{message type="success" content="Alerting -> Notification channels"/}邮件修改grafana.ini配置文件，重启服务[smtp] enabled = true host = smtp.163.com:465 user = ******@163.com password =********* #授权码 from_address = *******@163.com from_name = grafana系统{card-default label="邮件报警通道" width="60%"}{/card-default}钉钉通过钉钉后台申请webhook{card-default label="钉钉报警通道" width="75%"}{/card-default}Cloud Alert登录cloud Alert，添加grafana应用。然后按照官方文档添加即可。{card-default label="添加账号" width="75%"}{/card-default}告警规则{message type="success" content="grafana报警规则，基于pannel配置。不支持模板，所以生成单独的dashboard专门用于报警。"/}{card-default label="报警规则" width="75%"}{/card-default}可用行测试{callout color="#f0ad4e"}通过改变相应的阈值条件，使条件触发。然后查看是否收到报警，如果可以收到。说明配置可用，然后将阈值改成需要的值即可。{/callout}

数据可视化grafana面板制作，展示订单统计信息 前言{callout color="#f0ad4e"}前面写过如何部署基于docker的grafana做数据可视化展示，因为常用数据库是mysql,所以数据源以mysql来制作。再使用过程中，有了新的需求。继续补充面版制作方法。{/callout}docker下运行grafana和grafana Image Renderer制作面板按规格统计计成功订单数{callout color="#f0ad4e"}规格名称不是很标准，需要对查询的字段进行处理注意结果的时间排序，开始因为倒序，一直报错{/callout}{card-default label="按天统计" width="80%"}{/card-default}SELECT unix_timestamp(time) as time_sec, count(format_name) as value, format_name as metric FROM ( select date_format(b.create_time, '%Y-%m-%d') as time, substring_index(substring_index(c.format_name, '(', 1), '（', 1) as format_name from order_record b join (select '10000' as format_id, '自定义' as format_name union all select format_id,format_name from photo_format ) c on b.format_id = c.format_id where $__timeFilter(b.create_time) and b.order_status = 10 ) AS temp GROUP BY time, format_name order by time, format_name desc ;{card-default label="按小时统计" width="80%"}{/card-default}SELECT unix_timestamp(time) as time_sec, count(format_name) as value, format_name as metric FROM ( select date_format(b.create_time, '%Y-%m-%d %H') as time, c.format_name as format_name from order_record b join (select '10000' as format_id, '自定义' as format_name union all select format_id,format_name from photo_format ) c on b.format_id = c.format_id where $__timeFilter(b.create_time) and b.order_status = 10 ) AS temp GROUP BY time, format_name order by time, format_name desc ;

grafana基于mysql数据源的数据可视化展示 前言Grafana是一个开源的，拥有丰富dashboard和图表编辑的指标分析平台，支持多种数据源。下面介绍一些基于mysql数据源的面板配置方法。端口流量图{card-default label="结果图" width="75%"}{/card-default}数据来源（zabbix监控采集）表结构MariaDB [zreport]> desc history_uint; +--------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+ | itemid | bigint(20) unsigned | NO | MUL | NULL | | | clock | int(11) | NO | | 0 | | | value | bigint(20) unsigned | NO | | 0 | | | ns | int(11) | NO | | 0 | |rows in set (0.001 sec)数据样本MariaDB [zreport]> select * from history_uint order by clock desc limit 10; +--------+------------+-----------+-----------+ | itemid | clock | value | ns | +--------+------------+-----------+-----------+ | 36336 | 1639155600 | 122559104 | 354559595 | | 36505 | 1639155600 | 18404320 | 462215215 | | 36506 | 1639155600 | 20577192 | 932727023 | | 36513 | 1639155600 | 15658232 | 932727023 | | 36337 | 1639155600 | 116371000 | 778553999 | | 36553 | 1639155600 | 597137480 | 932727023 | | 36385 | 1639155600 | 174508568 | 827251666 | | 36552 | 1639155600 | 628670928 | 462215215 | | 36504 | 1639155600 | 15002368 | 462215215 | | 36384 | 1639155600 | 88 | 223615600 |rows in set (1.025 sec)95计费#in端口ID元组 IN_ITEMS SELECT 'IN_ITEMS' AS __text, '(36384, 36385)' as __value; #95计费去掉的点数 SELECT 'DROPCOUNTS' AS __text, ROUND((${__to}+1000-${__from})/6000000) AS __value; #原始数据表名TABLE_R SELECT 'TABLE_R' AS __text, 'history_uint' AS __value; #95计费In端口速率(原始数据) INRATE SELECT 'INRATE' AS __text, (SUM(value)/1000/1000/1000) as __value FROM (SELECT date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time, value FROM ${TABLE_R} WHERE itemid IN ${IN_ITEMS} AND $__unixEpochFilter(clock) ORDER BY time) AS temp GROUP BY time ORDER by __value DESC LIMIT ${DROPCOUNTS},1 #流量表 SELECT unix_timestamp(time) as time_sec, (SUM(value)/1000/1000/1000) as value, 'In端口流量汇总(优化数据)' as metric FROM (SELECT date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time , value FROM ${TABLE_O} WHERE itemid IN ${IN_ITEMS} AND $__unixEpochFilter(clock) ORDER BY time) AS temp GROUP BY time;订单统计分析面板{card-default label="订单统计分析面板" width="75%"}{/card-default}数据源(mysql数据库)order_record表MySQL [yaoguai]> select order_status,price,create_time,format_id,order_type,user_id from order_record order by id desc limit 10; +--------------+-------+---------------------+-----------+------------+---------+ | order_status | price | create_time | format_id | order_type | user_id | +--------------+-------+---------------------+-----------+------------+---------+ | 5 | 290 | 2022-03-15 19:21:44 | 42 | 0 | 1087835 | | 10 | 290 | 2022-03-15 19:21:39 | 42 | 0 | 1087836 | | 5 | 290 | 2022-03-15 19:21:03 | 42 | 0 | 1087833 | | 5 | 290 | 2022-03-15 19:20:54 | 4 | 0 | 1087834 | | 5 | 290 | 2022-03-15 19:19:57 | 42 | 0 | 1087824 | | 5 | 290 | 2022-03-15 19:19:14 | 42 | 0 | 1087830 | | 5 | 290 | 2022-03-15 19:18:52 | 42 | 0 | 1087831 | | 10 | 290 | 2022-03-15 19:16:11 | 42 | 0 | 1087810 | | 5 | 290 | 2022-03-15 19:15:50 | 42 | 0 | 1087829 | | 5 | 290 | 2022-03-15 19:14:55 | 42 | 0 | 1087828 |rows in set (0.00 sec)photo_formatMySQL [yaoguai]> select format_id,format_name from photo_format limit 10; +-----------+----------------------+ | format_id | format_name | +-----------+----------------------+ | 1 | 一寸 | | 2 | 小一寸 | | 3 | 大一寸 | | 4 | 二寸 | | 5 | 小二寸 | | 6 | 大二寸 | | 7 | 美国签证 | | 8 | 日本签证 | | 9 | 越南签证 | | 10 | 身份证(无回执) |rows in set (0.00 sec)user表MySQL [yaoguai]> select id,mini_kind from user order by id desc limit 10; +---------+-----------+ | id | mini_kind | +---------+-----------+ | 1087840 | 1 | | 1087839 | 1 | | 1087838 | 1 | | 1087837 | 1 | | 1087836 | 1 | | 1087835 | 1 | | 1087834 | 1 | | 1087833 | 10 | | 1087832 | 1 | | 1087831 | 1 |rows in set (0.00 sec)Stat 面板#金额 SELECT now() as time_sec, sum(case a.order_status when '10' then a.num else 0 end) as '成功订单金额(单位:元)', sum(case a.order_status when '5' then a.num else 0 end) as '失败订单金额(单位:元)' from ( select order_status ,sum(price)/100 as num from order_record where $__timeFilter(create_time) GROUP BY order_status) a #个数 SELECT now() as time_sec, sum(case a.order_status when '10' then a.num else 0 end) as '成功订单数(单位:个)', sum(case a.order_status when '5' then a.num else 0 end) as '失败订单数(单位:个)' from ( select order_status , count(order_status) as num from order_record where $__timeFilter(create_time) GROUP BY order_status) aBar gauge 面板select c.format_name as format_name, count(c.format_name) as num from order_record b join photo_format c on b.format_id = c.format_id where $__timeFilter(b.create_time) and b.order_status = 10 GROUP BY c.format_name order by count(c.format_name) desc ;Pie chart 面板SELECT now() as time_sec, sum(case a.order_type when '0' then a.num else 0 end) as '未换装电子版', sum(case a.order_type when '5' then a.num else 0 end) as '未换装打印版', sum(case a.order_type when '10' then a.num else 0 end) as '已换装电子版', sum(case a.order_type when '15' then a.num else 0 end) as '已换装打印版' from ( select order_type ,sum(price)/100 as num from order_record where $__timeFilter(create_time) and order_status = 10 GROUP BY order_type) aTime series 面板SELECT unix_timestamp(time) as time_sec, sum(value) as value, '所有订单' as metric FROM ( select date_format(b.create_time, '%Y-%m-%d %H') as time , 1 as value from order_record b where $__timeFilter(b.create_time) and b.order_status = 10 GROUP BY b.create_time) AS temp GROUP BY time;Table 面板SELECT ( CASE WHEN `mini_kind`=0 THEN 'web-官网' WHEN `mini_kind`=1 THEN '微信-APP' WHEN `mini_kind`=10 THEN '支付宝-APP' WHEN `mini_kind`=12 THEN '支付宝-APP2' ELSE NULL END ) '用户类型', user_id as '用户ID', count(user_id) as '订单数', sum(price)/100 as '总金额(单位:元)' FROM (SELECT c.mini_kind as mini_kind, b.user_id as user_id, b.price as price FROM order_record b join user c on b.user_id = c.id WHERE $__timeFilter(b.create_time) AND b.order_status = 10 ORDER BY b.user_id) AS a GROUP BY user_id order by sum(price) desc limit 20 ;

docker下运行grafana和grafana Image Renderer {card-default label="grafana首页" width="80%"}{/card-default}前言{message type="success" content=" Grafana是一个开源的，拥有丰富dashboard和图表编辑的指标分析平台，支持多种数据源。"/}{callout color="#f0ad4e"}DashBoad:仪表盘,以各种图形的方式来展示从datasource拿到的数据Row:行,DashBoad的基本组成单元，一个DashBoad可以包含多个row。一个row可以展示一种信息或者多种信息的组合Panel:面板,实际上就是row展示信息的方式，支持表格，列表，热图等多种形式。Query Editor:查询编辑器，用来指定获取那一部分的数据，类似于sql 查询语句。{/callout}版本信息{callout color="#f0ad4e"}系统版本: centos7.9软件版本: grafana-8.4.1 {/callout}资源下载代码下载地址：隐藏内容，请前往内页查看详情安装grafana安装docker和docker-composedocker和docker-compose一键安装脚本编辑docker-compose文件隐藏内容，请前往内页查看详情编辑grafana.ini配置在配置文件最后配置[rendering] server_url = http://xx.xx.xx.xx:3001/render callback_url = http://xx.xx.xx.xx:3000 [plugin.grafana-image-renderer] rendering_timezone = Asia/Shanghai rendering_language = zh启动grafanacd grafana && docker-compose up -d安装插件docker exec -it grafana-server bash grafana-cli plugins install grafana-piechart-panel邮件日报　利用python脚本调用grafana-image-renderer每天生成统计日报，邮件发给相关人员编辑docker-compose配置文件隐藏内容，请前往内页查看详情python核心脚本下载指定的dashboarddef download_dashboard(): # 组装url，跑代码之前现在浏览器试试 dbuid = GRAFANA['dbuid-tuluban'] grafana_server = "http://{}:{}".format(GRAFANA['server'], GRAFANA['port']) url = grafana_server + '/render/d/' + dbuid + '?from=' + str(sTime*1000) + '&to=' + str(eTime*1000) + '&var-datasource=MySQL&width=' + GRAFANA['width'] + '&height=' + GRAFANA['height'] + '&tz=Asia/Shanghai' #用管理员去Grafana生成API Key header = {"Authorization": GRAFANA['apikey']} request = urllib.request.Request(url,headers=header) try: # 访问并下载面板图 response = urllib.request.urlopen(request) time_now = int(Time.time()) time_local = Time.localtime(time_now) dt = Time.strftime("%Y-%m-%d",time_local) img_name = "bimg"+dt+".png" filename = '/tmp/' + img_name # print(response.getcode()) if (int(response.getcode()) == 200): with open(filename, "wb") as f: f.write(response.read()) return filename else: return "failed" except Exception as ex: print(ex) return "failed"发送邮件python脚本def send_mail(img_name): msgRoot = MIMEMultipart('related') msgRoot['Subject'] = '订单统计日报-{}'.format(Yesterday) msgRoot['From'] = formataddr(["订单统计", MAIL['sender']]) msgRoot['To'] = ",".join(TOEMAIL) # 发给多人 #添加正文 content = MIMEText('<html><head><style>#string{text-align:center;font-size:25px;}</style><div id="string">统计结果:<div></head><body><img src="cid:image1" alt="image1"></body></html>','html','utf-8') msgRoot.attach(content) # 获取图片 fp = open(img_name, 'rb') msgImage = MIMEImage(fp.read()) fp.close() msgImage.add_header('Content-ID', 'image1') # 该id和html中的img src对应 msgRoot.attach(msgImage) smtp = smtplib.SMTP_SSL(MAIL['smtp']) smtp.login(MAIL['username'], MAIL['password']) smtp.sendmail(MAIL['sender'], TOEMAIL, msgRoot.as_string()) smtp.quit()计划任务部署计划任务,每天9:00自动发送报告到邮箱(宿主机配置)0 9 * * * docker exec -i pyreport_pyreport_1 /bin/bash -c 'cd /myapp/ && sh start_report.sh'FAQ时区问题可以在 Configuration -> Preferences -> TimeZone 修改为UTC