Python读取excel调用Zabbix API批量添加主机信

1. 基础环境

运行环境：python3.7
Zabbix版本：5.0
需要对python和zabbix API 有一定了解。

2. 实现功能

读取Excel表格，批量添加主机，链接的模版必须存在，会将添加失败的用户群组名输出到日志文件./fail.txt中。

Excel表格内容如下：

# !/usr/bin/env python3
# coding:utf-8

import json
from urllib.request import Request
from urllib.request import urlopen
from urllib.error import HTTPError
import pandas as pd
import time
 
 
class zabbixtools:
    def __init__(self):
        # zabbix前端访问UERL
        self.url = "http://192.168.8.1/api_jsonrpc.php"
        self.header = {"Content-Type": "application/json"}
        # zabbix具有读写权限的可登陆账号密码
        self.authID = self.user_login("Admin", "zabbix")
 
    def user_login(self, user, password):
        data = bytes(json.dumps(
            {
                "jsonrpc": "2.0",
                "method": "user.login",
                "params": {
                    "user": user,
                    "password": password
                },
                "id": 0
            }), "utf-8")
        request = Request(self.url, data)
        for key in self.header:
            request.add_header(key, self.header[key])
        try:
            result = urlopen(request)
        except HTTPError as e:
            print("Auth Failed, Please Check Your Name And Password:", e.code)
        else:
            response = json.loads(result.read().decode('utf8'))
            result.close()
            authID = response['result']
            return authID
 
    def get_data(self, data):
        formData = bytes(data, 'utf-8')
        request = Request(self.url, formData)
        for key in self.header:
            request.add_header(key, self.header[key])
        try:
            result = urlopen(request)
        except HTTPError as e:
            if hasattr(e, 'reason'):
                print('We failed to reach a server.')
                print('Reason: ', e.reason)
            elif hasattr(e, 'code'):
                print('The server could not fulfill the request.')
                print('Error code: ', e.code)
            return 0
        else:
            response = json.loads(result.read().decode('utf-8'))
            result.close()
            return response
 
    def host_get(self, hostip):
        # hostip = raw_input("\033[1;35;40m%s\033[0m" % 'Enter Your Check Host:Host_ip :')
        data = json.dumps(
            {
                "jsonrpc": "2.0",
                "method": "host.get",
                "params": {
                    "output": ["hostid", "name", "status", "host"],
                    "filter": {
                        "host": [hostip]}
                },
                "auth": self.authID,
                "id": 1
            })
        res = self.get_data(data)['result']
        if (res != 0) and (len(res) != 0):
            # print(type(res))
 
            # for host in res:
            host = res[0]
            # if hostip == host['hostip']:
            return host['hostid']
        else:
            return ""
 
    def hostgroup_get(self, hostgroupName):
        data = json.dumps(
            {
                "jsonrpc": "2.0",
                "method": "hostgroup.get",
                "params": {
                    "output": "extend",
                    "filter": {
                        "name": [  # 列表格式，可多个
                            hostgroupName
                        ]
                    }
                },
                "auth": self.authID,
                "id": 1
            })
        res = self.get_data(data)['result']
        if (res != 0) and (len(res) != 0):
            # print(type(res))
            # for host in res:
            host = res[0]
            return host['groupid']
        else:
            return ""
 
    def hostgroup_create(self, hostgroupName):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "hostgroup.create",
            "params": {
                "name": hostgroupName
            },
            "auth": self.authID,
            "id": 1
        })
        res = self.get_data(data)['result']
 
        if (res != 0) and (len(res) != 0):
            # for host in res:
            # print(type(res))
            # print res['hostids'][0]
            return res['groupids'][0]
        else:
            return ""
 
    def template_get(self, templateName):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "template.get",
            "params": {
                "output": "extend",
                "filter": {
                    "host": [
                        templateName
                    ]
                }
            },
            "auth": self.authID,
            "id": 1,
        })
        res = self.get_data(data)['result']
        if (res != 0) and (len(res) != 0):
            # for host in res:
            # print(type(res))
            host = res[0]
            return host['templateid']
        else:
            return ""
 
    def proxy_get(self, proxyName):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "proxy.get",
            "params": {
                "output": "extend",
                "selectInterface": "extend"
            },
            "auth": self.authID,
            "id": 1,
        })
        res = self.get_data(data)['result']
        if (res != 0) and (len(res) != 0):
            # print(type(res))
 
            for proxyHost in res:
                # proxyHost = res[0]
                if proxyName == proxyHost['host']:
                    return proxyHost['proxyid']
        else:
            return ""
 
    def user_get(self, userName):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "user.get",
            "params": {
                "output": "extend",
            },
            "auth": self.authID,
            "id": 1,
        })
        res = self.get_data(data)['result']
        if (res != 0) and (len(res) != 0):
            # print(type(res))
 
            for proxyHost in res:
                # proxyHost = res[0]
                if userName == proxyHost['name']:
                    return proxyHost['userid']
        else:
            return ""
 
    def usergroup_get(self, usergroupName):
        pass
 
    def user_create(self, userName):
        pass
 
    def host_create(self, proxyID, visibleName, hostIP, hostgroupsidList, description, templateList):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "host.create",
            "params": {
                "host": hostIP,
                "name": visibleName,
                "proxy_hostid": proxyID,
                "description": description,
                "interfaces": [
                    {
                        "type": 1,
                        "main": 1,
                        "useip": 1,
                        "ip": hostIP,
                        "dns": '',
                        "port": "10050"
                    }
                ],
                "groups": hostgroupsidList,
                "templates": templateList,
            },
            "auth": self.authID,
            "id": 1
        })
        print("============")
        # ss = self.host_get(hostIP)
        # print(self.get_data(data))
        # print(self.get_data(data))
 
        # print(type(res))
        res = self.get_data(data)
        if "result" in res:
            print(res['result'])
            return res['result']['hostids'][0]
        elif "error" in res:
            # 打开一个文件
            f = open("./fail.txt", "a+")
            localtime = time.asctime(time.localtime(time.time()))
            log = localtime + "[主机] " + hostIP + ": " + res['error']['data'] + "\n"
            f.write(log)
            # 关闭打开的文件
            f.close()
 
 
if __name__ == "__main__":
    test = zabbixtools()
    excelPath = r'/data/host.xlsx'
    df = pd.read_excel(excelPath, sheet_name=0, header=0).fillna(method='pad')
    for row in range(df.__len__()):
        proxyName = df.iloc[row][0]
        visiblename = df.iloc[row][1]
        hostip = df.iloc[row][2]  # =hostname
        description = df.iloc[row][3]
        # print(description)
        if description == "Null":
            description = ""
        hostgroupName = df.iloc[row][4]
        # print(hostgroupName)
        templateName = df.iloc[row][5]
        # print(templateName)
        proxyid = test.proxy_get(proxyName)
        # print(proxyid)
        groupsidlist = []
        for indexGroup in hostgroupName.split('\n', -1):
            groupsid = test.hostgroup_get(indexGroup)
            # print(groupsid)
            if groupsid == "":
                # print(indexGroup, "00000000")
                groupsid = test.hostgroup_create(indexGroup)
                # print(groupsid)
 
            groupsidlist.append({"groupid": groupsid})
 
        print(groupsidlist)
        hosttempidlist = []
        for indexTempate in templateName.split('\n', -1):
            hosttempidid = test.template_get(indexTempate)
            # print(hosttempidid)
            hosttempidlist.append({"templateid": hosttempidid})
 
        print(hosttempidlist)
        test.host_create(proxyid, visiblename, hostip, groupsidlist, description, hosttempidlist)