Zabbix批量创建用户_用户组_导出数据脚本合集-从零到无

- - 时间：2020年12月7日

- - 作者：飞翔的小胖猪

该文档的作用是笔记，包含了zabbix添加资源时的三个脚本。所以格式上不会太严谨。有需要的小伙伴可以自行查看。前两个脚本的作用是添加资源的，现网环境中往往有很多业务部门手动添加也是可以的但是可能会有点累，这个时候你可以选用创建用户组这个脚本，先按照execl文件的格式填入数据，然后再脚本中指定该脚本，运行脚本则按照execl表格创建用户组，同时会输出一个新的execl包含用户组名及ID号。  当然现网环境最多还是主机如果一台一台的添加估计会疯的，同理使用创建用户的脚本可以实现批量，也是需要按照execl表格填入数据。

创建用户

脚本：

from zabbix_api import ZabbixAPI
import urllib3
import sys
import json
import xlrd
import requests
url = 'http://192.168.111.124/zabbix/api_jsonrpc.php'
headers = {'Content-Type': 'application/json-rpc'}
server_ip= '192.168.111.124'

# 获取token
def getToken(username, passwd):
    # url = 'http://%s/zabbix/api_jsonrpc.php'%server_ip

    # headers = {'Content-Type': 'application/json-rpc'}

    username = username
    passwd = passwd
    data = {
        "jsonrpc": "2.0",
        "method": "user.login",
        "params": {
            "user": username,
            "password": passwd
        },
        "id": 1
    }
    request = requests.post(url=url,headers=headers,data=json.dumps(data))
    dict = json.loads(request.text)
    print('第一次获取到的token为：',dict['result'])
    return dict['result']


# 从api获取主机信息，
def getHosts(token_num):
     data = {
         "jsonrpc": "2.0",
         "method": "host.get",
         "params": {
         "output": [
                 "hostid",
                 "host"
                    ],
         "selectInterfaces": [
            "interfaceid",
            "ip"
              ]
                   },
         "id": 2,
         "auth": token_num,

             }

     request = requests.post(url=url,headers=headers,data=json.dumps(data))
     dict = json.loads(request.content)
     #print dict['result']
     return dict['result']

#获取模板ID,传递一个token值和模板名输出对应的模板id号。查出来的id号可用在后续创建主机时填入模板号。
def get_temp_id(token_num,temp_name):
    temp_data = {
     "jsonrpc": "2.0",
     "method": "template.get",
     "params": {
         "output": "extend",
         "filter": {
             "host": [
                 temp_name,
             ]
         }
    },
    "auth": token_num,
    "id": 1,
    }
    request = requests.post(url=url,headers=headers,data=json.dumps(temp_data))
    dict = json.loads(request.text)
    cc = dict['result'][0]['templateid']
    return cc

#获取主机组ID，传递一个token值和主机名输出对应的模板id号。查出来的id号可用在后续创建主机时填入主机号。
def get_hostgroup_id(token_num,hostgroup_name):
    print("程序接收到的token为：",token_num)
    group_info = {
        "jsonrpc": "2.0",
        "method": "hostgroup.get",
        "params": {
            "output": "extend",
            "filter": {
                "name": [
                    hostgroup_name,
                ]
            }
        },
        "auth": token_num,
        "id": 1
    }
    request = requests.post(url=url, headers=headers,data=json.dumps(group_info))
    dict = json.loads(request.text)
    # ff=dict2['result']
    r_group_id = dict['result'][0]['groupid']
    return r_group_id


