[MySQL] zabbix企业应用之如何快速查看异常流量

#!/usr/bin/env python
#-*- coding: utf-8 -*-
#author:Deng Lei
#email: dl528888@gmail.com
import MySQLdb
import time
import sys
import xlwt
reload(sys)
sys.setdefaultencoding('utf8')
if __name__ == "__main__":
  now_hour=int(time.strftime('%H'))
  old_hour=now_hour-1
  now_time=time.strftime('%Y-%m-%d')
  mysql_conn=MySQLdb.connect(host='10.10.14.11',user='zabbix',passwd='zabbix',port=3306,charset="utf8")
  mysql_cur=mysql_conn.cursor()
  mysql_conn.select_db('zabbix')
  last_results=[]
  in_results=[]
  out_results=[]
  network_device=['em2','eth1','eth0']
  try:
    room=sys.argv[1]
  except IndexError:
room='all'
  try:
old_hour=sys.argv[2]
  except IndexError:
old_hour=now_hour-1
  try:
    now_hour=sys.argv[3]
  except IndexError:
    now_hour=int(time.strftime('%H'))
  if room == 'all':
    #search network in traffic
    for i in network_device:
      search_sql="select from_unixtime(hi.clock,'%%Y-%%m-%%d %%T') as Date,g.name as Group_Name,h.host as Host,round(max(hi.value_max)/1000,0) as Network  from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on  i.itemid = hi.itemid  where  i.key_='net.if.in[%s]' and  hi.clock >= UNIX_TIMESTAMP('%s %s:00:00') and  hi.clock < UNIX_TIMESTAMP('%s %s:00:00') group by h.host;"%(i,now_time,old_hour,now_time,now_hour)
      n=mysql_cur.execute(search_sql)
      result=mysql_cur.fetchall()
      for ii in result:
    msg1={'Group_Name':ii[1],'Host':ii[2],'Network_device':i,'Source':'In'}
    if msg1 not in in_results:
  in_results.append(msg1)
      msg={'Date':ii[0],'Group_Name':ii[1],'Host':ii[2],'Network':float(ii[3]),'Network_device':i,'Source':'In'}
      last_results.append(msg)
    #search network out traffic
    for i in network_device:
      search_sql="select from_unixtime(hi.clock,'%%Y-%%m-%%d %%T') as Date,g.name as Group_Name,h.host as Host,round(max(hi.value_max)/1000,0) as Network  from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on  i.itemid = hi.itemid  where  i.key_='net.if.out[%s]' and  hi.clock >= UNIX_TIMESTAMP('%s %s:00:00') and  hi.clock < UNIX_TIMESTAMP('%s %s:00:00') group by h.host;"%(i,now_time,old_hour,now_time,now_hour)
      n=mysql_cur.execute(search_sql)
      result=mysql_cur.fetchall()
      for ii in result:
        msg1={'Group_Name':ii[1],'Host':ii[2],'Network_device':i,'Source':'out'}
        if msg1 not in out_results:
          out_results.append(msg1)
          msg={'Date':ii[0],'Group_Name':ii[1],'Host':ii[2],'Network':float(ii[3]),'Network_device':i,'Source':'out'}
          last_results.append(msg)
  else:
    #search network in traffic
    for i in network_device:
      search_sql="select from_unixtime(hi.clock,'%%Y-%%m-%%d %%T') as Date,g.name as Group_Name,h.host as Host,round(max(hi.value_max)/1000,0) as Network  from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on  i.itemid = hi.itemid  where  i.key_='net.if.in[%s]' and g.name like '%s' and hi.clock >= UNIX_TIMESTAMP('%s %s:00:00') and  hi.clock < UNIX_TIMESTAMP('%s %s:00:00') group by h.host;"%(i,room+"%",now_time,old_hour,now_time,now_hour)
      n=mysql_cur.execute(search_sql)
      result=mysql_cur.fetchall()
      for ii in result:
        msg1={'Group_Name':ii[1],'Host':ii[2],'Network_device':i,'Source':'In'}
        if msg1 not in in_results:
          in_results.append(msg1)
          msg={'Date':ii[0],'Group_Name':ii[1],'Host':ii[2],'Network':float(ii[3]),'Network_device':i,'Source':'In'}
          last_results.append(msg)
    #search network out traffic
    for i in network_device:
      search_sql="select from_unixtime(hi.clock,'%%Y-%%m-%%d %%T') as Date,g.name as Group_Name,h.host as Host,round(max(hi.value_max)/1000,0) as Network  from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on  i.itemid = hi.itemid  where  i.key_='net.if.out[%s]' and g.name like '%s' and hi.clock >= UNIX_TIMESTAMP('%s %s:00:00') and  hi.clock < UNIX_TIMESTAMP('%s %s:00:00') group by h.host;"%(i,room+"%",now_time,old_hour,now_time,now_hour)
      n=mysql_cur.execute(search_sql)
      result=mysql_cur.fetchall()
      for ii in result:
        msg1={'Group_Name':ii[1],'Host':ii[2],'Network_device':i,'Source':'out'}
        if msg1 not in out_results:
          out_results.append(msg1)
          msg={'Date':ii[0],'Group_Name':ii[1],'Host':ii[2],'Network':float(ii[3]),'Network_device':i,'Source':'out'}
          last_results.append(msg)
  time="%s-[%s-%s]"%(now_time,old_hour,now_hour)
  a=[]
  for i in last_results:
if i['Network'] >=10000:
  msg=(i['Group_Name'],i['Host'],i['Network']/1000,i['Network_device'],i['Source'])
  a.append(msg)
  sort_list=sorted(a,key=lambda d:d[2],reverse = True)
  wb = xlwt.Workbook()
  ws = wb.add_sheet('zabbix', cell_overwrite_ok=True)
  ws.write(0,0,'报警组'.decode("utf-8"))
  ws.write(0,1,'主机'.decode("utf-8"))
  ws.write(0,2,'流量(Mbps)'.decode("utf-8"))
  ws.write(0,3,'网卡名'.decode("utf-8"))
  ws.write(0,4,'方向'.decode("utf-8"))
  for i in range(1,len(sort_list)+1):
    for ii in range(0,len(sort_list[i-1])):
      ws.write(i,ii,sort_list[i-1][ii])
  ws.col(0).width = 3333*3
  ws.col(1).width = 3333
  wb.save('/tmp/zabbix_network_traffic-%s.xls'%time)
  mysql_cur.close()
  mysql_conn.close()

python check_zabbix_network_traffic.py 'A' 09 13

network_device=['em2','eth1','eth0']