편리한 Zabbix Screen Setting을 위한 zabbix.py example

########## Python 3.X INSTALL ##########

# python 3.X donwload & install

#yum install -y https://centos6.iuscommunity.org/ius-release.rpm

#yum install -y python36u python36u-libs python36u-devel python36u-pip

#ln -s python3.x python

# pip download & install

#curl -k -O https://bootstrap.pypa.io/get-pip.py

#python get-pip.py

########## Python 3.X INSTALL ##########

# pip install PyMySQL

import os

import platform

import time

from logging import handlers

import logging

import pymysql

from time import sleep

from datetime import datetime

from pyzabbix import ZabbixAPI

# 프로그램 시작

start_time = time.time()

# slash 변환

if platform.system().upper() == "LINUX":

    slash = "/"

elif platform.system().upper() == "WINDOWS":

    slash = "\\"

# 디렉토리 존재유무 파악후 디렉토리를 생성한다

log_dirname = "log"

if not os.path.isdir(log_dirname):

    os.mkdir(log_dirname)

""" DEBUG < INFO < WARNING < ERROR < CRITICAL """

# logger 인스턴스 생성 및 로그레벨 설정한다

info_logger = logging.getLogger("info_log")

info_logger.setLevel(logging.INFO)

# formatter를 생성한다

formatter = logging.Formatter("[%(levelname)s|%(filename)s:%(lineno)s] %(asctime)s > %(message)s")

   

# fileHandler streamhandler를 생성한다

info_fileHandler = logging.FileHandler(os.getcwd() + slash + log_dirname + slash + "saja_test_ZABBIX.log")

streamhandler = logging.StreamHandler()

# handler에 formatter를 세팅한다

info_fileHandler.setFormatter(formatter)

streamhandler.setFormatter(formatter)

# handler를 logging에 추가한다

info_logger.addHandler(info_fileHandler)

info_logger.addHandler(streamhandler)

# data init #

db_list = list()

db_list.append(["localhost", 3006, "saja_id", "saja_password", "ZABBIXDB"])

hosts_list = list()

items_list = list()

items_list_disk = list()

graphs_items_list = list()

graphs_items_sorted_list = list()

screens_list = list()

hostid = ""

screenid = ""

screenid_flag = 0

hostid_flag = 0

x = 0

# data init #

# query init #

get_hosts_sql = """

SELECT

         hostid,

         host,

         name

FROM hosts

WHERE 1=1

         AND available = 1

"""

get_screens_sql = """

SELECT

         screenid,

    name,

    hsize,

    vsize

FROM screens

WHERE 1=1

         AND userid = 1

"""

# query init #

# function #

def execute_query_select(connection_list, sql):

    """

    Mysql에 접속하여 Query를 SELECT 한다.

    @author kgu

    @param list Connect정보

    @param sql SQL문

    """

    try:

        host = connection_list[0]

        port = connection_list[1]

        user = connection_list[2]

        password = connection_list[3]

        database = connection_list[4]

        conn = pymysql.connect(host=host, port=port, user=user, password=password, database=database, charset='utf8')

        curs = conn.cursor()

        sql = sql

        curs.execute(sql)

        rows = curs.fetchall()

        conn.close()

    except BaseException as ex:

        info_logger.info("########## execute_query_select fail ##########")

        info_logger.info("########## sql : " + sql + " ##########")

        info_logger.info("########## ex : " + ex + " ##########")

    else:

        info_logger.debug("########## execute_query_select Success ##########")

        return list(rows)

def execute_query_insert(connection_list, sql, graphs_items_list, screenitemid, y, x):

    """

    Mysql에 접속하여 Query를 INSERT 한다.

    @author kgu

    @param list Connect정보

    @param sql SQL문

    """

    try:

        host = connection_list[0]

        port = connection_list[1]

        user = connection_list[2]

        password = connection_list[3]

        database = connection_list[4]

        conn = pymysql.connect(host=host, port=port, user=user, password=password, database=database, charset='utf8')

        curs = conn.cursor()

        for i in range(len(graphs_items_list)):

            curs.execute(sql, (screenid, str(graphs_items_list[i][1]), str(graphs_items_list[i][0]), str(x), str(y), str(screenitemid)))

            x += 1

            screenitemid += 1

        conn.commit()

        conn.close()

    except BaseException as ex:

        info_logger.info("########## execute_query_insert fail ##########")

        info_logger.info("########## sql : " + sql + " ##########")

        info_logger.info("########## ex : " + ex + " ##########")

    else:

        info_logger.debug("########## execute_query_insert Success ##########")