#创建主机，并添加主机资产信息。
#通过传递一个token和一个字典过来，字典中包括了很多主机相关的信息。
def create_host(token_num,host_info):
    new_host = {
        "jsonrpc": "2.0",
        "method": "host.create",
        "params": {
            "host": "test_lvs1",        #使用host_info字典中的hostname字段填入。
            "interfaces": [
                {
                    "type": 1,
                    "main": 1,
                    "useip": 1,
                    "ip": "192.168.111.11",   #使用host_info字典中的ip字段填入。
                    "dns": "",
                    "port": "10050"           #默认使用10050端口
                }
            ],
            "groups": [
                {
                    "groupid": "2"            #使用host_info字段中的groupid字段填入。
                }
            ],
            "templates": [
                {
                    "templateid": "10001"     #使用host_info字段中的templateid字段填入。
                }
            ],
            "inventory_mode": 0,
            "inventory": {                   #填入资产相关的内容，具体字段及定义请参考XXXXXXXXXX
                "macaddress_a": "01234",
                "macaddress_b": "56768"
            }
        },
        "auth": token_num,
        "id": 1
    }

    request = requests.post(url=url, headers=headers, data=json.dumps(new_host))
    dict1 = json.loads(request.text)
    print(dict1)


#批量创建主机添加资产信息，虚拟机。
def create_host_vm(token_num,host_info):
    for i in host_info:
        new_host = {
            "jsonrpc": "2.0",
            "method": "host.create",
            "params": {
                "host": i[3],
                "interfaces": [
                    {
                        "type": 1,
                        "main": 1,
                        "useip": 1,
                        "ip": i[0],
                        "dns": "",
                        "port": "10050"
                    }
                ],
                "groups": [
                    {
                        "groupid": "2"
                        #"groupid": int(i[69]),
                    }
                ],
                "templates": [
                    {
                        "templateid": "10001"
                    }
                ],
                "inventory_mode": 0,
                "inventory": {
                    "type": i[1],
                    "type_full": i[2],
                    "name": i[3],
                    "os": i[5],
                    "serialno_a": i[8],
                    "tag": i[10],
                    "asset_tag": i[11],
                    "hardware": i[14],
                    "hardware_full": i[15],
                    "software": i[16],
                    "software_full": i[17],
                    "contact": i[23],
                    "location": i[24],
                    "vendor": i[31],
                    "deployment_status": i[34],
                    "host_networks": i[38],
                    "host_netmask": i[39],
                    "host_router": i[40],
                    "oob_ip": i[41],
                    "oob_netmask": i[42],
                    "oob_router": i[43],
                    "date_hw_install": i[45],
                    "site_address_a": i[48],
                    "site_city": i[51],
                    "site_state": i[52],
                    "site_country": i[53],
                    "site_rack": i[55],
                    "site_notes": i[56],
                    "poc_1_name": i[57],
                    "poc_1_email": i[58],
                    "poc_1_phone_a": i[59],
                    "poc_1_cell": i[61],
                    "poc_2_name": i[64],
                    "poc_2_email": i[65],
                    "poc_2_phone_a": i[66],
                    "poc_2_cell": i[68],
                }
            },
            "auth": token_num,
            "id": 1
        }
        request = requests.post(url=url, headers=headers, data=json.dumps(new_host))
        dict1 = json.loads(request.text)
        print(dict1)






#通过读取execl中的数据填充到数组中，数组中嵌套字典。
def read_execl_info(execl_dir):
    host_info_list = list()
    host_info_dict = {}
    #xl = xlrd.open_workbook('d:/test.xlsx')
    xl = xlrd.open_workbook(execl_dir)
    table = xl.sheets()[0]
    table_sum_num = table.ncols
    #print("文档总列数为：", table_sum_num)
    table_row_num = table.nrows
    #print("文档总行数为：", table_row_num)
    # 获取每一个单元格的数据
    for i in range(1, table_row_num):
        # 此处初始化一下字典
        host_info_dict = {}
        #print("此时列表的值：", host_info_list)
        for j in range(0, table_sum_num):
            # print(table.cell(i,j).value)
            host_info_dict[j] = table.cell(i, j).value
            # 在循环体内一次加入数据到字典中哈哈哈
        host_info_list.append(host_info_dict)
    #print("列表总长度为：", len(host_info_list))
    # 取出数据
    #for i in host_info_list:
    #    print(i)
    return host_info_list


# 整理信息,输出想要的信息，组合成字典，我这边提出ip。
def getProc(data):
    dict = {}
    list = data
    for i in list:
        host = i['host']
        inter = i['interfaces']
        for j in inter:
            ip = j['ip']
            dict[host] = ip

    return dict


