def save_recover_info(results): """ save recover info :param results: :return: """ sqls = [] for item in results: if item['port'] is None or (str(item['port']) == ''): item['port'] = 0 sql = INSERT_WARNING_HISTORY % ( get_uuid_str(), item['sys_id'], item['sys_name'], item['description'], item['type'], item['ip'], str(item['port']), item['start_time'], item['check_result'], '', '异常恢复', str(item['interval'])) sqls.append(sql) DB_POOL.execute_sql_array(sqls)
def delete_instance(json_obj): """ insert or update instance :param json_obj: :return: """ sql = DELETE_MONITOR_INSTANCE % (TypeChange.to_string(json_obj, 'id')) DB_POOL.execute_sql_str(sql) return {'is_success': True}
def delete_system_info(json_obj): """ insert or update instance :param json_obj: :return: """ sql = DELETE_SYSTEM_INFO % (TypeChange.to_string(json_obj, 'id')) DB_POOL.execute_sql_str(sql) return {'is_success': True}
def __update_system_info(json_obj): """ UPDATE MONITOR INSTANCE :param json_obj: :return: """ sql = UPDATE_SYSTEM_INFO % ( TypeChange.to_string(json_obj, 'id'), TypeChange.to_string(json_obj, 'name'), TypeChange.to_string(json_obj, 'manager'), TypeChange.to_string(json_obj, 'telephone'), TypeChange.to_string( json_obj, 'job_number'), TypeChange.to_string(json_obj, 'id')) DB_POOL.execute_sql_str(sql)
def __insert_system_info(json_obj): """ insert monitor instance :param json_obj: :return: """ sql = INSERT_SYSTEM_INFO % ( get_uuid_str(), TypeChange.to_string(json_obj, 'name'), TypeChange.to_string(json_obj, 'manager'), TypeChange.to_string(json_obj, 'telephone'), TypeChange.to_string(json_obj, 'job_number'), ) DB_POOL.execute_sql_str(sql)
def query_instance_by_condition(sys_id=None, sys_name=None, ip=None, check_type=None, description=None): """ query instance by condition :param description: :param sys_id: :param sys_name: :param ip: :param check_type: :return: """ base_sql = QUERY_MONITOR_INSTANCE if sys_id is not None and len(sys_id) > 0: base_sql = base_sql + """and sys_id = '%s'""" % sys_id if sys_name is not None and len(sys_name) > 0: base_sql = base_sql + "and sys_name like '%" + sys_name + "%'" if ip is not None and len(ip) > 0: base_sql = base_sql + "and ip like '%" + ip + "%'" if check_type is not None and len(check_type) > 0: base_sql = base_sql + "and `type` like '%" + check_type + "%'" if description is not None and len(description) > 0: base_sql = base_sql + "and `description` like '%" + description + "%'" base_sql = base_sql + 'order by sys_name limit 200;' result = DB_POOL.select(sql=base_sql) return result
def query_warning_history_by_condition(sys_id=None, sys_name=None, ip=None, check_type=None): """ query instance by condition :param sys_id: :param sys_name: :param ip: :param check_type: :return: """ base_sql = SELECT_WARNING_HISTORY_BASE if sys_id is not None and len(sys_id) > 0: base_sql = base_sql + """and sys_id = '%s'""" % sys_id if sys_name is not None and len(sys_name) > 0: base_sql = base_sql + "and sys_name like '%" + sys_name + "%'" if ip is not None and len(ip) > 0: base_sql = base_sql + "and ip like '%" + ip + "%'" if check_type is not None and len(check_type) > 0: base_sql = base_sql + "and `check_type` like '%" + check_type + "%'" base_sql = base_sql + 'ORDER BY check_time DESC limit 200;' print(base_sql) result = DB_POOL.select(sql=base_sql) for res in result: res['check_time'] = str(res['check_time']) if res['check_type'] == 'half_connection': res['check_type'] = '半连接' return result
def get_system_info_tree(): """ get system info tree :return: """ system_info = DB_POOL.select(SELECT_SYSTEM_INFO) half_connection = [] ping = [] for sys in system_info: half_connection.append({ 'id': sys['id'], 'name': sys['name'], 'type': '半连接' }) ping.append({'id': sys['id'], 'name': sys['name'], 'type': 'ping'}) return_data = { 'results': { 'data': [{ 'id': 1, 'name': '半连接', 'children': half_connection }, { 'id': 2, 'name': 'ping', 'children': ping }] } } return return_data
def __insert_monitor_instance(json_obj): """ insert monitor instance :param json_obj: `id`,`sys_id`,`sys_name`,`description`,`ip`,`port`,`type`,`interval`,`is_use` :return: """ sql = INSERT_MONITOR_INSTANCE % ( get_uuid_str(), TypeChange.to_string(json_obj, 'sys_id'), TypeChange.to_string(json_obj, 'sys_name'), TypeChange.to_string( json_obj, 'description'), TypeChange.to_string( json_obj, 'ip'), TypeChange.to_string(json_obj, 'port'), TypeChange.to_string( json_obj, 'type'), TypeChange.to_string(json_obj, 'interval'), TypeChange.to_string(json_obj, 'is_use')) DB_POOL.execute_sql_str(sql)
def get_all_used_check_instances(): """ get all used check instances :return: """ base_sql = QUERY_MONITOR_INSTANCE result = DB_POOL.select(sql=base_sql) return result
def get_system_name_by_id(sys_id): """ get system name by id :param sys_id: :return: """ sql = SELECT_BASE_SYSTEM_INFO + """ and id ='%s'""" % sys_id sys_name = DB_POOL.select(sql)[0]['name'] return sys_name
def __update_monitor_instance(json_obj): """ UPDATE MONITOR INSTANCE :param json_obj: :return: """ sys_name = SystemInfoService.get_system_name_by_id(json_obj['sys_id']) sql = UPDATE_MONITOR_INSTANCE % ( TypeChange.to_string(json_obj, 'id'), TypeChange.to_string(json_obj, 'sys_id'), sys_name, TypeChange.to_string( json_obj, 'description'), TypeChange.to_string( json_obj, 'ip'), TypeChange.to_int(json_obj, 'port'), TypeChange.to_string( json_obj, 'type'), TypeChange.to_int(json_obj, 'interval'), TypeChange.to_int(json_obj, 'is_use'), TypeChange.to_string(json_obj, 'id')) DB_POOL.execute_sql_str(sql)
def get_ping_last_state(item): """ get ping last state :param item: :return: """ ip = item.ip sql = SELECT_WARNING_HISTORY_BASE + """ and check_type = 'ping' and ip = '%s' order by check_time desc;""" % ip res = DB_POOL.select(sql) return res
def save_check_result_to_detail(results): """ save check info to mysql :param results: :return: """ sqls = [] for result in results: if result.interval is None: result.interval = 0 if result.end_time is None: result.end_time = time.time() if result.port is None: result.port = 0 sql = INSERT_PING_TO_MONITOR_DETAIL. \ format(pymysql.escape_string(get_uuid_str()), pymysql.escape_string(result.ip), result.port, pymysql.escape_string(result.check_type), pymysql.escape_string(str(result.is_success)), pymysql.escape_string(TypeChange.date_stamp_to_datetime(result.start_time)), pymysql.escape_string(TypeChange.date_stamp_to_datetime(result.end_time)), pymysql.escape_string(str(result.interval))) sqls.append(sql) DB_POOL.execute_sql_array(sqls)
def gen_obj_mapping(): """ get obj mapping :return: """ column_info = DB_POOL.select(GET_COLUMN_INFO) content = {} for info in column_info: table_name = info['TABLE_NAME'] column_name = info['COLUMN_NAME'] if table_name not in content.keys(): content[table_name] = [column_name] else: content[table_name].append(column_name) gen_py_file(content)
def save_items(items): """ save items :param items: :return: """ sqls = [] for item in items: if item.port is None: item.port = 0 sql = INSERT_WARNING_HISTORY % ( get_uuid_str(), item.sys_id, item.sys_name, item.description, item.check_type, item.ip, item.port, TypeChange.date_stamp_to_datetime(item.start_time), item.is_success, item.comment, item.warning_type, item.interval) sqls.append(sql) DB_POOL.execute_sql_array(sqls)
def update_monitor_visualization_db(result): """ :param result: :return: """ sqls = [] for item in result: visual = MONITOR_VISUALIZATION() visual.unique_identify = item.check_type + '_' + item.ip + '_' + str( item.port) visual.check_type = item.check_type visual.ip = item.ip visual.check_result = item.is_success visual.port = item.port visual.start_time = item.start_time if item.is_success: visual.interval = item.interval sql, query_results = MonitorVisualizationService. \ get_insert_or_update_visual(visual) WarningOperate.check_and_send_recover_info(visual, query_results) sqls.append(sql) if len(sqls) > 0: DB_POOL.execute_sql_array(sqls)
def query_sys_info(json_object): """ query sys info :param json_object: :return: """ sys_name, manager, job_number = json_object.get('name'), \ json_object.get('manager'), \ json_object.get('job_number') base_sql = QUERY_SYS_INFO if sys_name is not None and len(sys_name) > 0: base_sql = base_sql + "and name like '%" + sys_name + "%'" if manager is not None and len(manager) > 0: base_sql = base_sql + "and manager like '%" + manager + "%'" if job_number is not None and len(job_number) > 0: base_sql = base_sql + "and job_number like '%" + job_number + "%'" base_sql = base_sql + 'order by name limit 200;' result = DB_POOL.select(sql=base_sql) return result
def get_monitor_history_data(json_obj): """ get monitor history data :param json_obj: :return: """ ip = json_obj['ip'] check_type = json_obj['check_type'] port = 0 if 'port' in json_obj: port = str(json_obj['port']) start_time = json_obj['start_time'].replace('T', ' ').split('.')[0] end_time = json_obj['end_time'].replace('T', ' ').split('.')[0] if check_type == 'half_connection': sql = QUERY_MONITOR_DETAIL_HALF_CONNECTION % (start_time, end_time, ip, port, check_type) else: sql = QUERY_MONITOR_DETAIL_PING % (start_time, end_time, ip, check_type) sql = sql + ' order by start_time asc' print(sql) res = DB_POOL.select(sql) filter_monitor_result = MonitorDetailService.__filter_monitor_detail(res) return filter_monitor_result
from common.utility.uuid_utility import get_uuid_str from db.mysql_relevant.connection_pool.db_pool import DB_POOL from db.mysql_relevant.sql_str.monitor_instance import INSERT_MONITOR_INSTANCE sys_id = 'f6f7aa8239a24499962ee7f45a763bc5' sys_name = '综合运营平台' with open('1.csv', 'r', encoding='utf8') as f: reader = csv.reader(f) print(type(reader)) for row in reader: description = str(row[0]).replace(' ', '') ip = row[1].replace(' ', '') ports = row[2].replace(' ', '') ports = str(ports).split('/') for port in ports: if len(port) < 2: continue sql = INSERT_MONITOR_INSTANCE % (get_uuid_str(), sys_id, sys_name, description, ip, str(port), '半连接', '60', '1') print(sql) DB_POOL.execute_sql_str(sql) print(row)
def get_tcp_last_state(item): ip = item.ip sql = SELECT_WARNING_HISTORY_BASE + """ and check_type = 'half_connection' and ip = '%s' order by check_time desc;""" % ip res = DB_POOL.select(sql) return res