Mysql data is extracted and cleaned into standard format by python, and then stored in MSSql data.

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 ()