评论

收藏

[MySQL] zabbix二次开发之从mysql取值在运维平台js图表展现

数据库 数据库 发布于:2021-07-04 10:30 | 阅读数:312 | 评论:0

  前沿:
       集群控制平台已经要慢慢的灰度上线了,出问题的时候,才找点bug,时间有点空闲。正好看了下zabbix的数据库,产生了自己想做一套能更好的展现zabbix的页面。
  

  更多内容请到我的个人的博客站点,blog.xiaorui.cc
  

      zabbix的强点大家都感觉或者用出感觉了,确实很是方便。曾经的我,跟着公司的团队开发过一套不小的监控系统,看过我博客的人应该留意到那个用redis mongodb,后期改用golang,0mq支撑业务的监控平台。
       其实对比起来,我个人还是喜欢zabbix,虽然和团队一起很爽,爽也只是设计一套监控平台框框,里面的东西填写起来那可真是吐血。。。 随着看zabbix的文档深入,越发的觉得,zabbix做的真不错。   但是大家有没有觉得,对于不熟系zabbix的人,让他一下在看zabbix的数据,绝对是有点发蒙,他数据展现的有点不清不白的,当然有过配置和运维经验的人,一眼就能看出哪是哪里。

  

  旁边cdn部门在讨论东西的时候,我发现他们的那个报表不直观,而且不高端。。。
  

  

      对于zabbix,我用的最多的反而是api,在上个公司做了一个简练的针对zabbix的api接口,可以迅速的增删改查主机,虽然时不时的抽风,但是那绝对我自己对zabbix理解的问题。
  

  我期待的样子
  1.   权限用公司的权限管理系统,集中单点登录。
  2.   登录后输入ip地址,可以看到这个ip地址所属的情况,比如内存,cpu,流量等等。
  3.   根据业务线做好分组,这样dba组的人,一登录就可以看到dba所属的服务器状况。
  4.   数据库采用zabbix的从库,这样就能很好的免除了压力。
  

  

  当然写完前三个大需求就成一个项目了,目标定的是很远,但是精力是有限。
  我们可以先来个简单了,从数据库里面取出item监控项的时间和值,然后用前端的图表进行画图。
  

  
  我们首先要从mysql里面取值,Zabbix中存储历史数据的表是以history开头的,目前zabbix 2.06 中主要有一下几张表。
  >>>>>history              Numeric(float)
   >>>>>history_log           -log
   >>>>>history_str           -Character
   >>>>>history_text          -text
   >>>>>history_uint          -Numeric(unsigned)
  

  通过数据库可以看到这个值的 item 时间 value ns
   DSC0000.jpg
  

  我们看下zabbix的针对eth0监控的图表
   DSC0001.jpg
  

  流量的统计,这里只是简单的把数据塞到了jinja2的模板里面,然后用highcharts画图的 。
DSC0002.jpg
  原文:http://rfyiamcool.blog.51cto.com/1030776/1385639
  

  
