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