python 批量导入excel_zabbix3.4用Python脚本Excel批量导入主机

1.安装xlrd读取Excel文件

1.1. 下载setuptools-38.2.4.zip，上传至zabbix服务器解压安装，下载地址：https://pypi.python.org/packages/69/56/f0f52281b5175e3d9ca8623dadbc3b684e66350ea9e0006736194b265e99/setuptools-38.2.4.zip#md5=e8e05d4f8162c9341e1089c80f742f64

[root@localhost temp]# ll        #上传文件setuptools-38.2.4.zip

total 724

-rw-r--r-- 1 root root 736683 Dec 17 22:37 setuptools-38.2.4.zip

[root@localhost temp]# unzip setuptools-38.2.4.zip        #解压setuptools-38.2.4.zip

[root@localhost temp]# cd setuptools-38.2.4                  #进入setuptools-38.2.4目录

[root@localhost temp]# cd setuptools-38.2.4                  #安装

1.2下载pip-9.0.1.tar.gz，上传至zabbix服务器解压安装，下载地址：https://pypi.python.org/packages/11/b6/abcb525026a4be042b486df43905d6893fb04f05aac21c32c638e939e447/pip-9.0.1.tar.gz#md5=35f01da33009719497f01a4ba69d63c9

[root@localhost temp]# ll

total 1896

-rw-r--r-- 1 root root 1197370 Dec 17 22:53 pip-9.0.1.tar.gz      #上传文件pip-9.0.1.tar.gz

drwxr-xr-x 9 root root 4096 Dec 17 22:55 setuptools-38.2.4

-rw-r--r-- 1 root root 736683 Dec 17 22:37 setuptools-38.2.4.zip

[root@localhost temp]# tar -xzvf pip-9.0.1.tar.gz               #解压pip-9.0.1.tar.gz

[root@localhost pip-9.0.1]# python setup.py build            #编译

[root@localhost pip-9.0.1]# python setup.py install          #安装

[root@localhost pip-9.0.1]# pip install xlrd                       #安装xlrd

3.编辑Excel模板

3.1主机名、显示名、IP、主机组、模板

3.2将Execle表zabbix_host_add.xlsx上传至zabbix服务器

[root@localhost temp]# ll

total 1964

-rw-r--r-- 1 root root 46079 Dec 17 23:28 zabbix_host_add.xlsx

4.编写Python脚本，参考http://www.361way.com/zabbix-api-2/3681.html

#!/usr/bin/python

#coding:utf-8

import json

import urllib2

from urllib2 import URLError

import sys,argparse

import xlrd

defaultencoding = 'utf-8'

if sys.getdefaultencoding() != defaultencoding:

reload(sys)

sys.setdefaultencoding(defaultencoding)

class zabbix_api:

def __init__(self):

self.url = 'http://zabbix服务器IP地址/zabbix/api_jsonrpc.php' #修改URL

self.header = {"Content-Type":"application/json"}

def user_login(self):

data = json.dumps({

"jsonrpc": "2.0",

"method": "user.login",

"params": {

"user": "Admin", #web页面登录用户名

"password": "zabbix" #web页面登录密码

},

"id": 0

})

