Zabbix Api 拉取某个监控项的所有主机的值并保存到Excel中

例：拉取键值为"LinuxMemUsedPct"的监控项的所有主机的值,保存到excel文件中,excel文件命名格式为"LastValue(item_key).xlsx"。

Py脚本使用需要修改的地方：

1. 初始化信息根据个人环境修改

2. 脚本最下面需定义"item_key"监控项键值的变量参数

import json
import requests
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Side, Border, PatternFill
import os
import logging



class ZabbixApi:
    def __init__(self):  # 初始化信息
        self.url = 'http://10.0.xx.xx/api_jsonrpc.php'
        self.user = 'xxx'
        self.passwd = 'xxx'
        self.header = {
            'Content-Type': 'application/json-rpc',
            'User-Agent': 'python/pyzabbix',
            'Cache-Control': 'no-cache'
        }


    def UserLogin(self):  # api登录操作
        data = {
            "jsonrpc": "2.0",
            "method": "user.login",
            "params": {
                "user": self.user,
                "password": self.passwd
            },
            "id": 0,
        }
        return self.UrlRequest(data)

    def UrlRequest(self, data):  ##
        request = requests.post(url=self.url,data=json.dumps(data),headers=self.header,verify=False)
        response = json.loads(request.text)
        return response['result']

    def GetItem(self,key):  # 'key_'为要检查lastvalue的监控项键值,由第一个参数key传递
        data = {
            "jsonrpc": "2.0",
            "method": "item.get",
            "params": {
                "output": ["name","lastvalue"],
                "search": {
                    "key_": key
                },
            "selectHosts": ["host"]
            },
            "auth": token,
            "id": 1
        }
        return self.UrlRequest(data)

    def writeExcel(self, fileName, ZabbixData):
        WorkBook = Workbook()
        Sheet = WorkBook.active
        Sheet.title = 'ItemInfo'
        TableTitle = ['host_name', "item_name","LastValue"]
        TitleColumn = {}
        AllHostItemValues = []

        for row in range(len(TableTitle)):
            Col = row + 1
            Column = Sheet.cell(row=1, column=Col)
            Column.value = TableTitle[row]
            # TitleCol = Column.coordinate.strip('1')
            # TitleColumn[TableTitle[row]] = TitleCol

        # 整理Zabbix 监控数据逐行写入到表格中
        for iteminfo in ZabbixData.values():
            HostItemValues = []
            HostItemValues.append(iteminfo['hosts'][0]['host']) #主机名称
            HostItemValues.append(iteminfo['name']) #监控项名称
            HostItemValues.append(iteminfo['lastvalue']) #LastValue

            AllHostItemValues.append(HostItemValues)

        # 将所有信息写入到表格中
        for HostValue in range(len(AllHostItemValues)):
            Sheet.append(AllHostItemValues[HostValue])
            print(AllHostItemValues[HostValue])

        ############ 设置单元格样式 ############
        # 字体样式
        TitleFont = Font(name="宋体", size=12, bold=True, italic=False, color="000000")
        TableFont = Font(name="宋体", size=11, bold=False, italic=False, color="000000")
        # 对齐样式
        alignment = Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
        # 边框样式
        side1 = Side(style='thin', color='000000')
        border = Border(left=side1, right=side1, top=side1, bottom=side1)
        # 填充样式
        pattern_fill = PatternFill(fill_type='solid', fgColor='99ccff')
        # 设置列宽
        column_width = {'A': 25, 'B': 25, 'C': 25, 'D': 25, 'E': 25, 'F': 16, 'G': 18, 'H': 18, 'I': 22, 'J': 22,
                        'K': 23,
                        'L': 15, 'M': 16, 'N': 16, 'O': 14, 'P': 16}
        for i in column_width:
            Sheet.column_dimensions[i].width = column_width[i]
        # 设置首行的高度
        Sheet.row_dimensions[1].height = 38
        # 冻结窗口
        Sheet.freeze_panes = 'A2'
        # 添加筛选器
        Sheet.auto_filter.ref = Sheet.dimensions

        # 设置单元格字体及样式
        for row in Sheet.rows:
            for cell in row:
                if cell.coordinate.endswith('1') and len(cell.coordinate) == 2:
                    cell.alignment = alignment
                    cell.font = TitleFont
                    cell.border = border
                    cell.fill = pattern_fill
                else:
                    cell.font = TableFont
                    cell.alignment = alignment
                    cell.border = border
        WorkBook.save(filename=fileName)

if __name__ == '__main__':
    zapi = ZabbixApi()
    token = zapi.UserLogin()
    print(f'身份令牌为"{token}"')

    item_key = 'LinuxMemUsedPct'      ### 定义监控项键值的参数 
    zabbixDict = {}
    zabbixData = zapi.GetItem(item_key)
    fileName = os.path.join(os.getcwd(),"LastValue" + f'({item_key})' + '.xlsx')
    for host in zabbixData:
        hostid = host['hosts'][0]['hostid']
        zabbixDict[hostid] = host
    zapi.writeExcel(fileName, zabbixDict)