var d = new Date("{{ showdotime }}");
   $('#container').highcharts({
      chart: {
        zoomType: 'x',
        spacingRight: 20
      },
      title: {
        text: 'eth0 流量'
      },
      subtitle: {
        text: document.ontouchstart === undefined ?
          '滑动显示更多' :
          ''
      },
      xAxis: {
        type: 'datetime',
        maxZoom: 10 * 1000, // fourteen days
        title: {
          text: null
        }
      },
      yAxis: {
        title: {
          text: '进来的流量'
        }
      },
      tooltip: {
        shared: true
      },
      legend: {
        enabled: false
      },
      plotOptions: {
        area: {
          fillColor: {
            linearGradient: { x1: 0, y1: 0, x2: 0, y2: 1},
            stops: [
              [0, Highcharts.getOptions().colors[0]],
              [1, Highcharts.Color(Highcharts.getOptions().colors[0]).setOpacity(0).get('rgba')]
            ]
          },
          lineWidth: 1,
          marker: {
            enabled: false
          },
          shadow: false,
          states: {
            hover: {
              lineWidth: 1
            }
          },
          threshold: null
        }
      },
                                                                                                                                                                             
      series: [{
        type: 'area',
        name: 'Byte',
      pointInterval: 10 * 1000,
      pointStart: d.getTime(),
        data: [1153,946,679,112,112,250,112,7631,2818,244,7781,2683,442197,806342,5191431,728543,418013,502684,127900,493,1151125,5573336,3348358,5213414,4482643,3575359,2698665,844558,4204725,4311682,4776592,3762035,5018876,4599467,1529725,4576385,3654153,3515204,2361854,815,4903,3947,2184,128,145,6102,13607,31939,3052,47821,97577,10760,50366,1046,254,1753,2661,581269,4299933,4113029,4267878,781296,885,3232,1430,2472,61669,103854,170090,112507,111455,162,322,112,2927,437,3116,13580,1125,3062,458,738,2961,4406853,3084219,945726,4712167,3476555,4042300,4646039,2964012,1728972,112,112,256,112,112,262,112,112,256,112,112,284,118,139,256,194,112,256,139,118,256,139,112,254,112,118,262,112,112,256,234,139,256,250,256,1711626,4722630,3606945,5026923,211202,649,408,118,521,462,145,675,418,265,1464972,3963484,4375091,942586,3019,127430,112220,37612,104421,109967,98548,58317,77624,172,172,481,171,172,316,31886,105022,57734,95642,86328,137027,65218,80590,48013,51634,67985,99618,84169,93314,36073,116393,79163,150923,89319,89091,133733,90220,94252,121016,144024,137263,127760,163344,122370,99454,45178,71369,90175,136685,52301,141410,137028,27751,2647301,4415498,3134557,2847034,4297467,3569987,118552,111,111,484,7941,287668,889281,400112,233295,2083,111,574,262,111,680,262,205,410,256,117,112,256,118,176,833,640,1004,256,433,703,798,377,698,351,300,112,262,150,437,428,477,128,461,244,261,289,112,118,262,112,112,262,118,130,268,112,112,262,112,112,256,118,277,570,112,112,262,431,167,351,228,238,1059,224,118,262,219,112,256,112,117,289,151,110,345,278,189,454,112,118,262,111,124,307,112,112,268,111,112,256,112,112,256,117,112,262,112,146,421,200,118,256,199,359,262,112,568,423,112,112,262,112,112,262,125,112,262,112,112,256,112,118,262,112,112,262,175,272,262,112,112,384,112,257,345,118,130,271,136,112,262,112,118,262,112,150,268,124,112,262,106,112,268,673,112,262,118,112,262,118,112,256,124,279,529,291,219,505,344,380,617,477,112,577,448,163,284,118,112,289,112,211,644,112,124,385,526,112,409,471,112,262,180,341,450,228,326,224,178,112,279,534,339,196,188,536,468,210,184,196,271,300,252,178,112,196,172,281,582,118,112,470,112,118,262,112,118,256,156,383,472,664,112,297,180,151,565,211,112,256,231,272,753,211,112,256,118,118,262,112,112,256,284,537,461,112,112,139,256,112,337,798,672,614,549,112,112,256,131,112,256,112,112,256,112,118,256,111,112,250,112,112,256,112,112,250,834,394,2934,155,111,250,124,0,250,111,117,250,28774,45960,2377859,3852873,4289316,4402594,4821490,3532899,4404084,3681347,4537306,4040884,304,236,131,112,265,185,357,508,118,112,306,118,112,262,118,118,300,454,449,876,557,205,279,516,195,256,112,421,662,200,112,250,112,112,250,112,118,250,112,112,250,112,112,256,112,112,250,112,112,250,118,136,274,112,112,250,112,118,250,194,266,761,321,287,444,452,303,333,112,112,250,118,112,250,118,214,250,112,118,346,118,112,250,112,112,256,112,112,256,112,112,250,118,112,250,118,112,250,112,118,284,112,118,250,112,112,284,112,112,256,112,118,250,112,145,250,194,112,250,112,118,250,112,112,250,221,240,588,421,1029,327,156,218,250,134,211,267,112,112,250,112,118,250,112,112,250,112,112,256,271,134,250,112,112,250,118,112,250,112,112,250,112,118,256,245,128,250,196,121,256,118,531,946,1259,453,4115,4691,527,285,9,172,2966,393,1496237,4133810,3555137,4391843,4734147,2365335,4309393,4816593,2865318,5075970,3642291,3712991,4050442,726911,782292,161,300,488,327,244,256,134,647,660,538,785,803,338,306,256,112,118,256,112,112,312,211,326,776,537,360,839,451,387,554,118,214,544,360,291,311,273,112,112,256,112,202,282,366,359,577,458,198,391,543,404,422,112,228,312,289,427,755,112,112,390,360,118,256,139,112,256,195,553,407,112,112,256,112,112,256,118,112,256,112,112,256,118,118,262,112,112,256,118,112,262,112,112,256,136,154,256,118,0,256,118,112,112,256,118,112,262,112,194,256,139,118,285,124,118,280,124,112,262,124,145,256,112,112,256,145,118,256,118,112,256,112,124,256,112,118,256,112,112,268,133,112,262,128,112,284,118,112,256,118,112,256,112,118,256,139,213,256,112,140,2694,961,972,268,256,111,111,117,194,112,127,130,111,130,139,111,329,419,627,494,294,333,433,163,3232,257,256,284,713,112,111,111,145,261,775,1253,433,403,616,382,503,133,357,876,601,742,671,553,273,814,261,128,112,112,112,145,1886446,4459752,4025218,4333118,3649335,4275277,3551729,3454440,3240775,4322114,3254331,3932662,4241105,2599449,4153879,2718555,529,208,384,1068,688,313,112,564,433,112,179,112,118,283,456,2612894,4136542,4381146,4788558,3434215,3880547,5053044,1464799,4943006
        ]
      }]
    });
  

  

  我们再来看看zabbix的几个主要的表:
  一看就懂,在咱们创建主机的那几个选项。。。要留意下hostid,这个hostid在其他表做了数据字段的关联。
  
