Zabbix常用SQL操作
前言
线上API项目中, 有部分常用Zabbix操作, 采用直接读库方式。
本文章记录出现在项目中的常用SQL操作(主要以3.x版本为例)，主要使用Python语言做演示。
Zabbix一些对应关系及常量
# 历史表对应ID
ZBX_HISTORY_TABLES = ("history", "history_str",
"history_log", "history_uint", "history_text")
# 一些主要的常量
ZBX_MAPPING = {
"EVENT_SOURCE_TRIGGERS": 0,
"EVENT_OBJECT_TRIGGER": 0,
"TRIGGER_VALUE_FALSE": 0,
"SEC_PER_DAY": 86400,
"TRIGGER_VALUE_TRUE": 1,
"TRIGGER_VALUE_TRUE": 0
常用基础操作
获取主机ID
# hostname 主机名
def get_host_id(self, hostname):
sql = """
SELECT hg.hostid from hosts_groups hg,hosts h
WHERE h.hostid=hg.hostid and h.HOST='%s' limit 1;
""" % hostname
获取主机TriggerID
# hostid 主机ID
# triggername 触发器名
def get_trigger_id(self, hostid, triggername):
sql = """
SELECT DISTINCT
t.triggerid
FROM
triggers t,functions f,items i
WHERE
i.hostid='%s'
AND f.triggerid=t.triggerid
AND f.itemid=i.itemid
AND t.description like "%%%s%%"
""" % (hostid, triggername)
获取主机Item ID
# hostid 主机ID
# item item名
def get_item_id(self, hostid, item):
sql = """
SELECT DISTINCT
i.itemid
FROM
items i
WHERE
i.hostid = "%s"
AND i.key_ = "%s"
""" % (hostid, item)
获取ItemID的类型
# itemid itemID
def get_itemid_type(self, itemid):
sql = "SELECT i.value_type FROM items i WHERE i.itemid='%s'" % itemid
获取关闭的Triger(适用于巡检)
def get_close_trigger(self):
sql = """
SELECT
`triggers`.description,
`hosts`.`host`
FROM
`triggers`
INNER JOIN functions ON functions.triggerid = `triggers`.triggerid
INNER JOIN items ON functions.itemid = items.itemid
INNER JOIN `hosts` ON items.hostid = `hosts`.hostid
WHERE
`triggers`.`status` <> 0
"""
获取状态异常的监控项(适用于巡检)
def get_not_supported(self):
sql = """
SELECT
i.name,i.error,h.host
FROM
items i ,hosts h
WHERE
h.hostid = i.hostid and i.error != "" ;
"""
获取关闭的告警器(适用于巡检)
def get_close_trigger(self):
sql = """
SELECT
`triggers`.description,
`hosts`.`host`
FROM
`triggers`
INNER JOIN functions ON functions.triggerid = `triggers`.triggerid
INNER JOIN items ON functions.itemid = items.itemid
INNER JOIN `hosts` ON items.hostid = `hosts`.hostid
WHERE
`triggers`.`status` <> 0
"""
组合操作
获取主机Item历史记录
# self.sql_query为SQL查询器
def get_host_item_history(self, hostname, item, startTime=int(time()) - 60 * 15, endTime=int(time())):
hostid = self.get_host_id(hostname)
# 获取主机id
if not hostid[0]:
return hostid
hostid = hostid[1]
# 获取itemID
itemid = self.get_host_item(hostid, item)
if not itemid[0]:
return itemid
itemid = itemid[1][0]["itemid"]
# 获取itemType
itemtype = self.get_itemid_type(itemid)
if not itemtype[0]:
return itemtype
# 历史数据表对应关系
tables_name = self.ZBX_HISTORY_TABLES[itemtype[1]]
# 获取历史数据
sql = """
SELECT
h.clock,
h.value
FROM
%s h
WHERE
h.itemid = '%s'
AND h.clock >= '%s'
AND h.clock <= '%s';
""" % (tables_name, itemid, startTime, endTime)
result = self.sql_query(sql)
进阶操作
获取Trigger可用率
def get_trigger_availability(self, hostname, trigger, startTime=0, endTime=0):
get_host_id = self.get_host_id(hostname)
if not get_host_id[0]:
return get_host_id
get_trigger_id = self.get_trigger_id(get_host_id[1], trigger)
if not get_trigger_id:
return get_trigger_id
calculateAvailability = self.calculateAvailability(
get_trigger_id[1], startTime, endTime)
return calculateAvailability
# 这一段根据ZabbixWeb PHP源码改写Python版
def calculateAvailability(self, triggerid, startTime=0, endTime=0):
'''
@description: ZABBIX可用率计算(照搬源码)
@param {triggerid} 触发器ID
@param {startTime} 开始时间
@param {endTime} 结束时间
@return: (True/False, Data)
'''
startTime = int(startTime)
endTime = int(endTime)
startValue = self.ZBX_MAPPING["TRIGGER_VALUE_FALSE"]
minValue = None
if startTime > 0 and startTime <= int(time()):
__sql = """
SELECT
e.eventid,e.value
FROM
events e
WHERE
e.objectid=%s
AND e.source=%s
AND e.object=%s
AND e.clock<%s
ORDER BY e.eventid DESC
LIMIT 1
OFFSET 0
""" % (triggerid,
self.ZBX_MAPPING["EVENT_SOURCE_TRIGGERS"],
self.ZBX_MAPPING["EVENT_OBJECT_TRIGGER"],
startTime)
result = self.sql_query(__sql)
logger.debug(__sql)
if result:
startValue = result[0]['value']
minValue = startTime
__sql = """
SELECT
COUNT(e.eventid) AS cnt,
MIN(e.clock) AS min_clock,
MAX(e.clock) AS max_clock
FROM
events e
WHERE
e.objectid=%s
AND e.source=%s
AND e.object=%s
""" % (triggerid,
self.ZBX_MAPPING["EVENT_SOURCE_TRIGGERS"],
self.ZBX_MAPPING["EVENT_OBJECT_TRIGGER"])
if startTime:
__sql += "AND e.clock>=%s " % startTime
if endTime:
__sql += " AND e.clock<=%s " % endTime
dbEvent = self.sql_query(__sql)
logger.debug(__sql)
if dbEvent[0]['cnt'] > 0:
if minValue is not None:
minValue = dbEvent[0]['min_clock']
maxValue = dbEvent[0]['max_clock']
else:
if startTime == 0 and endTime == 0:
maxValue = int(time())
minValue = maxValue - self.ZBX_MAPPING["SEC_PER_DAY"]
else:
resultDict = {
'true_time': 0,
'false_time': 0,
}
resultDict['true'] = 100 if self.ZBX_MAPPING["TRIGGER_VALUE_TRUE"] == startValue else 0
resultDict['false'] = 100 if self.ZBX_MAPPING["TRIGGER_VALUE_FALSE"] == startValue else 0
return True, resultDict
State = startValue
Time = minValue
True_time = 0
False_time = 0
if startTime == 0 and endTime == 0:
maxValue = int(time())
if endTime == 0:
endTime = maxValue
rows = 0
dbEvents = self.sql_query("""
SELECT
e.eventid,
e.clock,
e.value
FROM
events e
WHERE
e.objectid=%s
AND e.source=%s
AND e.object=%s
AND e.clock BETWEEN %s AND %s
ORDER BY e.eventid
""" % (triggerid,
self.ZBX_MAPPING["EVENT_SOURCE_TRIGGERS"],
self.ZBX_MAPPING["EVENT_OBJECT_TRIGGER"],
minValue, maxValue))
if dbEvents:
for row in dbEvents:
clock = row["clock"]
value = row["value"]
diff = max(int(clock) - int(Time), 0)
Time = clock
if State == 0:
False_time += diff
State = value
elif State == 1:
True_time += diff
State = value
if State == self.ZBX_MAPPING["TRIGGER_VALUE_FALSE"]:
False_time = False_time + endTime - Time
elif State == self.ZBX_MAPPING["TRIGGER_VALUE_TRUE"]:
True_time = True_time + endTime - Time
Total_time = True_time + False_time
if Total_time == 0:
resultDict = {
'true_time': 0,
'false_time': 0,
'true': 0,
'false': 0
}
else:
resultDict = {
'true_time': True_time,
'false_time': False_time,
'true': "%.4f" % ((100 * True_time) / Total_time),
'false': "%.4f" % ((100 * False_time) / Total_time)
}
return True, resultDict
return False, "dbEvents Query error!"