- from pymongo import MongoClient
- from pymysql import connect
- import pymssql
- from datetime import datetime, timedelta
- import time
Nagios 数据库的 IP 地址
NagiosDB_IP = ["10.1.1.44", "10.1.1.37"]
用于数据展示的 BI 中间数据库地址及账号信息
- BIDBServer='10.1.1.65'
- BI_NagiosDB='NagiosInfo'
- NagiosDB='nagios'
- BIDB_User='sa'
- BIDBPassword='123456!a'
- NagiosDB_User='report'
- NagiosDBPassword='report!a'
链接中间数据库, 该库用于存放 Nagios 性能数据
Mssqlconn=pymssql.connect(host=BIDBServer,database=BI_NagiosDB,user=BIDB_User,password=BIDBPassword)
查询交换机端口状态信息
- Querys_SwitchPortStatus = """SELECT NSS.service_object_id,NSS.status_update_time,NSS.output,NS.display_name,NH.display_name, NH.address ,NS.host_object_id, NSS.check_command from nagios_servicestatus NSS
- LEFT join
- nagios_services NS on NSS.service_object_id = NS.service_object_id
- LEFT join
- nagios_hosts NH on NS.host_object_id = NH.host_object_id
- where (check_command LIKE 'check_snmp_free_interfaces%' or check_command LIKE 'check_snmp_usage_interfaces%')
- ORDER BY
- NH.display_name"""
- 查询交换机基础信息
- Querys_SwitchInfo = """SELECT NSS.service_object_id,NSS.status_update_time,NSS.output,NS.display_name,NH.display_name, NH.address ,NS.host_object_id, NSS.check_command from nagios_servicestatus NSS
- LEFT join
- nagios_services NS on NSS.service_object_id = NS.service_object_id
- LEFT join
- nagios_hosts NH on NS.host_object_id = NH.host_object_id
- where (check_command LIKE 'snmp_cisco_info%' AND NS.display_name LIKE '1.1.2-Network Access Switch Info%')
- ORDER BY
- NH.display_name
- """
- 查询主机状态信息
- Querys_HostStatus = """select host_object_id,status_update_time,output from nagios_hoststatus WHERE check_command LIKE'check-host-alive%'"""Mssqlconn=pymssql.connect(host='.',database='NagiosInfo',user='sa',password='123456!a')
- Mssqlcur=Mssqlconn.cursor()
- def Port_Status_DataClean():
- start=time.strftime("%Y%m%d",(datetime.now()-timedelta(1)).timetuple())
- # end=time.strftime("%Y%m%d",datetime.now().timetuple())
- # t1=datetime.now()
- Port_Status= 'Unknown'
- for i in range(len(NagiosDB_IP)):
- conn=connect(NagiosDB_IP[i], NagiosDB_User, NagiosDBPassword, NagiosDB, charset='utf8')
- cursor=conn.cursor()
- cursor.execute(Querys_SwitchPortStatus)
- for row in cursor:
- # print(row)
- record= {}
- record['Object_ID']=row[0]
- record['Record_Time']=row[1]
- record['Service_Name']=row[3]
- record['Host_Name']=row[4]
- record['IP_Addr']=row[5]
- record['Host_ID']=row[6]
- #record['Check_Command']=row[7]
- if row[7].split('!')[0]== 'check_snmp_free_interfaces':
- Port_Status='Free'
- elif row[7].split('!')[0]=='check_snmp_usage_interfaces':
- Port_Status='Usage'
- try:
- for Port in row[2].split('(0')[1].strip(')').split():
- record["Port_Status"]= Port_Status
- record['Port']=Port
- Mssqlcur.execute("insert into ASwitchPortStatus values ('%s','%s','%s','%s','%s','%s','%s','%s')"%(record['Object_ID'],record['Record_Time'],record['Service_Name'],record['Host_Name'],record['IP_Addr'],record['Host_ID'],record["Port_Status"],record['Port']) )
- except:
- # print(row[2])
- continue
- Mssqlcur.connection.commit()
- conn.close()
- def SwitchInfo_DataClean():
- start=time.strftime("%Y%m%d",(datetime.now()-timedelta(1)).timetuple())
- # end=time.strftime("%Y%m%d",datetime.now().timetuple())
- # t1=datetime.now()
- for i in range(len(NagiosDB_IP)):
- conn = connect(NagiosDB_IP[i], "client", "client!a", "nagios", charset='utf8')
- cursor = conn.cursor()
- cursor.execute(Querys_SwitchInfo)
- for row in cursor:
- record = {}
- record['Object_ID'] = row[0]
- record['Record_Time'] = row[1]
- record['Service_Name'] = row[3]
- record['Host_Name'] = row[4]
- record['IP_Addr'] = row[5]
- record['Host_ID'] = row[6]
- try:
- record['Host_Info_Name']=row[2].split(',<br>')[0]
- record['Host_Info_Model'] = row[2].split(',<br>')[1]
- record['Host_Info_Version']=row[2].split(',<br>')[2]
- record['Host_Info_SN'] = row[2].split(',<br>')[3]
- record['Host_Info_location'] = row[2].split(',<br>')[4]
- record['Host_Info_Contact'] = row[2].split(',<br>')[5]
- Mssqlcur.execute("insert into ASwitchInfo values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(record['Object_ID'], record['Record_Time'], record['Service_Name'], record['Host_Name'],record['IP_Addr'], record['Host_ID'], record['Host_Info_Name'], record['Host_Info_Model'],record['Host_Info_Version'], record['Host_Info_SN'], record['Host_Info_location'],record['Host_Info_Contact']))
- except:
- continue
- Mssqlcur.connection.commit()
- conn.close()
- def HostStatus_DataClean():
- start=time.strftime("%Y%m%d",(datetime.now()-timedelta(1)).timetuple())
- # end=time.strftime("%Y%m%d",datetime.now().timetuple())
- # t1=datetime.now()
- for i in range(len(NagiosDB_IP)):
- conn = connect(NagiosDB_IP[i], "client", "client!a", "nagios", charset='utf8')
- cursor = conn.cursor()
- cursor.execute(Querys_HostStatus)
- for row in cursor:
- record = {}
- record['Host_ID'] = row[0]
- record['Record_Time'] = row[1]
- try:
- if row[2].split('-')[0]== 'OK':
- record['Host_Status']='UP'
- elif row[2].split('-')[0]== 'CRITICAL':
- record['Host_Status'] = 'Down'
- else:
- record['Host_Status'] = '#N/A'
- except:
- continue
- Mssqlcur.execute("insert into HostStatus values('%s','%s','%s')" %(record['Host_ID'], record['Record_Time'], record['Host_Status']))
- print(record)
- Mssqlcur.connection.commit()
- conn.close()
- if name=="main":
- Port_Status_DataClean()
- SwitchInfo_DataClean()
- HostStatus_DataClean()
来源: http://www.bubuko.com/infodetail-2849488.html