mysql> desc hosts;
+--------------------+---------------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| hostid       | bigint(20) unsigned | NO   | PRI | NULL  |     |
| proxy_hostid     | bigint(20) unsigned | YES  | MUL | NULL  |     |
| host         | varchar(64)     | NO   | MUL |     |     |
| status       | int(11)       | NO   | MUL | 0     |     |
| disable_until    | int(11)       | NO   |   | 0     |     |
| error        | varchar(128)    | NO   |   |     |     |
| available      | int(11)       | NO   |   | 0     |     |
| errors_from    | int(11)       | NO   |   | 0     |     |
| lastaccess     | int(11)       | NO   |   | 0     |     |
| ipmi_authtype    | int(11)       | NO   |   | 0     |     |
| ipmi_privilege   | int(11)       | NO   |   | 2     |     |
| ipmi_username    | varchar(16)     | NO   |   |     |     |
| ipmi_password    | varchar(20)     | NO   |   |     |     |
| ipmi_disable_until | int(11)       | NO   |   | 0     |     |
| ipmi_available   | int(11)       | NO   |   | 0     |     |
| snmp_disable_until | int(11)       | NO   |   | 0     |     |
| snmp_available   | int(11)       | NO   |   | 0     |     |
| maintenanceid    | bigint(20) unsigned | YES  | MUL | NULL  |     |
| maintenance_status | int(11)       | NO   |   | 0     |     |
| maintenance_type   | int(11)       | NO   |   | 0     |     |
| maintenance_from   | int(11)       | NO   |   | 0     |     |
| ipmi_errors_from   | int(11)       | NO   |   | 0     |     |
| snmp_errors_from   | int(11)       | NO   |   | 0     |     |
| ipmi_error     | varchar(128)    | NO   |   |     |     |
| snmp_error     | varchar(128)    | NO   |   |     |     |
| jmx_disable_until  | int(11)       | NO   |   | 0     |     |
| jmx_available    | int(11)       | NO   |   | 0     |     |
| jmx_errors_from  | int(11)       | NO   |   | 0     |     |
| jmx_error      | varchar(128)    | NO   |   |     |     |
| name         | varchar(64)     | NO   | MUL |     |     |
+--------------------+---------------------+------+-----+---------+-------+
30 rows in set (0.00 sec)
mysql>
  

  查询下: 可以得到我的hostid是 10085, 你为啥不是10086,不然以后再也不为花费发愁了 !!!
*************************** 25. row ***************************
      hostid: 10085
    proxy_hostid: NULL
        host: 192.168.1.120
      status: 0
   disable_until: 0
       error:
     available: 0
     errors_from: 0
    lastaccess: 0
   ipmi_authtype: -1
  ipmi_privilege: 2
   ipmi_username:
   ipmi_password:
ipmi_disable_until: 0
  ipmi_available: 0
snmp_disable_until: 0
  snmp_available: 0
   maintenanceid: NULL
maintenance_status: 0
  maintenance_type: 0
  maintenance_from: 0
  ipmi_errors_from: 0
  snmp_errors_from: 0
    ipmi_error:
    snmp_error:
 jmx_disable_until: 0
   jmx_available: 0
   jmx_errors_from: 0
     jmx_error:
        name: 192.168.1.120
  

  

  今晚就先做这出根据数据做图,明天再搞搞别的。
  再来说说掌控主机监控项的表
