zabbix+grafana+ansible

目标:
实现 ansible hosts 文件里的组 自动创建
实现 ansible hosts 文件里 组里的hosts 自动添加到zabbix 组
zabbix 对接 grafana 自动生成 监控表 (sql语句实现)

版本:
zabbix 4.2
grafana 6.7.1

1. hosts 示例:

#######################

[kaifa-717]
192.168.196.89
192.168.196.162
192.168.196.88
192.168.196.164
192.168.196.87

[kaifa-718]
192.168.196.97
192.168.196.93
192.168.196.92
192.168.196.91
192.168.196.160
192.168.196.171
192.168.196.159
################################

2. python 脚本 自动生成 zabbix组, 以及添加hosts 进 组

# -*- coding: utf-8 -*-
from zabbix_api import ZabbixAPI
import sys,os,re

server = "http://192.168.216.38/"
username = "admin"
password = "passwd"
zapi = ZabbixAPI(server=server, path="", log_level=0)
zapi.login(username, password)

EPIC_templateid = zapi.template.get({"filter":{"host":"Linux OS EPIC"}})[0]['templateid']
EPIC_group_id = zapi.hostgroup.get({"filter":{"name":"EPIC_servers"}})[0]['groupid']

all_zabbix_groups_list = []
all_zabbix_group_info = zapi.hostgroup.get({"output":  "extend"})
for i in  range(len(all_zabbix_group_info)):
  all_zabbix_groups_list.append(all_zabbix_group_info[i]['name'])
#print(all_zabbix_groups_list)
#生成zabbix所有组列表

all_zabbix_allgroup_hosts_list = []
all_zabbix_allhosts_info = zapi.host.get({"output": "extend",})
for i in range(len(all_zabbix_allhosts_info)):
  all_zabbix_allgroup_hosts_list.append(all_zabbix_allhosts_info[i]['host'])
#生成zabbix所有host列表

#生成 dict
def list_group(inventory):
    global GW_dict
    GW_dict = {}
    if os.path.exists(inventory):
       out = '已拥有' + inventory + '文件'
       print(out)
    else:
       out = '不存在' + inventory + '文件'
       print(out)
       sys.exit(1)
    fo = open('all_groups','r')
    for group in fo.readlines():
      group = group.replace('\n','')
      os.environ['inventory'] = str(inventory)
      os.environ['group'] = str(group)
      hosts = os.popen('ansible $group -i $inventory --list-host 2> /dev/null |grep -Eo "[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}" ','r').read()
      hosts = hosts.replace('\n',' ')
      hosts1 = re.split(r'([\s])\s*', hosts)
      hosts1 = [i for i in hosts1 if i != '']
      hosts1 = [i for i in hosts1 if i != ' ']
      GW_dict[group] = hosts1
#    print(GW_dict)
#把ansible hosts文件 生成如下格式 字典
#{kaifa-718 : [192.168.196.97,192.168.196.98,192.168.196.99],kaifa-719 : [192.168.196.108,192.168.106.98,192.168.196.109]}


def get_host_id(host_name): #定义获取hostid函数
    return zapi.host.get({"filter": {"host": host_name}})[0]['hostid']

def get_group_id(group_name):#定义获取groupid函数
    return zapi.hostgroup.get({"filter":{"name":group_name}})[0]['groupid']

def get_group_hosts_list(group_name):# 定义获取zabbix组里host列表函数
    group_id = get_group_id(group_name)
    group_hosts_list = []
    group_hosts_info = zapi.host.get({ "selectGroups": "extend","groupids":[group_id]})
    for i in range(len(group_hosts_info)):
      group_hosts_list.append(group_hosts_info[i]['host'])
    return group_hosts_list

def get_group_inventory_list(group_name):#定义获取ansible host文件组里host列表函数
    return GW_dict[group_name]

def change_host_group(host_name,group_name):#定义修改host 组信息函数
    host_id = get_host_id(host_name)
    group_id = get_group_id(group_name)
    zapi.host.update({"hostid": host_id,"groups": [group_id] } )

def create_group(group_name): #定义创建组 函数
    zapi.hostgroup.create({"name": group_name })

def create_host(host_name,group_name):#定义创建 host 函数
    group_id = get_group_id(group_name)
    zapi.host.create( {"host": host_name, "interfaces": [{"type": 1, "main": 1, "useip": 1, "ip": host_name, "dns": "", "port": "10050"}] , "groups": [{ "groupid": group_id }],"tags": [{"tag": "Host name","value": host_name }], "templates": [{ "templateid": EPIC_templateid }] })


list_group('all_hosts')

for inventory_group in GW_dict:  #创建不存在组
  if inventory_group not in all_zabbix_groups_list:
    create_group(inventory_group)
    print('创建' + inventory_group + '成功')

for inventory_group in GW_dict:
  for inventory_host in get_group_inventory_list(inventory_group): 
#    host_id = get_host_id(inventory_host)
    if inventory_host in all_zabbix_allgroup_hosts_list: #host 存在于zabbix
       host_id = get_host_id(inventory_host)
       if inventory_host not in get_group_hosts_list(inventory_group): #host 不存在于 zabbix group
        change_host_group(inventory_host,inventory_group)
        print('修改' + inventory_host + '组信息为: ' + inventory_group)
    else:
      create_host(inventory_host,inventory_group)
      print('创建' + inventory_host + '成功')

3. grafana配置
   调用grafana mysql 模板
   利用sql语句生成表

(1) 创建变量group:
select name from hstgrp where name like 'kaifa-%' ;
(2)sql语句如下:
磁盘:

select gg.host ,gg.hostgroup,
        sum(CASE gg.item_name WHEN 'DISK /home'  THEN gg.value ELSE 0 END ) AS  'Total DISK',
        sum(CASE gg.item_name WHEN 'DISK /home free'  THEN gg.value ELSE 0 END ) AS  'Available DISK'
from (
select distinct ff.itemid, ff.host,ff.name hostgroup ,ff.item_name,ff. value from 
(select aa.itemid,aa.value,dd.host,ee.name,cc.name as item_name
from history_uint aa,hosts_groups bb,items cc,hosts  dd ,hstgrp ee
where aa.itemid=cc.itemid and cc.hostid=dd.hostid and  dd.hostid =bb.hostid and bb.groupid=ee.groupid and ee.name = ('$group') and (cc.name = 'DISK /home' or cc.name = 'DISK /home free')) ff  GROUP BY ff.item_name,ff.itemid
) gg GROUP BY gg.host  order by 'Available DISK' ASC
;

内存:

select gg.host ,gg.hostgroup,
        sum(CASE gg.item_name WHEN 'Total memory'  THEN gg.value ELSE 0 END ) AS  'Total memory_value',
        sum(CASE gg.item_name WHEN 'Available memory'  THEN gg.value ELSE 0 END ) AS  'Available memory_value'
from (
select distinct ff.itemid, ff.host,ff.name hostgroup ,ff.item_name,ff. value from 
(select aa.itemid,aa.value,dd.host,ee.name,cc.name as item_name
from history_uint aa,hosts_groups bb,items cc,hosts  dd ,hstgrp ee
where aa.itemid=cc.itemid and cc.hostid=dd.hostid and  dd.hostid =bb.hostid and bb.groupid=ee.groupid and ee.name = ('$group') and (cc.name = 'Total memory' or cc.name = 'Available memory')) ff  GROUP BY ff.item_name,ff.itemid
) gg GROUP BY gg.host  order by 'Available memory_value' ASC
;

(3)效果展示:

图片.png