zabbix监控的主机数量与主机名

#!/usr/bin/env python2.7
#encoding:utf-8
#__author__ = 'zzling'
import MySQLdb
import time,datetime
import os,sys
import urllib,urllib2
import cookielib
import smtplib
from prettytable import PrettyTable 
from email.header import Header
from email.MIMEText import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart

zabbix_host = 'ip'
username = 'admin'
password = 'pass'
width = 1100
height = 250
period = 86400
EMAIL_DOMAIN = '163.com'
EMAIL_USERNAME = 'name'
EMAIL_PASSWORD = '密码'

conn =MySQLdb.connect(host='localhost',user='user',passwd='passwd',port=端口,db='zabbixdb')
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

def get_last_day():
    today = datetime.date.today()
    today_time = str(time.mktime(today.timetuple()))
    oneday = datetime.timedelta(days=1) 
    yesterday = today-oneday
    yesterday_time = str(time.mktime(yesterday.timetuple()))
    today_timStamp = today_time.split('.',2)[0]
    yesterday_timeStamp = yesterday_time.split('.',2)[0]
    today_time = int(today_timStamp)
    today_timeArray = time.localtime(today_time)
    today_StyleTime = time.strftime("%Y%m%d",today_timeArray)
    yesterday_time = int(yesterday_timeStamp)
    yesterday_timeArray = time.localtime(yesterday_time)
    yesterday_StyleTime = time.strftime("%Y%m%d",yesterday_timeArray)
    return today_timStamp,yesterday_timeStamp,yesterday_StyleTime,today_StyleTime

def get_hostid():
    sql='''select hostid from hosts where host like 'aggregate_all'; '''
    cursor.execute(sql)
    hostid = cursor.fetchall()
    return hostid

def get_itemid():
    getid = get_hostid()
    hostid = getid[0].get('hostid')
    sql='''select itemid,name from items where hostid = %s;''' % hostid
    cursor.execute(sql)
    itemid = cursor.fetchall()
    num = len(itemid)
    itemid_list = []
    for i in range(num):
        every_itemid = itemid[i].get('itemid')
        itemid_list.append(every_itemid)
    return itemid_list,itemid

def get_graphid():
    itemids = get_itemid()[0]
    num = len(itemids)
    graphid_list = []
    for i in range(num):
       itemid = itemids[i]
       sql = '''select graphid from graphs_items where itemid = %s;''' % itemid
       cursor.execute(sql)
       data = cursor.fetchall()
       nums = len(data)
       for i in range(nums):
           graphid = data[i].get('graphid')
           graphid_list.append(graphid)
    ids = list(set(graphid_list))
    return ids

def get_data():
    time_cur = get_last_day()
    today_time = time_cur[0]
    ytd_time = time_cur[1]
    itemids = get_itemid()[0]
    name = get_itemid()[1]
    num = len(itemids)
    data_dict = {}
    for i in range(num):
        nameserver = name[i]['name']
    every_id = itemids[i]
        sql='''SELECT clock,min(value),avg(value),max(value) FROM history_uint WHERE itemid=%s and clock > %s and clock < %s''' % (every_id,ytd_time,today_time)
        cursor.execute(sql)
        data = cursor.fetchall()
        data_dict[nameserver] = data
    cursor.close()
    conn.close()
    return data_dict

#def format_output():
#    data = get_data()
#    yesterday_date = get_last_day()[2]
#    today_date = get_last_day()[3]
#    date=yesterday_date + "~" + today_date
#    formats = PrettyTable(["date","items", "min", "avg", "max"])
#    formats.align["item"] = "l"
#    formats.padding_width = 1
#    for k in data:
#        value = data[k][0]
#        if 'traffic' in k:
#       maxs = "%.2f" % float(value.get("max(value)")/float(1000000))
#           mins = "%.2f" % float(value.get("min(value)")/float(1000000))
#           avgs = "%.2f" % float(float(value.get("avg(value)"))/float(1000000))
#       print date,k,maxs,mins,avgs
#           formats.add_row([date,k,mins,avgs,maxs])
#        else:
#           maxs = "%.2f" % float(value.get("max(value)")/float(1000))
#           mins = "%.2f" % float(value.get("min(value)")/float(1000))
#           avgs = "%.2f" % float(float(value.get("avg(value)"))/float(1000))           
#           print date,k,maxs,mins,avgs
#           formats.add_row([date,k,mins,avgs,maxs])
#    return formats


def handler_data():
    data = get_data()
    final_data = {}
    for k in data:
        value = data[k][0]
        if 'traffic' in k:
           maxs = "%.2f" % float(value.get("max(value)")/float(1000000)) + "M"
           mins = "%.2f" % float(value.get("min(value)")/float(1000000)) + "M"
           avgs = "%.2f" % float(float(value.get("avg(value)"))/float(1000000)) + "M"
           final_data[k]=mins,avgs,maxs
        else:
           maxs = "%.2f" % float(value.get("max(value)")/float(1000)) + "K"
           mins = "%.2f" % float(value.get("min(value)")/float(1000)) + "K"
           avgs = "%.2f" % float(float(value.get("avg(value)"))/float(1000)) + "K"          
           final_data[k]=mins,avgs,maxs
    return final_data

def format_data():
    dicts_data = handler_data()
    list_items = []
    MINS = []
    AVGS = []
    MAXS = []
    for i in range(len(dicts_data)):
        list_items.append(dicts_data.keys()[i])
        MINS.append(list(dicts_data.get(dicts_data.keys()[i]))[0])
        AVGS.append(list(dicts_data.get(dicts_data.keys()[i]))[1])
        MAXS.append(list(dicts_data.get(dicts_data.keys()[i]))[2])
    return list_items,MINS,AVGS,MAXS
        