mysql> desc items;
+-----------------------+---------------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| itemid        | bigint(20) unsigned | NO   | PRI | NULL  |     |
| type          | int(11)       | NO   |   | 0     |     |
| snmp_community    | varchar(64)     | NO   |   |     |     |
| snmp_oid        | varchar(255)    | NO   |   |     |     |
| hostid        | bigint(20) unsigned | NO   | MUL | NULL  |     |
| name          | varchar(255)    | NO   |   |     |     |
| key_          | varchar(255)    | NO   |   |     |     |
| delay         | int(11)       | NO   |   | 0     |     |
| history         | int(11)       | NO   |   | 90    |     |
| trends        | int(11)       | NO   |   | 365   |     |
| lastvalue       | varchar(255)    | YES  |   | NULL  |     |
| lastclock       | int(11)       | YES  |   | NULL  |     |
| prevvalue       | varchar(255)    | YES  |   | NULL  |     |
| status        | int(11)       | NO   | MUL | 0     |     |
| value_type      | int(11)       | NO   |   | 0     |     |
| trapper_hosts     | varchar(255)    | NO   |   |     |     |
| units         | varchar(255)    | NO   |   |     |     |
| multiplier      | int(11)       | NO   |   | 0     |     |
| delta         | int(11)       | NO   |   | 0     |     |
| prevorgvalue      | varchar(255)    | YES  |   | NULL  |     |
| snmpv3_securityname   | varchar(64)     | NO   |   |     |     |
| snmpv3_securitylevel  | int(11)       | NO   |   | 0     |     |
| snmpv3_authpassphrase | varchar(64)     | NO   |   |     |     |
| snmpv3_privpassphrase | varchar(64)     | NO   |   |     |     |
| formula         | varchar(255)    | NO   |   | 1     |     |
| error         | varchar(128)    | NO   |   |     |     |
| lastlogsize       | bigint(20) unsigned | NO   |   | 0     |     |
| logtimefmt      | varchar(64)     | NO   |   |     |     |
| templateid      | bigint(20) unsigned | YES  | MUL | NULL  |     |
| valuemapid      | bigint(20) unsigned | YES  | MUL | NULL  |     |
| delay_flex      | varchar(255)    | NO   |   |     |     |
| params        | text        | NO   |   | NULL  |     |
| ipmi_sensor       | varchar(128)    | NO   |   |     |     |
| data_type       | int(11)       | NO   |   | 0     |     |
| authtype        | int(11)       | NO   |   | 0     |     |
| username        | varchar(64)     | NO   |   |     |     |
| password        | varchar(64)     | NO   |   |     |     |
| publickey       | varchar(64)     | NO   |   |     |     |
| privatekey      | varchar(64)     | NO   |   |     |     |
| mtime         | int(11)       | NO   |   | 0     |     |
| lastns        | int(11)       | YES  |   | NULL  |     |
| flags         | int(11)       | NO   |   | 0     |     |
| filter        | varchar(255)    | NO   |   |     |     |
| interfaceid       | bigint(20) unsigned | YES  | MUL | NULL  |     |
| port          | varchar(64)     | NO   |   |     |     |
| description       | text        | NO   |   | NULL  |     |
| inventory_link    | int(11)       | NO   |   | 0     |     |
| lifetime        | varchar(64)     | NO   |   | 30    |     |
+-----------------------+---------------------+------+-----+---------+-------+
  

  咱们来定位下,我的那个eth0 。
  原文:http://rfyiamcool.blog.51cto.com/1030776/1385639
  

  
mysql> select * from  items where hostid=10084 and name="eth0" \G;
*************************** 1. row ***************************
         itemid: 23329
         type: 0
     snmp_community:
       snmp_oid:
         hostid: 10084
         name: eth0
         key_: net.if.in[eth0,bytes]
        delay: 10
        history: 90
         trends: 365
      lastvalue: 647
      lastclock: 1395938649
      prevvalue: 648
         status: 0
       value_type: 3
    trapper_hosts:
        units:
       multiplier: 0
        delta: 1
     prevorgvalue: 3687436151
  snmpv3_securityname:
 snmpv3_securitylevel: 0
snmpv3_authpassphrase:
snmpv3_privpassphrase:
        formula: 1
        error:
      lastlogsize: 0
       logtimefmt:
       templateid: NULL
       valuemapid: NULL
       delay_flex:
         params:
      ipmi_sensor:
      data_type: 0
       authtype: 0
       username:
       password:
      publickey:
       privatekey:
        mtime: 0
         lastns: 101190560
        flags: 0
         filter:
      interfaceid: 1
         port:
      description:
     inventory_link: 0
       lifetime: 30
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
  

  

  

  简单说下他的账号,他默认是用md5入库的,可以让新老用户都登录到平台中。
  

  继续继续 !

  
关注下面的标签,发现更多相似文章