request = urllib2.Request(self.url, data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "\033[041m 用户认证失败，请检查 !\033[0m", e.code

else:

response = json.loads(result.read())

result.close()

#print response['result']

self.authID = response['result']

return self.authID

def host_get(self,hostName=''):

data=json.dumps({

"jsonrpc": "2.0",

"method": "host.get",

"params": {

"output": "extend",

"filter":{"host":hostName}

},

"auth": self.user_login(),

"id": 1

})

request = urllib2.Request(self.url,data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError 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

else:

response = json.loads(result.read())

#print response

result.close()

print "主机数量: \033[31m%s\033[0m"%(len(response['result']))

for host in response['result']:

status={"0":"OK","1":"Disabled"}

available={"0":"Unknown","1":"available","2":"Unavailable"}

#print host

if len(hostName)==0:

print "HostID : %s\t HostName : %s\t Status :\033[32m%s\033[0m \t Available :\033[31m%s\033[0m"%(host['hostid'],host['name'],status[host['status']],available[host['available']])

else:

print "HostID : %s\t HostName : %s\t Status :\033[32m%s\033[0m \t Available :\033[31m%s\033[0m"%(host['hostid'],host['name'],status[host['status']],available[host['available']])

return host['hostid']

def hostgroup_get(self, hostgroupName=''):

data = json.dumps({

"jsonrpc":"2.0",

"method":"hostgroup.get",

"params":{

"output": "extend",

"filter": {

"name": hostgroupName

}

},

"auth":self.user_login(),

"id":1,

})

request = urllib2.Request(self.url,data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "Error as ", e

else:

#print result.read()

response = json.loads(result.read())

result.close()

#print response()

for group in response['result']:

if len(hostgroupName)==0:

print "hostgroup: \033[31m%s\033[0m \tgroupid : %s" %(group['name'],group['groupid'])

else:

print "hostgroup: \033[31m%s\033[0m\tgroupid : %s" %(group['name'],group['groupid'])

self.hostgroupID = group['groupid']

return group['groupid']

def template_get(self,templateName=''):

data = json.dumps({

"jsonrpc":"2.0",

"method": "template.get",

"params": {

"output": "extend",

"filter": {

"name":templateName

}

},

"auth":self.user_login(),

"id":1,

})

request = urllib2.Request(self.url, data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "Error as ", e

else:

response = json.loads(result.read())

result.close()

#print response

for template in response['result']:

if len(templateName)==0:

print "template : \033[31m%s\033[0m\t id : %s" % (template['name'], template['templateid'])

else:

self.templateID = response['result'][0]['templateid']

print "Template Name : \033[31m%s\033[0m "%templateName

return response['result'][0]['templateid']

def hostgroup_create(self,hostgroupName):

if self.hostgroup_get(hostgroupName):

print "hostgroup \033[42m%s\033[0m is exist !"%hostgroupName

sys.exit(1)

data = json.dumps({

"jsonrpc": "2.0",

"method": "hostgroup.create",

"params": {

"name": hostgroupName

},

"auth": self.user_login(),

"id": 1

})

request=urllib2.Request(self.url,data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "Error as ", e

else:

response = json.loads(result.read())

result.close()

print "\033[042m 添加主机组:%s\033[0m hostgroupID : %s"%(hostgroupName,response['result']['groupids'])

def host_create_andy(self,hostName,visibleName, hostip, hostgroupName, templateName):

if self.host_get(hostip):

print "\033[041m该主机已经添加!\033[0m"

sys.exit(1)

group_list=[]

template_list=[]

for i in hostgroupName.split(','):

var = {}

var['groupid'] = self.hostgroup_get(i)

group_list.append(var)

for i in templateName.split(','):

var={}

var['templateid']=self.template_get(i)

template_list.append(var)

data = json.dumps({

"jsonrpc":"2.0",

"method":"host.create",

"params":{

"host": hostName,

"name": visibleName,

"interfaces": [

{

"type": 2, #1：表示IP；2表示SNMP

"main": 1,

"useip": 1,

"ip": hostip,

"dns": "",

"port": "161" #IP端口10051；SNMP端口161

}

],

"groups": group_list,

"templates": template_list,

},

"auth": self.user_login(),

"id":1

})

request = urllib2.Request(self.url, data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "Error as ", e

else:

response = json.loads(result.read())

result.close()

print "添加主机 : \033[42m%s\031[0m \tid :\033[31m%s\033[0m" % (hostip, response['result']['hostids'])

def host_create(self, hostip, hostgroupName, templateName):

if self.host_get(hostip):

print "\033[041m该主机已经添加!\033[0m"

sys.exit(1)

group_list=[]

template_list=[]

for i in hostgroupName.split(','):

var = {}

var['groupid'] = self.hostgroup_get(i)

group_list.append(var)

for i in templateName.split(','):

var={}

var['templateid']=self.template_get(i)

template_list.append(var)

data = json.dumps({

"jsonrpc":"2.0",

"method":"host.create",

"params":{

"host": hostip,

"interfaces": [

{

"type": 2,

"main": 1,

"useip": 1,

"ip": hostip,

"dns": "",

"port": "161"

}

],

"groups": group_list,

"templates": template_list,

},

"auth": self.user_login(),

"id":1

})

request = urllib2.Request(self.url, data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "Error as ", e

else:

response = json.loads(result.read())

result.close()

print "添加主机 : \033[42m%s\031[0m \tid :\033[31m%s\033[0m" % (hostip, response['result']['hostids'])

def host_disable(self,hostip):

data=json.dumps({

"jsonrpc": "2.0",

"method": "host.update",

"params": {

"hostid": self.host_get(hostip),

"status": 1

},

"auth": self.user_login(),

"id": 1

})

request = urllib2.Request(self.url,data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except URLError as e:

print "Error as ", e

else:

response = json.loads(result.read())

result.close()

print '----主机现在状态------------'

print self.host_get(hostip)

def host_delete(self,hostid):

hostid_list=[]

#print type(hostid)

for i in hostid.split(','):

var = {}

var['hostid'] = self.host_get(i)

hostid_list.append(var)

data=json.dumps({

"jsonrpc": "2.0",

"method": "host.delete",

"params": hostid_list,

"auth": self.user_login(),

"id": 1

})

request = urllib2.Request(self.url,data)

for key in self.header:

request.add_header(key, self.header[key])

try:

result = urllib2.urlopen(request)

except Exception,e:

print e

else:

result.close()

print "主机 \033[041m %s\033[0m 已经删除 !"%hostid

if __name__ == "__main__":

zabbix=zabbix_api()

parser=argparse.ArgumentParser(description='zabbix api ',usage='%(prog)s [options]')

parser.add_argument('-H','--host',nargs='?',dest='listhost',default='host',help='查询主机')

parser.add_argument('-G','--group',nargs='?',dest='listgroup',default='group',help='查询主机组')

parser.add_argument('-T','--template',nargs='?',dest='listtemp',default='template',help='查询模板信息')

parser.add_argument('-A','--add-group',nargs=1,dest='addgroup',help='添加主机组')

parser.add_argument('-C','--add-host',dest='addhost',nargs=3,metavar=('192.168.2.1', 'test01,test02', 'Template01,Template02'),help='添加主机,多个主机组或模板使用分号')

parser.add_argument('-d','--disable',dest='disablehost',nargs=1,metavar=('192.168.2.1'),help='禁用主机')

parser.add_argument('-L','--allin',dest='allin',nargs='?',default='allin',help='从Excel批量导入主机')

parser.add_argument('-D','--delete',dest='deletehost',nargs='+',metavar=('192.168.2.1'),help='删除主机,多个主机之间用分号')

parser.add_argument('-v','--version', action='version', version='%(prog)s 1.0')

if len(sys.argv)==1:

print parser.print_help()

else:

args=parser.parse_args()

if args.listhost != 'host' :

if args.listhost:

zabbix.host_get(args.listhost)

else:

zabbix.host_get()

if args.listgroup !='group':

if args.listgroup:

zabbix.hostgroup_get(args.listgroup)

else:

zabbix.hostgroup_get()

if args.listtemp != 'template':

if args.listtemp:

zabbix.template_get(args.listtemp)

else:

zabbix.template_get()

if args.addgroup:

zabbix.hostgroup_create(args.addgroup[0])

if args.addhost:

zabbix.host_create(args.addhost[0], args.addhost[1], args.addhost[2])

if args.disablehost:

zabbix.host_disable(args.disablehost)

if args.deletehost:

zabbix.host_delete(args.deletehost[0])

if args.allin != 'allin':

workbook = xlrd.open_workbook('zabbix_host_add.xlsx') #Excel名

for row in xrange(workbook.sheets()[0].nrows):

hostname = workbook.sheets()[0].cell(row, 0).value

visible = workbook.sheets()[0].cell(row, 1).value

hostip = workbook.sheets()[0].cell(row, 2).value

hostgroup = workbook.sheets()[0].cell(row, 3).value

hosttemp = workbook.sheets()[0].cell(row, 4).value

zabbix.host_create_andy(hostname,visible,hostip,hostgroup, hosttemp)

上面红色标注，请根据实际情况修改。

上传Python脚本zabbix_host.py至zabbix服务器

[root@localhost temp]# ll

total 1964

-rwxr-xr-x 1 root root 14644 Dec 17 23:28 zabbix_host.py

[root@localhost temp]# chown zabbix:zabbix zabbix_host.py  #修改属组属主

[root@localhost temp]# chmod +x zabbix_host.py           #添加执行权限

[root@localhost temp]# python zabbix_host.py -L   #执行zabbix_host.py进行主机添加