# 排序ip列表
def getData(dict):
    data = dict
    ip_list = []
    for key in data.keys():
        ip = data[key]
        ip_list.append(ip)
    ip_list = list(set(ip_list))
    ip_list.sort()
    return ip_list


# 整理输出ip
def getGroup(ip_list):
    ip_group = {}
    ips = ip_list
    for i in ips:
        print(i)


if __name__ == "__main__":
    username = 'Admin'
    passwd = 'zabbix'
    token_num = getToken(username,passwd)

    #--------获取指定主机组ID时使用-------------------
    #group_name = "Linux servers"
    #group_id = get_hostgroup_id(token_num,group_name)
    #print("最终接收到的程序种的groupid为：",group_id)
    #---------------------------------------------

    #--------获取指定TEMP（模板）ID时使用-------------------
    temp_name = "Template OS Linux by Zabbix agent"
    temp_id = get_temp_id(token_num,temp_name)
    print("最终接收到的程序种的groupid为：",temp_id)
    #---------------------------------------------

    #--------测试获取execl内容-------------------
    #execl_name = "d:/test.xlsx"
    #host_info_list = read_execl_info(execl_name)
    #print("最终接收到的程序种的groupid为：",temp_id)
    #---------------------------------------------

    #--------测试使用函数新建主机-------------------
    #需要取消获取execl内容的注释
    #r_create_host = create_host_vm(token_num,host_info_list)



    #data = getHosts(token_num)
    #hosts = getProc(data)
    #ip_list = getData(hosts)
    #getGroup(ip_list)

配合用户创建的execl表：

链接：https://pan.baidu.com/s/1r0yoqD2UUx85aKASA6iJlQ
提取码：36e3

创建用户组

脚本：

from zabbix_api import ZabbixAPI
import urllib3
import sys
import json
import xlrd
import requests
import xlsxwriter
url = 'http://192.168.111.124/zabbix/api_jsonrpc.php'
headers = {'Content-Type': 'application/json-rpc'}
server_ip= '192.168.111.124'

# 获取token
def getToken(username, passwd):
    # url = 'http://%s/zabbix/api_jsonrpc.php'%server_ip
    # headers = {'Content-Type': 'application/json-rpc'}
    username = username
    passwd = passwd
    data = {
        "jsonrpc": "2.0",
        "method": "user.login",
        "params": {
            "user": username,
            "password": passwd
        },
        "id": 1
    }
    request = requests.post(url=url,headers=headers,data=json.dumps(data))
    dict = json.loads(request.text)
    print('第一次获取到的token为：',dict['result'])
    return dict['result']



#通过读取execl中的数据填充到数组中，数组中嵌套字典。
def read_group_execl_info(execl_dir):
    host_info_list = list()
    xl = xlrd.open_workbook(execl_dir)
    table = xl.sheets()[0]
    table_sum_num = table.ncols
    #print("文档总列数为：", table_sum_num)
    table_row_num = table.nrows
    #print("文档总行数为：", table_row_num)
    # 获取每一个单元格的数据
    for i in range(1, table_row_num): #从第二行开始获取数据，下标为0时是标题列
        # 此处初始化一下字典
        host_info_dict = {}
        #print("此时列表的值：", host_info_list)
        for j in range(0, table_sum_num):
            host_info_dict[j] = table.cell(i, j).value
            # 在循环体内一次加入数据到字典中哈哈哈
        host_info_list.append(host_info_dict)
    print("列表总长度为：", len(host_info_list))
    # 取出数据
    #for i in host_info_list:
        #print(i)
    return host_info_list

#create_host_group通过获取execl数据创建新的组，并把新的组信息置入execl表中。
def create_host_group(token_num,host_info):
    host_info_list = list()
    for i in host_info:
        host_info_dict = dict()
        new_group = {
           "jsonrpc": "2.0",
           "method": "hostgroup.create",
           "params": {
           "name": i[2]
           },
        "auth": token_num,
        "id": 1
        }
        host_info_dict["业务处室"] = i[0]
        host_info_dict["业务科室"] = i[1]
        host_info_dict["组简称"] = i[2]
        request = requests.post(url=url, headers=headers, data=json.dumps(new_group))
        dict_r = json.loads(request.text)
        host_info_dict["id"] = dict_r['result']['groupids'][0]
        host_info_list.append(host_info_dict)
    return host_info_list


