Shulou(Shulou.com)06/03 Report--

From pymongo import MongoClient

From pymysql import connect

Import pymssql

From datetime import datetime, timedelta

Import time

IP address of the Nagios database

NagiosDB_IP = ["10.1.1.44", "10.1.1.37"]

BI intermediate database address and account information for data display

BIDBServer='10.1.1.65'

BI_NagiosDB='NagiosInfo'

NagiosDB='nagios'

BIDB_User='sa'

BIDBPasswordbirds 123456rooma'

NagiosDB_User='report'

Nagios DBPasswordcustomers reportholders a'

Link to the intermediate database, which is used to store Nagios performance data

Mssqlconn=pymssql.connect (host=BIDBServer,database=BI_NagiosDB,user=BIDB_User,password=BIDBPassword)

Query switch port status information

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 ""

Query the basic information of the switch

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

"

Query host status information

Querys_HostStatus = "select host_object_id,status_update_time,output from nagios_hoststatus WHERE check_command LIKE 'check-host-alive%'"

Mssqlconn=pymssql.connect (hostworthy records. Records database databases records NagiosInfoores database records NagiosInfocomm usernames databases saurs coach passwordbooks 123456passport 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') '% 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", "clientyoga", "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 ('

') [0] record [' Host_Info_Model'] = row [2] .split ('

') [1] record [' Host_Info_Version'] = row [2] .split ('

') [2] record [' Host_Info_SN'] = row [2] .split ('

') [3] record [' Host_Info_location'] = row [2] .split ('

') [4] record [' Host_Info_Contact'] = row [2] .split ('

') [5] Mssqlcur.execute ("insert into ASwitchInfo values ['% 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", "clientyoga", "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'] ='# Nmax A 'except: continue Mssqlcur.execute ("insert into HostStatus values ('% s') '% sails Mssqlcur.connection.commit% 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 ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.