stime = get_last_day()[2] + '000000'
curtime = get_last_day()[0] + '000' 
date_time = get_last_day()
title = date_time[2] + "~" + date_time[3]
graphid_list = get_graphid()
final_data = list(format_data())
items = final_data[0]
MIN = final_data[1]
AVG = final_data[2]
MAX = final_data[3]
def Save_Graph(zabbix_host,username,password,width,height,period,stime,curtime):
    for graphid in graphid_list:
        #print graphid
        login_opt = urllib.urlencode({
        "name": username,
        "password": password,
        "autologin": 1,
        "enter": "Sign in"})
        get_graph_opt = urllib.urlencode({
        "graphid": graphid,
        "screenid": '16',
        "width": width,
        "height": height,
        "period": period,
        "stime": stime,
        "curtime": curtime})
        cj = cookielib.CookieJar()
        opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
        login_url = r"http://%s/index.php"%zabbix_host
        save_graph_url = r"http://%s/chart2.php"%zabbix_host
        opener.open(login_url,login_opt).read()
        data = opener.open(save_graph_url,get_graph_opt).read()
        names = title + "." + str(graphid)
        filename = "/opt/get_report/pic/%s.jpeg"%str(names)
        f = open(filename,"wb")
        f.write(data)
        f.close()

def send_mail(to_list):
  me = 'Zabbix <%s@%s>' % (EMAIL_USERNAME, EMAIL_DOMAIN)
  def _create_msg():
    msg = MIMEMultipart('alternatvie')
    msg['Subject'] = '%s 节点会话数和流量负载汇总 '% title
    msg['From'] = me
    msg['To'] = ';'.join(to_list)
    zbxText = MIMEText("""
<html>
<body>
<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td align="center">会话数</td>
</tr>
<tr>
<td><img src="cid:image0"></td>
</tr>
</table>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td align="center">流量负载</td>
</tr>
<tr>
<td><img src="cid:image1"></td>
</tr>
</table>

<table border="1" width="100%">
<tr>
    <td width="300" height="40">date</td>
    <td width="300" height="40">items</td>
    <td width="300" height="40">min</td>
    <td width="300" height="40">avg</td>
    <td width="300" height="40">max</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item0}</td>
    <td height="40">{MIN0}</td>
    <td height="40">{AVG0}</td>
    <td height="40">{MAX0}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item1}</td>
    <td height="40">{MIN1}</td>
    <td height="40">{AVG1}</td>
    <td height="40">{MAX1}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item2}</td>
    <td height="40">{MIN2}</td>
    <td height="40">{AVG2}</td>
    <td height="40">{MAX2}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item3}</td>
    <td height="40">{MIN3}</td>
    <td height="40">{AVG3}</td>
    <td height="40">{MAX3}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item4}</td>
    <td height="40">{MIN4}</td>
    <td height="40">{AVG4}</td>
    <td height="40">{MAX4}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item5}</td>
    <td height="40">{MIN5}</td>
    <td height="40">{AVG5}</td>
    <td height="40">{MAX5}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item6}</td>
    <td height="40">{MIN6}</td>
    <td height="40">{AVG6}</td>
    <td height="40">{MAX6}</td>
</tr>
<tr>
    <td height="40">{date}</td>
    <td height="40">{item7}</td>
    <td height="40">{MIN7}</td>
    <td height="40">{AVG7}</td>
    <td height="40">{MAX7}</td>
</tr>
</table>
</body>
</html>
    """.format(date=title,item0=items[0],item1=items[1],item2=items[2],item3=items[3],item4=items[4],item5=items[5],item6=items[6],item7=items[7],MIN0=MIN[0],MIN1=MIN[1],MIN2=MIN[2],MIN3=MIN[3],MIN4=MIN[4],MIN5=MIN[5],MIN6=MIN[6],MIN7=MIN[7],AVG0=AVG[0],AVG1=AVG[1],AVG2=AVG[2],AVG3=AVG[3],AVG4=AVG[4],AVG5=AVG[5],AVG6=AVG[6],AVG7=AVG[7],MAX0=MAX[0],MAX1=MAX[1],MAX2=MAX[2],MAX3=MAX[3],MAX4=MAX[4],MAX5=MAX[5],MAX6=MAX[6],MAX7=MAX[7]),'html','utf-8')
    msg.attach(zbxText)
    
    for n in range(len(graphid_list)):
        names = title + "." + str(graphid_list[n])
        zbx_img = open(r'/opt/get_report/pic/%s.jpeg'%names, 'rb')
        msgImage = MIMEImage(zbx_img.read())
        zbx_img.close()
        msgImage.add_header('Content-ID', '<image%s>'%n)
        msg.attach(msgImage)
    return msg
  try:
    server = smtplib.SMTP()
    server.connect('smtp.%s' % EMAIL_DOMAIN)
    server.login('%s@%s' % (EMAIL_USERNAME, EMAIL_DOMAIN), EMAIL_PASSWORD)
    server.sendmail(me, to_list, _create_msg().as_string())
    server.close()
    print 'send mail Ok!'
  except Exception, e:
    print e
if __name__ == '__main__':
    Save_Graph(zabbix_host,username,password,width,height,period,stime,curtime)
    send_mail(['my mail'])

 获取报警信息：select clock,message from alerts where clock > 1479657600 and clock < 1479744000G;