#插入数据到结果表中,从create_host_group函数处获取到返回的列表用来插入到execl中。
def into_group_id_execl(group_execl_dir,group_info):
        print(group_info)
        # 创建文件
        workbook = xlsxwriter.Workbook(group_execl_dir + '.xls')
        # 创建工作薄
        worksheet = workbook.add_worksheet()
        print(" 创建工作薄");
        # 写入标题（第一行）
        i = 0
        for value in ["业务处室", "业务科室", "组简称","id号"]:
            worksheet.write(0, i, value)
            i = i + 1
            # 写入内容：
        j = 1
        for kk in group_info:
            worksheet.write(j, 0, kk['业务处室'])
            worksheet.write(j, 1, kk['业务科室'])
            worksheet.write(j, 2, kk['组简称'])
            worksheet.write(j, 3, kk['id'])
            j = j+1
        workbook.close()






if __name__ == "__main__":
    username = 'Admin'
    passwd = 'zabbix'
    token_num = getToken(username,passwd)
    execl_name = "d:/t222.xlsx"
    group_id_execl = "d:/t3333.xlsx"

    return_group_info = read_group_execl_info(execl_name)
    #print(return_group_info)
    cc = create_host_group(token_num,return_group_info)
    print(cc)
    into_group_id_execl(group_id_execl,cc)
    #data = getHosts(token_num)
    #hosts = getProc(data)
    #ip_list = getData(hosts)
    #getGroup(ip_list)

配合用户组创建的execl表：

链接：https://pan.baidu.com/s/1GIV3drJVraIjXUdB58c7cQ
提取码：rcvr

导出主机数据

脚本：

#!/usr/bin/python3
# @Date: 2020/10/29 21:16
# @Author: lvan
# @email: yinwanit@qq.com
# -*- coding: utf-8 -*-

import pymysql
import time,datetime
import math
from decimal import *
import xlsxwriter
import xlrd
#打开数据库连接函数
def open_mysql_db(zdbhost,zdbuser,zdbpass,zdbport,zdbname):
    print(".开始连接数据库...")
    conn = pymysql.connect(host=zdbhost, user=zdbuser, passwd=zdbpass, port=zdbport, db=zdbname, charset="utf8")
    cursor = conn.cursor()
    print("--完成：连接数据库，状态OK！")
    return cursor

# 指定模板ID，获取模板中包含的主机id号
def get_temp_id(db_cursor,groupid):
    print("开始获取模板中包含主机ID号...")
    sql = '''select hostid from hosts_templates where templateid = "{0}"'''.format(groupid)
    db_cursor.execute(sql)
    hostlist = [i for i in list("%s" %j for j in db_cursor.fetchall())]
    print("--完成：主机ID号获取，状态OK！")
    return hostlist
    #结果为['10357', '10362', '10363', '10365']


#通过hostid获取到主机的host名，定义一个保存主机IP地址和ID号的字典
def get_host_for_hostid(hostlist,db_cursor):
    print('.开始生成主机id号和主机名对应关系...')
    Ipinfo_dict = dict()
    for hostid in hostlist:  #每次从hostlist中取一个hostid出来，然后获取到指定的host通过字典的方式加入到IpinfoList中去。
        #print("hostid:",hostid)
        sql = '''select host from hosts where status = 0 and hostid = {0}'''.format(hostid)
        ret = db_cursor.execute(sql)
        if ret:
            for i in db_cursor.fetchone():
                Ipinfo_dict[hostid] = i
    print('--完成：主机id与主机名关系生成，状态OK！')
    return Ipinfo_dict
    #结果{'10357': '192.168.111.131', '10362': '192.168.111.11', '10363': '192.168.111.12', '10365': '192.168.111.124'}