def get_items():

    get_items_disk_sql = "SELECT T.itemid, CASE WHEN T.key_ = 'net.if.in[enp0s25]' OR T.key_ = 'net.if.in[eno1]' OR T.key_ = 'net.if.in[eth0]' THEN 1 WHEN T.key_ = 'system.cpu.load[percpu,avg1]' OR T.key_ = 'system.cpu.load[percpu,avg5]' OR T.key_ = 'system.cpu.load[percpu,avg15]' THEN 2 WHEN T.key_ = 'vm.memory.size[available]' THEN 3 ELSE 0 END AS seq FROM (SELECT itemid, key_ FROM items WHERE 1=1 AND hostid = " + hostid + " AND key_ IN ('net.if.in[enp0s25]', 'net.if.in[eno1]', 'net.if.in[eth0]', 'system.cpu.load[percpu,avg1]', 'system.cpu.load[percpu,avg5]', 'system.cpu.load[percpu,avg15]', 'vm.memory.size[available]') ) T ORDER BY seq"

    items_list = execute_query_select(db_list[0], get_items_disk_sql)

    return items_list

def get_items_list_disk():

    get_items_sql = "SELECT itemid FROM items WHERE 1=1 AND hostid = " + hostid + " AND key_ IN ('vfs.fs.size[/,pfree]')"

    items_list_disk = execute_query_select(db_list[0], get_items_sql)

    return items_list_disk

def get_graphs_items(items_list):

    itemid = ""

    for i in range(len(items_list)):

        if i == len(items_list)-1:

            itemid += str(items_list[i][0])

        else:

            itemid += str(items_list[i][0]) + ", "

    get_graphs_items_sql = "SELECT DISTINCT max(itemid), graphid FROM graphs_items WHERE 1=1 AND itemid IN (" + itemid + ") GROUP BY graphid"

    graphs_items_list = execute_query_select(db_list[0], get_graphs_items_sql)

    return graphs_items_list

def get_screenitemid():

    get_screenitemid_sql = "SELECT MAX(screenitemid)+1 as screenitemid FROM screens_items WHERE 1=1"

    screenitemid_list = execute_query_select(db_list[0], get_screenitemid_sql)

    screenitemid = screenitemid_list[0][0]

    if screenitemid == None:

        screenitemid = 0

    return screenitemid

def get_y():

    get_y_sql = "SELECT MAX(y)+1 as y FROM screens_items WHERE 1=1 AND screenid = " + screenid

    y = execute_query_select(db_list[0], get_y_sql)

    y = y[0][0]

    if y == None:

        y = 0

    return y

def post_screens_items(graphs_items_list, screenitemid, y, x):

    post_screens_items_sql = "INSERT INTO screens_items (screenid, resourcetype, style, url, width, height, halign, valign, colspan, rowspan, max_columns, dynamic, elements, sort_triggers, application, resourceid, x, y, screenitemid) VALUES (%s,%s,'0','','500','100','0','0','1','1','3','0','0','0','',%s,%s,%s,%s)"

    execute_query_insert(db_list[0], post_screens_items_sql, graphs_items_list, screenitemid, y, x)

# function #

info_logger.info("########## program start ##########")

info_logger.debug("########## get_screens_sql ##########")

screens_list = execute_query_select(db_list[0], get_screens_sql)

info_logger.info("########################################")

info_logger.info("screenid          name")

for i in range(len(screens_list)):

    info_logger.info("   " + str(screens_list[i][0]) + "     " + str(screens_list[i][1]))

info_logger.info("########################################")

while screenid_flag < 1:

    screenid = input("추가하길 원하는 screenid 입력해주세요. ")

    for e in screens_list:

        if screenid == str(e[0]):

            screenid_flag = 1

    if screenid_flag < 1:

        info_logger.info("########## 일치하는 screendid가 존재하지않습니다. 다시선택해주세요. ##########")

info_logger.debug("########## get_hosts_sql ##########")

hosts_list = execute_query_select(db_list[0], get_hosts_sql)

info_logger.info("########################################")

info_logger.info("hostid          name")

for i in range(len(hosts_list)):

    info_logger.info(" " + str(hosts_list[i][0]) + "     " + str(hosts_list[i][2]))

info_logger.info("########################################")

while hostid_flag < 1:

    hostid = input("추가하길 원하는 hostid를 입력해주세요. ")

    for e in hosts_list:

        if hostid == str(e[0]):

            hostid_flag = 1

    if hostid_flag < 1:

        info_logger.info("########## 일치하는 screendid가 존재하지않습니다. 다시선택해주세요. ##########")

items_list = get_items()

graphs_items_list = get_graphs_items(items_list)

for i in range(len(items_list)):

    for j in range(len(graphs_items_list)):

        if items_list[i][0] == graphs_items_list[j][0]:

            graphs_items_sorted_list.append([graphs_items_list[j][1], 0])

items_list_disk = get_items_list_disk()

graphs_items_sorted_list.append([items_list_disk[0][0], 1])

screenitemid = get_screenitemid()

y = get_y()

post_screens_items(graphs_items_sorted_list, screenitemid, y, x)

info_logger.info("########## %s seconds ##########" %(round(time.time() - start_time, 2)))

info_logger.info("########## program end ##########")