#获取指定id号的主机的item资源号,在trends表和trends_uint中
def get_itemid(keys,hostlist,db_cursor):
    print('.开始获取ITEM号...')
    Item_key_dict = dict()
    Hostid_Item_dict = dict()
    Item_name_list = list()
    item_name_dir = dict()
    for bb in ['trends','trends_uint']:
        Iteminfo_list = list()
        for j in keys[bb]:
            for k in hostlist:
                #print("this is %s,this er %s",j,k)
                sql = '''select itemid from items where hostid = {0} and key_ = "{1}" '''.format(k,j)
                if db_cursor.execute(sql):
                    itemid = "".join("%s" %i for i in list(db_cursor.fetchone()))
                    Hostid_Item_dict[itemid] = k
                    Iteminfo_list.append(itemid)
                    item_name_dir[itemid] = j
                else:
                    itemid = None
            Item_key_dict[bb] =Iteminfo_list
    print('--完成：ITEM号获取，状态OK！')
    return Item_key_dict,Hostid_Item_dict,item_name_dir
    #结果：输出的item_dict: {'trends': ['33875', '34302', '34367', '34497', '33882', '34309', '34374', '34504', '33878', '34305', '34370', '34500'], 'trends_uint': ['33903', '34330', '34395', '34525', '33905', '34332', '34397', '34527']}
    #输出的item_host_dict: {'33875': '10357', '34302': '10362', '34367': '10363', '34497': '10365', '33882': '10357', '34309': '10362', '34374': '10363', '34504': '10365', '33878': '10357', '34305': '10362', '34370': '10363', '34500': '10365', '33903': '10357', '34330': '10362', '34395': '10363', '34525': '10365', '33905': '10357', '34332': '10362', '34397': '10363', '34527': '10365'}



#整合数据,之前得主机id，ip地址，item值名，item名整合在一起。
def format_all_data(host_id_list,id_ip_dir,Item_key_dict,Hostid_Item_dict,Item_name_dir,item_values_dir_list):
    print('.开始整合数据...')
    resut_info = dict()
    for i in list(id_ip_dir.values()):
        temp_values_dir = dict()
        #print(i) #通过ip来确定，如果i和item_name_dir里面数据查出来的一致则记录数据到字典，当数据记录完毕后添加到最终字典中，并开启下一次循环，清空temp_values_dir字典
        for j in list(Item_name_dir.keys()):
            #print(j)
            temp_ip = id_ip_dir[Hostid_Item_dict[j]]
            temp_item_name = Item_name_dir[j]
            temp_value = item_values_dir_list[j]
            if temp_ip == i:
                temp_values_dir[temp_item_name] = temp_value
        resut_info[i] = temp_values_dir
    print('--完成：整合数据完成，状态OK！')
    return resut_info
    #最终想得到的数据格式为：{ip:{{cpu:[最小，中间，最大]}},{内存：[最小，中间，最大]}}


#查询指定表中的指定指标的数据,反馈itemid和及值名字类型大小。
def get_items_valuesb(item_dict_r,db_cursor,start_time,end_time):
    print('.开始查询数据...')
    resultlist = {}
    for table_name in ['trends','trends_uint']:
        for itemid in item_dict_r[table_name]:
            sql = '''select min(value_min),avg(value_avg),max(value_max) from {2} where itemid = {3} and clock >= {4} and clock <= {5}'''.format(type, type, table_name, itemid, start_time, end_time)
            db_cursor.execute(sql)
            result = db_cursor.fetchall()
            for aa,bb,cc in result:
                value_list = list()
                value_list.append(aa)
                value_list.append(bb)
                value_list.append(cc)
                resultlist[itemid] = value_list
    print('完成：数据查询完成，状态OK！')
    return  resultlist
    #生成格式为{itemid:[min,avg,max]}


#写入数据到execl中
def writeexecl(format_data_r_dict,file_dir):
    # 创建文件
    print('开始生成execl文件...')
    workbook = xlsxwriter.Workbook(file_dir)
    # 创建工作薄
    worksheet = workbook.add_worksheet()
    print(" 创建工作薄");
    # 写入标题（第一行）
    i = 0
    for value in ["ip地址", "CPU负载谷值", "CPU负载均值", "CPU负载峰值","根目录使用GB",'根目录使用百分比GB','更目录总量GB','内存可用谷值GB','内存可用均值GB','内存可用峰值GB','内存总大小GB','CPU空闲谷','CPU空闲均','CPU空闲峰','CPU个数']:
        worksheet.write(0, i, value)
        i = i + 1
        # 写入内容：
    j = 1
    for ip_host in list(format_data_r_dict.keys()):
        #这为一行
        value= format_data_r_dict[ip_host]
        worksheet.write(j, 0, ip_host)
        worksheet.write(j, 1, value['system.cpu.load[all,avg15]'][0])
        worksheet.write(j, 2, value['system.cpu.load[all,avg15]'][1])
        worksheet.write(j, 3, value['system.cpu.load[all,avg15]'][2])
        worksheet.write(j, 4, '{}GB'.format(math.ceil(value['vfs.fs.size[/,used]'][0] / 1024 / 1024 / 1024)))
        worksheet.write(j, 5, '{:.2f}%'.format(value['vfs.fs.size[/,pused]'][0]))
        worksheet.write(j, 6, '{}GB'.format(math.ceil(value['vfs.fs.size[/,total]'][0] / 1024 / 1024 / 1024)))
        worksheet.write(j, 7, '{}GB'.format(math.ceil(value['vm.memory.size[available]'][0] / 1024 / 1024 / 1024)))
        worksheet.write(j, 8, '{}GB'.format(math.ceil(value['vm.memory.size[available]'][1] / 1024 / 1024 / 1024)))
        worksheet.write(j, 9, '{}GB'.format(math.ceil(value['vm.memory.size[available]'][2] / 1024 / 1024 / 1024)))
        worksheet.write(j, 10, '{}GB'.format(math.ceil(value['vm.memory.size[total]'][0] / 1024 / 1024 / 1024)))
        worksheet.write(j, 11, '{:.2f}%'.format(value['system.cpu.util[,idle]'][0]))
        worksheet.write(j, 12, '{:.2f}%'.format(value['system.cpu.util[,idle]'][1]))
        worksheet.write(j, 13, '{:.2f}%'.format(value['system.cpu.util[,idle]'][2]))
        worksheet.write(j, 14, value['system.cpu.num'][0])
        j = j + 1
    workbook.close()
    print(" 完成：execl文件生成，状态OK！路径为： ",file_dir)

if __name__ == "__main__":
    # zabbix数据库信息：
    zdbhost1 = "192.168.111.124"
    zdbuser1 = "zabbix"
    zdbpass1 = "password"
    zdbport1 = 3306
    zdbname1 = "zabbix"
    #监控模板id号
    groupid = 10001
    #定义时间范围,此处填写的时时间戳。可使用https://tool.lu/timestamp/网页工具自行转换，所以失效了自行百度。
    start_time = 0
    end_time = 999999999999
    #设置execl保存文件名
    save_file_dir = 'test2222.xls'
    # 需要查询的key列表，trends_unit字典里面的是trends_uint里的值；trends字典里面的是trends的值
    keys = {
        'trends_uint': [
            'vfs.fs.size[/,used]',
            'vm.memory.size[available]',
            'vm.memory.size[total]',
            'system.cpu.num',
            'vfs.fs.size[/,total]',
        ],
        'trends': [
            'system.cpu.load[all,avg15]',
            'system.cpu.util[,idle]',
            'system.swap.size[,pfree]',
            'vfs.fs.size[/,pused]',
        ],
    }
    db_r = open_mysql_db(zdbhost1,zdbuser1,zdbpass1,zdbport1,zdbname1)
    hostid_r = get_temp_id(db_r,groupid)
    ee = get_host_for_hostid(hostid_r,db_r)
    a,b,c=get_itemid(keys,hostid_r,db_r)
    ff = get_items_valuesb(a, db_r,start_time,end_time)
    ll = format_all_data(hostid_r,ee,a,b,c,ff)
    writeexecl(ll,save_file_dir)