def __init__(self): # 获取ram中所有用户名 self.data_formart = 'json' self.mysql_conn_dict = db_mysql.MyPymysqlPoolDict() file_path = os.path.join(os.path.dirname(__file__), "../../../conf/key.conf") self.cf = configparser.ConfigParser() self.cf.read(file_path, encoding='utf-8') self.ak_list_tablename = 'ak_list'
def show_task_schedule(request): table_name = 'task_scheduler' job_id = str(request.GET['job_id']) job_name = str(request.GET['job_name']) job_status = str(request.GET['job_status']) data_list = [] body_dict = {} result_dict = {} from src.lib import django_api django_api.DjangoApi().os_environ_update() mysql_conn = db_mysql.MyPymysqlPoolDict() code = 500 message = "fail" success = False sql = "select * from %s where job_id like '%%%s%%' and job_name like '%%%s%%' and " \ "job_status like '%%%s%%' order by last_schedule_starttime desc;" % \ (table_name, job_id, job_name, job_status) try: tmp_result = mysql_conn.select(sql) except Exception as e: print(e) else: if tmp_result: for record in tmp_result: if record['job_status'] == 'normal': record['job_status'] = '正常' else: record['job_status'] = '暂停' if record['last_schedule_status'] in ['True', 'success']: record['last_schedule_status'] = '成功' else: record['last_schedule_status'] = '失败' record['last_schedule_starttime'] = record[ 'last_schedule_starttime'].strftime("%Y-%m-%d %H:%M:%S") record['last_schedule_endtime'] = record[ 'last_schedule_endtime'].strftime("%Y-%m-%d %H:%M:%S") else: tmp_result = "" code = 0 message = "ok" success = True data_list = tmp_result finally: mysql_conn.dispose() body_dict['data'] = data_list result_dict = { 'code': code, 'success': success, 'message': message, 'body': body_dict } result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def flush_job_info_to_mysql(): # 定时刷新数据到数据库,避免数据库压力过大 global job_event_status_dict status = False mysql_conn = db_mysql.MyPymysqlPoolDict() for job_id in list(job_event_status_dict): if not job_id: continue sql = "select id, job_id from %s where job_id = '%s';" % (table_name, job_id) try: job_id_result = mysql_conn.select(sql) print(sql, job_id_result) except Exception as e: print(e) else: job_name = job_event_status_dict[job_id]['job_name'] job_status = job_event_status_dict[job_id]['job_status'] job_trigger = job_event_status_dict[job_id]['job_trigger'] last_schedule_starttime = job_event_status_dict[job_id][ 'last_schedule_starttime'] last_schedule_endtime = job_event_status_dict[job_id][ 'last_schedule_endtime'] last_schedule_status = job_event_status_dict[job_id][ 'last_schedule_status'] if job_id_result: print('update sql') table_id = int(job_id_result[0]['id']) update_sql = "update %s set job_status = '%s', job_trigger = '%s', last_schedule_starttime = '%s', " \ "last_schedule_endtime = '%s', last_schedule_status = '%s' where id = %s;" % \ (table_name, job_status, job_trigger, last_schedule_starttime, last_schedule_endtime, last_schedule_status, table_id) try: mysql_conn.update(update_sql) except Exception as e: print(e) else: status = True else: insert_sql = "INSERT INTO %s(job_id, job_name, job_status, job_trigger, last_schedule_starttime, " \ "last_schedule_endtime, last_schedule_status) " \ "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s');" % \ (table_name, job_id, job_name, job_status, job_trigger, last_schedule_starttime, last_schedule_endtime, last_schedule_status) try: mysql_conn.insert(insert_sql) except Exception as e: print(e) else: status = True job_event_status_dict = {} mysql_conn.dispose() return status
def get_job_config(job_id): result = False mysql_conn = db_mysql.MyPymysqlPoolDict() sql = "select * from task_job_config where job_id = '%s';" % job_id try: result = mysql_conn.select(sql) except Exception as e: print('Job_id: %s, get job config Error: %s!' % (job_id, e)) mysql_conn.dispose() return result
def get_all_job_config(table_name): result = False mysql_conn = db_mysql.MyPymysqlPoolDict() try: sql = "select * from %s;" % table_name except Exception as e: print('get_job_config fail, reason', e) pass else: result = mysql_conn.select(sql) finally: mysql_conn.dispose() return result
def query_resource_package(request): from src.lib import django_api django_api.DjangoApi().os_environ_update() mysql_conn = db_mysql.MyPymysqlPoolDict() result_dict = { 'code': 500, 'success': False, 'message': 'fail', 'body': {} } table_name = 'cost_resource_package' package_name = str(request.GET['package_name']) package_id = str(request.GET['package_id']) status = str(request.GET['status']) deduct_type = str(request.GET['deduct_type']) support_product = str(request.GET['support_product']) try: sql = "select * from %s where package_name like '%%%s%%' and package_id like '%%%s%%' and status like " \ "'%%%s%%' and deduct_type like '%%%s%%' and support_product like '%%%s%%' order by remaining_amount;" % \ (table_name, package_name, package_id, status, deduct_type, support_product) print(sql) tmp_result = mysql_conn.select(sql) except Exception as e: print('except, reason: %s' % e) else: result_dict['code'] = 0 if tmp_result: for record in tmp_result: record['total_amount'] = '%s %s' % ( record['total_amount'], record['total_amount_unit']) record['remaining_amount'] = '%s %s' % ( record['remaining_amount'], record['remaining_amount_unit']) record['effective_time'] = record['effective_time'].strftime( "%Y-%m-%d %H:%M:%S") record['expiry_time'] = record['expiry_time'].strftime( "%Y-%m-%d %H:%M:%S") record['update_time'] = record['update_time'].strftime( "%Y-%m-%d %H:%M:%S") result_dict['success'] = True result_dict['message'] = 'success' finally: mysql_conn.dispose() result_dict['body']['data'] = tmp_result result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def do_judge(problem_table, gps_object, expression_id, judge_result, level, value, time, begin_time, end_time): mysql_conn = db_mysql.MyPymysqlPoolDict() success = False # 获取同一个规则同一个对象的异常数据的个数 try: sql = "select count(*) AS count from %s where gps_object = '%s' and expression_id = '%s' and status = 1;" % \ (problem_table, gps_object, expression_id) result = mysql_conn.select(sql) monitor_count = result[0]['count'] except Exception as e: monitor_count = 0 print('do sql failed: %s' % e) if monitor_count == 0 and judge_result is True: # 如果gps_problem没表中没有该域名的异常数据,且判断结果为异常,则直接表中加入这条异常数据 status = 1 try: sql = "insert into %s(expression_id, level, gps_object, value, update_time, status, begin_time) values('%s', '%s', " \ "'%s', '%s','%s', '%s', '%s');" % (problem_table, expression_id, level, gps_object, value, time, status, begin_time) mysql_conn.insert(sql) success = True except Exception as e: print('do sql failed: %s' % e) if monitor_count == 0 and judge_result is False: success = True pass if monitor_count == 1: try: sql_cmd = "select id, expression_id, level, value, status, update_time from %s where gps_object = '%s' and " \ "expression_id = '%s';" % (problem_table, gps_object, expression_id) result = mysql_conn.select(sql_cmd) problem_id = result[0]['id'] if judge_result is True: status = 1 sql = "update %s set expression_id = '%s', level = '%s', value = '%s', status = '%s', update_time = '%s' " \ "where id = '%s';" % (problem_table, expression_id, level, value, status, time, problem_id) mysql_conn.update(sql) else: status = 0 sql = "update %s set expression_id = '%s', level = '%s', value = '%s', status = '%s', " \ "update_time = '%s', end_time = '%s' where id = '%s';" % \ (problem_table, expression_id, level, value, status, time, end_time, problem_id) mysql_conn.update(sql) success = True except Exception as e: print('something failed: %s' % e) mysql_conn.dispose() return success
def query_bill_overview(request): from src.lib import django_api django_api.DjangoApi().os_environ_update() mysql_conn = db_mysql.MyPymysqlPoolDict() body_dict = {} result_dict = { 'code': 500, 'success': False, 'message': 'fail', 'body': body_dict } table_name = 'cost_bill_overview' cost_item_id = str(request.GET['cost_item_id']) billing_cycle = str(request.GET['billing_cycle']) product_code = str(request.GET['product']) sql = "select item.item_remark as item_remark, bill.billing_cycle as billing_cycle, " \ "bill.product_code as product, sum(bill.pretax_amount) as pretax_amount from cost_bill_overview bill," \ "cost_item item where item.id = bill.cost_item_id and cost_item_id like '%%%s%%' and " \ "product_code like '%%%s%%' and billing_cycle like '%%%s%%' " \ "group by item_remark,billing_cycle,product_code order by billing_cycle desc, pretax_amount desc;" % \ (cost_item_id, product_code, billing_cycle) print(sql) try: tmp_result = mysql_conn.select(sql) except Exception as e: print('except, reason: %s' % e) result_dict['message'] = '数据库查询失败!' else: data_list = [] result_dict['success'] = True result_dict['code'] = 0 if not tmp_result: result_dict['message'] = '无匹配的数据' else: result_dict['message'] = 'success' data_list = tmp_result finally: mysql_conn.dispose() body_dict['data'] = data_list result_dict['body'] = body_dict result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def query_task_job_config(request): table_name = 'task_job_config' try: job_id = str(request.GET['jobId']) job_status = str(request.GET['jobStatus']) except: job_id = "" job_status = "" data_list = [] body_dict = {} from src.lib import django_api django_api.DjangoApi().os_environ_update() mysql_conn = db_mysql.MyPymysqlPoolDict() code = 500 message = "fail" success = False try: sql = "select id, job_id jobId, `trigger`, trigger_args triggerArgs, jitter, run_at_startup runAtStartup, " \ "enable jobStatus from %s where job_id like '%%%s%%' and enable like '%%%s%%' order by " \ "trigger_args desc;" % (table_name, job_id, job_status) print("sql: ", sql) tmp_result = mysql_conn.select(sql) print(tmp_result, type(tmp_result)) except Exception as e: print(e) else: if not tmp_result: tmp_result = "" code = 200 success = True message = "ok" data_list = tmp_result finally: mysql_conn.dispose() body_dict['data'] = data_list result_dict = { 'code': code, 'success': success, 'message': message, 'body': body_dict } result_json = json.dumps(result_dict, ensure_ascii=False) print(result_json) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def select_pn_block_data(start_time, end_time, pn_type, source_node, dest_node): result = [] #sql = "SELECT source AS source_node,pn_node AS dest_node,type , MAX(r_clock - clock + 1) AS max_duration , SUM(r_clock - clock + 1) AS total_duration , COUNT(*) AS number_of_times " \ # "FROM %s " \ # "WHERE clock BETWEEN %s AND %s AND type = %s AND source = '%s' AND pn_node IN %s " \ # "GROUP BY pn_node;" % ( # pn_event_data_table, start_time, end_time, pn_type, source_node, dest_node) sql = ( "SELECT source AS source_node,pn_node AS dest_node,type , MAX(r_clock - clock + 1) AS max_duration , " "SUM(r_clock - clock + 1) AS total_duration , COUNT(*) AS number_of_times " "FROM %s " "WHERE clock BETWEEN %s AND %s AND type = %s AND source = '%s' AND pn_node IN %s " "GROUP BY pn_node;" % (pn_event_data_table, start_time, end_time, pn_type, source_node, dest_node)) print('sql', sql) try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() result = mysql_conn_dict.select(sql) print('sql_result', result) except Exception as e: status_message = 'error: 数据库连接失败. ' print(e, status_message) else: if result: for r in result: if r['source_node'] == 'opscloud-1': r['source_node'] = 'aliyun' elif r['source_node'] == 'aws-template-2': r['source_node'] = 'aws' if r['dest_node'] == 'opscloud-1': r['dest_node'] = 'aliyun' elif r['dest_node'] == 'aws-template-3': r['dest_node'] = 'aws' if r['type'] == 0: r['type'] = 'icmp' elif r['type'] == 2: r['type'] = 'telnet' r['total_duration'] = int(r['total_duration']) else: result = [] finally: mysql_conn_dict.dispose() print('result', result) return result
def get_sql_select_result(sql): attempts = 0 status = False result = list() while attempts < 3 and not status: try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() result = mysql_conn_dict.select(sql) except Exception as e: print(e) else: status = True #if result: # for i in result: # ak_list.append(i['ak_id']) #print('成功获取可用ak %s 个,列表: %s' % (len(ak_list),ak_list)) finally: mysql_conn_dict.dispose() attempts += 1 return result
def get_method_info(method_name): mysql_conn = db_mysql.MyPymysqlPoolDict() status = False result = dict() try: sql_cmd = "select source_table, judge_object, judge_metric from gps_judge_methods where name = '%s';" % (method_name) sql_result = mysql_conn.select(sql_cmd) except Exception as e: print('do sql failed %s' % e) return status, result else: source_table = sql_result[0]['source_table'] judge_object = sql_result[0]['judge_object'] judge_metric = sql_result[0]['judge_metric'] result['source_table'] = source_table result['judge_object'] = judge_object result['judge_metric'] = judge_metric status = True mysql_conn.dispose() return status, result
def delete_task_job_config(request): # 任务配置信息的变更接口! code = 500 success = False data_list = [] body_dict = {} from src.lib import django_api django_api.DjangoApi().os_environ_update() if request.method == 'POST': # 当提交表单时 mysql_conn = db_mysql.MyPymysqlPoolDict() table_name = 'task_job_config' table_id = int(request.body.decode()) #table_id = json.loads(request.body.decode()).get('id') delete_sql = "delete from %s where id=%s" % (table_name, table_id) try: mysql_conn.delete(delete_sql) except Exception as e: print('delete task exception: ', e) else: code = 200 success = True message = "删除任务成功" mysql_conn.dispose() else: message = '请求方法错误' body_dict['data'] = data_list result_dict = { 'code': code, 'success': success, 'message': message, 'body': body_dict } result_json = json.dumps(result_dict, ensure_ascii=False) print(result_json) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def query_product_renew(request): from src.lib import django_api from src.lib import time_api django_api.DjangoApi().os_environ_update() result_dict = { 'code': 500, 'success': False, 'message': 'fail', 'body': {} } table_name = 'cost_product_renew' tmp_result = {} cost_item_id = str(request.GET['cost_item_id']) instance_id = str(request.GET['instance_id']) product_code = str(request.GET['product_code']) status = str(request.GET['status']) page = int(request.GET['page']) limit = int(request.GET['limit']) print(cost_item_id, instance_id, product_code, status) mysql_conn = db_mysql.MyPymysqlPoolDict() offset = (page - 1) * limit # (当前页数-1)*每页数量 = 每页的开始位置 # 获取总条数,用于返回的json里面输出 try: sql = "select count(id) as count from %s where cost_item_id like '%%%s%%' and instance_id like '%%%s%%' and " \ "product_code like '%%%s%%' and status like '%%%s%%'" % \ (table_name, cost_item_id, instance_id, product_code, status) print(sql) total_count = int(mysql_conn.select(sql)[0]['count']) except Exception as e: print("except: %s" % e) total_count = -1 try: sql = "select cost_item_id, instance_id, instance_name, product_code, product_type, status, " \ "subscription_type, end_time, create_time, sub_status, renew_status from %s where " \ "cost_item_id like '%%%s%%' and instance_id like '%%%s%%' and product_code " \ "like '%%%s%%' and status like '%%%s%%' order by end_time limit %s,%s;" % \ (table_name, cost_item_id, instance_id, product_code, status, offset, limit) print(sql) tmp_result = mysql_conn.select(sql) except Exception as e: print('except: %s' % e) else: result_dict['code'] = 0 if tmp_result: for record in tmp_result: record['count_down'] = int( (record['end_time'] - time.time()) / 86400) record['end_time'] = time_api.timestamp_to_datetime( record['end_time']) record['create_time'] = time_api.timestamp_to_datetime( record['create_time']) print(tmp_result) result_dict['success'] = True result_dict['message'] = 'success' result_dict['body']['data'] = tmp_result result_dict['body']['count'] = total_count mysql_conn.dispose() result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def add_task_job_config(request): # 任务配置信息的变更接口! code = 500 success = False message = 'fail' result_dict = {'code': code, 'success': success, 'message': message} from src.lib import django_api django_api.DjangoApi().os_environ_update() if request.method == 'POST': # 当提交表单时 print(request.body.decode()) table_name = 'task_job_config' job_id = json.loads(request.body.decode()).get('jobId') trigger = json.loads(request.body.decode()).get('trigger') trigger_args = json.loads(request.body.decode()).get('triggerArgs') if not job_id or not trigger or not trigger_args: result_dict['message'] = '必填参数(*)不能为空' result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8") if trigger == "crontab": values = trigger_args.split() if len(values) != 5: result_dict['message'] = 'crontab参数列数不正确,输入 {}, 需要 5'.format( len(values)) result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse( result_json, content_type="application/json,charset=utf-8") elif trigger == "cron": import re if not re.search('second', trigger_args): result_dict['message'] = 'cron仅支持second!' result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse( result_json, content_type="application/json,charset=utf-8") # 判断记录是否已存在 mysql_conn = db_mysql.MyPymysqlPoolDict() sql = "select * from %s where job_id = '%s'" % (table_name, job_id) try: tmp_result = mysql_conn.select(sql) except Exception as e: print('query task exception: ', e) result_dict['message'] = "查询数据库失败" else: if tmp_result: result_dict['message'] = '记录已存在' else: jitter = json.loads(request.body.decode()).get('jitter') run_at_startup = json.loads( request.body.decode()).get('runAtStartup') job_status = json.loads(request.body.decode()).get('jobStatus') add_sql = "INSERT INTO %s (`job_id`, `trigger`, `trigger_args`, `jitter`, `run_at_startup`, `enable`)" \ " VALUES ('%s', '%s', '%s', %s, '%s', '%s');" % \ (table_name, job_id, trigger, trigger_args, jitter, run_at_startup, job_status) try: mysql_conn.insert(add_sql) except Exception as e: print('insert task exception: ', e) result_dict['message'] = "写入数据库异常" else: result_dict['code'] = 200 result_dict['success'] = True result_dict['message'] = "增加任务成功" mysql_conn.dispose() else: result_dict['message'] = '请求方法错误' result_json = json.dumps(result_dict, ensure_ascii=False) print(result_json) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def query_aliyun_product_renew(accessid, accesssecret, regionid, cost_item_id): import datetime import aliyunsdkcore from aliyunsdkcore.acs_exception.exceptions import ClientException from src.lib.cloud import aliyun_api last_day = 30 status = False table_name = "cost_product_renew" time_part_a = (datetime.datetime.now() - datetime.timedelta(days=last_day)).strftime('%Y-%m-%d') time_part_b = (datetime.datetime.now() - datetime.timedelta(days=last_day)).strftime('%H:%M:%S') end_time_start = "%sT%sZ" % (time_part_a, time_part_b) end_time_end = "2199-03-23T12:00:00Z" # 首先查询返回的条数,以决定需要查询的页数 pass try: result_json = aliyun_api.AliyunApi(accessid, accesssecret, regionid).query_available_instance( end_time_start, end_time_end) result_total = json.loads(result_json, encoding='utf-8') tmp_result = result_total['Data']['InstanceList'] except aliyunsdkcore.acs_exception.exceptions.ServerException: print("aliyunsdkcore.acs_exception.exceptions.ServerException") except Exception as e: print("except: %s" % e) return status else: status = True if not tmp_result: return status # 获取db中数据 mysql_conn = db_mysql.MyPymysqlPoolDict() sql = "select * from %s where cost_item_id = %s;" % (table_name, cost_item_id) db_result = mysql_conn.select(sql) for instance in tmp_result: status = instance['Status'] subscription_type = instance['SubscriptionType'] product_code = instance['ProductCode'] instance_id = instance['InstanceID'] try: product_type = instance['ProductType'] except KeyError: product_type = "" try: sub_status = instance['SubStatus'] except KeyError: sub_status = "" renew_status = instance['RenewStatus'] instance_name = "" end_time = iso_to_timestamp(instance['EndTime']) create_time = iso_to_timestamp(instance['CreateTime']) sql = "INSERT INTO cost_product_renew (cost_item_id, instance_id, instance_name, product_code, product_type, " \ "status, subscription_type, end_time, create_time, sub_status, renew_status) VALUES " \ "(%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');" % \ (cost_item_id, instance_id, instance_name, product_code, product_type, status, subscription_type, end_time, create_time, sub_status, renew_status) if not db_result: result = mysql_conn.insert(sql) continue update = False for db_record in db_result: db_instance_id = db_record['instance_id'] if instance_id == db_instance_id: # instance_id一致,或者需要更新数据,或者数据一致不需要更新 update = True db_id = db_record['id'] db_status = db_record['status'] db_subscription_type = db_record['subscription_type'] db_end_time = db_record['end_time'] db_sub_status = db_record['sub_status'] db_renew_status = db_record['renew_status'] if status == db_status and end_time == db_end_time and renew_status == db_renew_status and \ sub_status == db_sub_status and subscription_type == db_subscription_type: break # update sql = "UPDATE %s set status = '%s', subscription_type = '%s', end_time = '%s', " \ "sub_status = '%s', renew_status = '%s' where id = %s" % \ (table_name, status, subscription_type, end_time, sub_status, renew_status, db_id) result = mysql_conn.update(sql) break if not update: # insert result = mysql_conn.insert(sql) mysql_conn.dispose() return status
def update_task_job_config(request): # 任务配置信息的变更接口! code = 500 success = False message = 'fail' result_dict = {'code': code, 'success': success, 'message': message} from src.lib import django_api django_api.DjangoApi().os_environ_update() if request.method == 'PUT': # 当提交表单时 mysql_conn = db_mysql.MyPymysqlPoolDict() table_name = 'task_job_config' table_id = json.loads(request.body.decode()).get('id') job_id = json.loads(request.body.decode()).get('jobId') trigger = json.loads(request.body.decode()).get('trigger') trigger_args = json.loads(request.body.decode()).get('triggerArgs') jitter = json.loads(request.body.decode()).get('jitter') run_at_startup = json.loads(request.body.decode()).get('runAtStartup') job_status = json.loads(request.body.decode()).get('jobStatus') if not job_id or not trigger or not trigger_args: result_dict['message'] = '必填参数(*)不能为空' result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8") if trigger == "crontab": values = trigger_args.split() if len(values) != 5: result_dict['message'] = 'crontab参数列数不正确,输入 {}, 需要 5'.format( len(values)) result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse( result_json, content_type="application/json,charset=utf-8") elif trigger == "cron": import re if not re.search('second', trigger_args): result_dict['message'] = 'cron仅支持second!' result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse( result_json, content_type="application/json,charset=utf-8") update_sql = "update %s set job_id='%s',`trigger`='%s', trigger_args='%s', jitter=%s, run_at_startup='%s', " \ "enable='%s' where `id`=%s" % \ (table_name, job_id, trigger, trigger_args, jitter, run_at_startup, job_status, table_id) print('update_sql: ', update_sql) try: mysql_conn.update(update_sql) except Exception as e: print('update task exception: ', e) result_dict['message'] = "更新数据库异常" else: result_dict['code'] = 200 result_dict['success'] = True result_dict['message'] = "更新任务成功" mysql_conn.dispose() else: result_dict['message'] = '请求方法错误' result_json = json.dumps(result_dict, ensure_ascii=False) print(result_json) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def pn_status(request): from src.lib import django_api django_api.DjangoApi().os_environ_update() data_list = [] body_dict = {} body_dict['data'] = data_list # 定义默认的code和status值 code = 500 success = False if request.method == 'POST': # 当提交表单时 try: start_time = json.loads(request.body.decode()).get('start_time') end_time = json.loads(request.body.decode()).get('end_time') source_node = json.loads(request.body.decode()).get('source_node') node = json.loads(request.body.decode()).get('node') pn_attribute = json.loads(request.body.decode()).get('pn_attribute') type = json.loads(request.body.decode()).get('type') except Exception as e: print(e,'error: Failed to get transfer parameters.') status_message = " error : Failed to get transfer parameters." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) #判断传入的源节点参数,并进行转换 if source_node: if source_node not in ('aws','aliyun') and source_node != '': status_message = " error : source_node' value is incorrect." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) else: if source_node == 'aliyun': source_node = 'opscloud-1' elif source_node == 'aws': source_node = 'aws-template-2' elif source_node == '': source_node = 'opscloud-1' else: source_node = 'opscloud-1' if not start_time or not end_time : status_message = ' error : start_time,end_time value cannot be empty.' result_json = to_json_result(code, status_message, success,body_dict) return HttpResponse(result_json) start_time = is_vaild_data(start_time) end_time = is_vaild_data(end_time) if not start_time or not end_time: status_message = ' error : One of start_time and end_time value is invaild.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) if start_time > end_time: status_message = ' error : start_time cannot be greater than end_time.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) if not type: pn_type = 0 else: if type == 'telnet': pn_type = 2 elif type == 'dealy': pn_type = 1 elif type == 'block': pn_type = 0 if not node or node == '': if pn_attribute == 'master': node = pn_master_list elif pn_attribute == 'backup': node = pn_backup_list elif pn_attribute == '' or not pn_attribute or pn_attribute == 'all': if source_node == 'opscloud-1': node = pn_aws_list elif source_node == 'aws-template-2': node = pn_aliyun_list else: pn_list = pn_master_list + pn_backup_list + pn_aws_list + pn_aliyun_list print('pn_list',pn_list) if node not in pn_list: print("存在") print('111', node, pn_master_list) status_message = " error : node' value is incorrect. " body_dict['data'] = data_list result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) node = "('" + node + "')" #将节点名称以字符串 转成类似元组的形式,sql语句只接受元组形式 sql = "SELECT source,pn_node,type , MAX(r_clock - clock + 1) AS MaxDuration , SUM(r_clock - clock + 1) AS TotalDuration , COUNT(*) AS NumberOfTimes " \ "FROM %s " \ "WHERE clock BETWEEN %s AND %s AND type = %s AND source = '%s' AND pn_node IN %s " \ "GROUP BY pn_node;" % ( pn_event_data_table, start_time, end_time, pn_type, source_node, node) print('sql',sql) try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() result = mysql_conn_dict.select(sql) print('sql_result',result) mysql_conn_dict.dispose() except Exception as e: mysql_conn_dict.dispose() status_message = 'error: Database query failed. ' print(e, status_message) result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) else: if not result: status_message = ' error : No eligible data.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) print('sql_result02',result) for r in result: if r['source'] == 'opscloud-1': r['source'] = 'aliyun' elif r['source'] == 'aws-template-2': r['source'] = 'aws' if r['pn_node'] == 'opscloud-1': r['pn_node'] = 'aliyun' elif r['pn_node'] == 'aws-template-3': r['pn_node'] = 'aws' r['TotalDuration'] = int(r['TotalDuration']) print('sql_result03', result) status_message = 'succes' code = 0 success = True body_dict['data'] = result result_json = to_json_result(code, status_message, success, body_dict) print(result_json) return HttpResponse(result_json) else: status_message = ' error : Please use post request.' result_json = to_json_result(code, status_message, success, data_list) return HttpResponse(result_json)
def pn_delay_status(request): from src.lib import django_api django_api.DjangoApi().os_environ_update() data_list = [] body_dict = {} body_dict['data'] = data_list # 定义默认的code和status值 code = 500 success = False if request.method == 'POST': # 当提交表单时 try: start_time = json.loads(request.body.decode()).get('start_time') end_time = json.loads(request.body.decode()).get('end_time') source_node = json.loads(request.body.decode()).get('source_node') node = json.loads(request.body.decode()).get('node') pn_attribute = json.loads(request.body.decode()).get('pn_attribute') except Exception as e: print(e,'error: Failed to get transfer parameters.') status_message = " error : Failed to get transfer parameters." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) #判断传入的源节点参数 if source_node: if source_node not in ('aws','aliyun') and source_node != '': status_message = " error : source_node' value is incorrect." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) elif source_node == '': source_node = 'aliyun' else: source_node = 'aliyun' if start_time and end_time : result_time = judge_time(start_time, end_time) if not result_time: status_message = " error :One of start_time and end_time value is incorrect." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) else: status_message = ' error : start_time,end_time value cannot be empty.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) if not node or node == '': if pn_attribute == 'master': node = pn_master_list elif pn_attribute == 'backup': node = pn_backup_list elif pn_attribute == '' or not pn_attribute or pn_attribute == 'all': if source_node == 'aliyun': node = pn_aws_list elif source_node == 'aws': node = pn_aliyun_list else: pn_list = pn_master_list + pn_backup_list + pn_aws_list + pn_aliyun_list print('pn_list',pn_list) if node not in pn_list: status_message = " error : node' value is incorrect. " result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) node = "('" + node + "')" #将节点名称以字符串 转成类似元组的形式,sql语句只接受元组形式 sql = "SELECT CAST(date AS CHAR ) AS date,source,PNnode,valueAvg,valueMax,valueA,valueB,valueC,valueD,valueE,valueF,valueG,valueH,valueI,valueJ,valueK " \ "FROM %s " \ "where date BETWEEN '%s' AND '%s' AND source = '%s' AND PNnode IN %s;" % ( pn_event_delay_data_table, start_time, end_time, source_node, node) print('sql',sql) try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() result = mysql_conn_dict.select(sql) print('sql_result',result) mysql_conn_dict.dispose() except Exception as e: mysql_conn_dict.dispose() status_message = 'error: Database query failed. ' print(e,status_message) result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) else: if not result: status_message = ' error : No eligible data.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) print('sql_result02',result) status_message = 'succes' code = 0 success = True body_dict['data'] = result result_json = to_json_result(code, status_message, success, body_dict) print(result_json) return HttpResponse(result_json) else: status_message = ' error : Please use post request.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json)
def update_ak_apply_info(request): # 更新ak的申请人信息接口 print('111') result_dict = {'code': -1, 'success': False, 'message': 'fail'} from src.lib import django_api django_api.DjangoApi().os_environ_update() print(request.body.decode()) if request.method != 'PUT': # 当提交表单时 result_dict['message'] = '请求方法错误, 需求PUT' result_json = json.dumps(result_dict, ensure_ascii=False) return HttpResponse(result_json, content_type="application/json,charset=utf-8") mysql_conn = db_mysql.MyPymysqlPoolDict() table_name = 'ak_apply_list' ak_list_id = json.loads(request.body.decode()).get('id') apply_owner = json.loads( request.body.decode()).get('applyInfo').get('apply_owner') apply_owner_tl = json.loads( request.body.decode()).get('applyInfo').get('apply_owner_tl') apply_owner_department = json.loads( request.body.decode()).get('applyInfo').get('apply_owner_department') apply_type = json.loads( request.body.decode()).get('applyInfo').get('apply_type') apply_reason = json.loads( request.body.decode()).get('applyInfo').get('apply_reason') start_time = int(time.time()) print(ak_list_id, apply_owner, apply_owner_tl, apply_owner_department, apply_type, apply_reason) sql = "select id from %s where ak_list_id=%s" % (table_name, ak_list_id) try: # 使用编辑操作统一了新增和更新,因此首先判断ak_list_id是否存在,存在则update,否则insert tmp_result = mysql_conn.select(sql) if tmp_result: update_sql = "UPDATE %s SET apply_owner='%s', apply_owner_tl = '%s', apply_owner_department='%s', " \ "apply_type='%s', apply_reason='%s' where `ak_list_id`=%s" % \ (table_name, apply_owner, apply_owner_tl, apply_owner_department, apply_type, apply_reason, ak_list_id) print('update_sql: ', update_sql) mysql_conn.update(update_sql) else: insert_sql = "INSERT INTO %s SET ak_list_id=%s, apply_owner='%s', apply_owner_tl='%s', " \ "apply_owner_department='%s', apply_type='%s', apply_reason='%s', start_time=%s" % \ (table_name, ak_list_id, apply_owner, apply_owner_tl, apply_owner_department, apply_type, apply_reason, start_time) print('insert_sql: ', insert_sql) mysql_conn.insert(insert_sql) except Exception as e: print('update task exception: ', e) result_dict['message'] = "写入申请人信息异常" else: result_dict['code'] = 200 result_dict['success'] = True result_dict['message'] = "写入申请人信息成功" finally: mysql_conn.dispose() result_json = json.dumps(result_dict, ensure_ascii=False) print(result_json) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def exec_data_list(self): # 需要传入list格式的数据 mysql_conn = db_mysql.MyPymysqlPoolDict() success = False if isinstance(self.calculate_data_list, list): for calculate_data in self.calculate_data_list: if isinstance(calculate_data, dict): try: if calculate_data[ 'job_id']: # 如果传入的是task_id则直接使用,如果传入的是job_id则搜索task_config表中id值 job_id = calculate_data['job_id'] try: # 根据job_id获取在task配置表里的id,这里命名为task_id sql = "select id from %s where job_id = '%s';" % ( self.task_job_table, job_id) sql_result = mysql_conn.select(sql) except Exception as e: # 如果查询语句执行失败, 则退出 print('%s 查询job_id表失败,退出;job_id为 %s' % (e, job_id)) continue # 查询task_id有问题,结束当前循环 else: if sql_result and len(sql_result) == 1: task_id = sql_result[0]['id'] else: print('请检查 %s 表中,job_id为 %s 的数据准确性' % (self.task_job_table, job_id)) continue # task_job配置表中数据不准确,退出当前循环 else: task_id = calculate_data['task_id'] object_filed = calculate_data['object_filed'] object_value = calculate_data['object_value'] try: # 如果没有传object_remark_name则默认使用object_value作为展示名 object_remark_name = calculate_data[ 'object_remark_name'] except Exception as e: object_remark_name = calculate_data['object_value'] metric_dict = calculate_data['metric_dict'] try: # 如果没有传update_time则使用当前调用函数的时间作为update_time update_time = calculate_data['update_time'] except Exception as e: update_time = datetime.datetime.now() except Exception as e: # object_remark_name和update_time以外的,其他所有参数为必传项 print(' %s ' % e) continue # 传入数据格式有问题,结束当前循环 else: # try: # 根据job_id获取在task配置表里的id,这里命名为task_id # sql = "select id from %s where job_id = '%s';" % (self.task_job_table, job_id) # sql_result = mysql_conn.select(sql) # except Exception as e: # 如果查询语句执行失败, 则退出 # print('%s 查询job_id表失败,退出;job_id为 %s' % (e, job_id)) # continue # 查询task_id有问题,结束当前循环 # else: # if sql_result and len(sql_result) == 1: # task_id = sql_result[0]['id'] try: success = self.become_calculate_data( mysql_conn, task_id, object_filed, object_value, object_remark_name, metric_dict, update_time) except Exception as e: print('% s 数据获取成功,但执行数据抽象化失败' % e) continue else: # 如果当条数据格式不对,则执行下一条数据循环 print('传入的列表中的元素需要为dict格式') continue else: # 如果没接收到list格式的传入数据,则失败 print('未接收到list格式的数据') mysql_conn.dispose() return success mysql_conn.dispose() return success
def query_aws_bill(cost_item_id): try: import simplejson as json except ImportError: import json from src.lib.cloud import aws_api status = False # 每次获取本月和上月的账单数据,避免上月结尾的数据遗漏 current_cycle = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) last_cycle = datetime.date(datetime.date.today().year, datetime.date.today().month - 1, 1) update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) for cycle in [current_cycle, last_cycle]: month_start = (cycle.replace(day=1)).strftime('%Y-%m-%d') month_end = (cycle.replace(month=cycle.month + 1, day=1) - datetime.timedelta(days=1)).strftime('%Y-%m-%d') month = cycle.strftime('%Y-%m') try: response = aws_api.AwsApi().get_cost_and_usage( month_start, month_end) tmp_result = json.loads(json.dumps(response)) result_total = tmp_result['ResultsByTime'][0]['Groups'] except Exception as e: print("except: %s" % e) else: status = True # 获取db数据,用于数据比对,确认写入还是更新 if len(result_total) == 0: return status mysql_conn = db_mysql.MyPymysqlPoolDict() # 获取db中数据 sql = "select * from %s where billing_cycle = '%s' and cost_item_id = %s;" % ( table_name, month, cost_item_id) db_result = mysql_conn.select(sql) # 分类计算,写入数据库 for product in result_total: if len(product['Keys']) != 1: print('error') continue product_code = product['Keys'][0] tmp_amount = float(product['Metrics']['BlendedCost'] ['Amount']) * 7 # 简单按照*7来计算美元汇率 pretax_amount = float('%.2f' % tmp_amount) insert_sql = "INSERT INTO cost_bill_overview (cost_item_id, product_code, billing_cycle, " \ "pretax_amount, update_time) VALUES (%s, '%s', '%s', %s, '%s');" % \ (cost_item_id, product_code, month, pretax_amount, update_time) if not db_result: result = mysql_conn.insert(insert_sql) else: update = False for db_record in db_result: db_record_id = int(db_record['id']) db_cost_item_id = int(db_record['cost_item_id']) db_product_code = db_record['product_code'] db_pretax_amount = db_record['pretax_amount'] if cost_item_id == db_cost_item_id and product_code == db_product_code: update = True db_update_time = db_record['update_time'] db_update_time_month = datetime.datetime.strftime( db_update_time, '%Y-%m') if month != current_cycle and db_update_time_month != month: break elif pretax_amount == db_pretax_amount: break # update sql = "UPDATE %s set pretax_amount = '%s' where id = %s" \ % (table_name, pretax_amount, db_record_id) result = mysql_conn.update(sql) break if not update: result = mysql_conn.insert(insert_sql) mysql_conn.dispose() return status
def check_ak_leak_in_github(task_id=0): calculate_fail = [] #对接巡检系统失败的ak calculate_data = {'task_id': task_id, 'object_field': 'ak'} #巡检抽象化数据集合 sql_select_ak_be_check = "select accesskey_id from %s WHERE status = 1 and policies <> 'NULL'; " % ak_list_table_name ak_be_check_list = [] #待检查的ak列表集合 ak_info_list = get_sql_select_result(sql_select_ak_be_check) if ak_info_list: for i in ak_info_list: ak_be_check_list.append(i['accesskey_id']) print('获待检查ak %s 个,列表: %s' % (len(ak_be_check_list), ak_be_check_list)) sql_ak_leak_list = "SELECT ak_list_id, file_path_url FROM %s WHERE end_time is NULL ;" % ak_leak_table_name ak_leak_list = get_sql_select_result( sql_ak_leak_list) #获取ak泄漏表中还未恢复泄漏问题的ak信息 problem_ak_list = [] #经过此次检查存在泄漏问题的ak信息集合 for ak in ak_be_check_list: calculate_data_list = [] calculate_data['object_value'] = ak metric_dict = {} attempts = 0 break_flag = False leak_list = list() while attempts < 4: try: leak_list = github_searchcode.Engine(token=api_token, keyword=ak).search() break except Exception as e: attempts += 1 print("获取失败: %s," % e, "重试三次") time.sleep(660) if attempts == 3: break_flag = True break if break_flag: continue #print('leak_list',leak_list,type(leak_list),len(leak_list)) if len(leak_list) > 0: print('查询到有%s的匹配记录' % ak, leak_list) metric_dict['use_in_github'] = 1 calculate_data['metric_dict'] = metric_dict for i in leak_list: sql_select_ak_id = "select id from %s WHERE accesskey_id = '%s'; " % ( ak_list_table_name, ak) select_ak_id_result = get_sql_select_result(sql_select_ak_id) print("select_ak_id_result", select_ak_id_result) ak_id = select_ak_id_result[0]['id'] problem_ak = {'ak_list_id': ak_id, 'file_path_url': i['url']} problem_ak_list.append(problem_ak) #将存在泄漏情况的ak信息收集到一个集合里 sql = "select count(*) from %s where file_path_url = '%s' AND ak_list_id = '%s' AND end_time is NULL" % ( ak_leak_table_name, i['url'], ak_id) result = get_sql_select_result(sql) #查询ak_leak表中是否已存在该ak泄漏记录 ak_leak_count = result[0]['count(*)'] if ak_leak_count == 0: if i['email'] is None: i['email'] = NULL sql = "insert into %s set ak_list_id = '%s', repository = '%s', file_path_url = '%s', user = '******', email = %s, start_time = '%s';" % ( ak_leak_table_name, ak_id, i['repository'], i['url'], i['user_name'], i['email'], now_time) attempts = 0 while attempts < 3: try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() mysql_conn_dict.update(sql) mysql_conn_dict.dispose() break except Exception as e: print(e) attempts += 1 finally: if attempts == 2: print("error: insert {m} table is fail".format( m=ak_leak_table_name)) return False if len(leak_list) == 0: print('本次查询没有%s的匹配记录' % ak) metric_dict['use_in_github'] = 0 calculate_data['metric_dict'] = metric_dict calculate_data_list.append(calculate_data) print('calculate_data_list', calculate_data_list) #数据对接巡检系统 from src.judge import data_calculate calculate_result = data_calculate.BecomeCalculate( calculate_data_list).exec_data_list() #print('calculate_result',calculate_result) if not calculate_result: calculate_fail.append(ak) if calculate_fail: print('输出对接巡检系统失败的ak', calculate_fail) print('problem_ak_list', problem_ak_list) #判断出已经恢复泄露的ak if ak_leak_list and problem_ak_list: noleak_ak = [ak for ak in ak_leak_list if ak not in problem_ak_list] elif ak_leak_list and not problem_ak_list: noleak_ak = ak_leak_list print('noleak_ak', noleak_ak) if noleak_ak: for i in noleak_ak: sql = "update %s set end_time = '%s' WHERE ak_list_id = '%s' AND file_path_url = '%s' AND end_time is NULL;" % ( ak_leak_table_name, now_time, i['ak_list_id'], i['file_path_url']) attempts = 0 while attempts < 3: try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() mysql_conn_dict.update(sql) mysql_conn_dict.dispose() break except Exception as e: print(e) attempts += 1 return True
def pn_delay_status(request): from src.lib import django_api django_api.DjangoApi().os_environ_update() data_list = [] body_dict = {} body_dict['data'] = data_list # 定义默认的code和status值 code = 500 success = False if request.method == 'POST': # 当提交表单时 try: start_time = json.loads(request.body.decode()).get('start_time') end_time = json.loads(request.body.decode()).get('end_time') source_node = json.loads(request.body.decode()).get('source_node') node = json.loads(request.body.decode()).get('node') except Exception as e: print(e, 'error: Failed to get transfer parameters.') status_message = " error : Failed to get transfer parameters." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) #判断传入的源节点参数 if source_node: if source_node not in ('aws', 'aliyun') and source_node != '': status_message = " error : source_node' value is incorrect." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) elif source_node == '': source_node = 'aliyun' else: source_node = 'aliyun' if start_time and end_time: result_time = judge_time(start_time, end_time) if not result_time: status_message = " error :One of start_time and end_time value is incorrect." result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) else: status_message = ' error : start_time,end_time value cannot be empty.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) print('pn_node_list', pn_node_list) if node: if node == 'aws': node = 'aws-template-3' elif node == 'aliyun': node = 'opscloud-1' elif node not in pn_node_list: status_message = " error : node' value is incorrect. " result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) else: status_message = " error : node' value is empty. " result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json) sql = "SELECT CAST(date AS CHAR ) AS date,source,PNnode,valueAvg,valueMax,value9999,value9995,value999,value99,value98 " \ "FROM %s " \ "where date BETWEEN '%s' AND '%s' AND source = '%s' AND PNnode = '%s';" % ( pn_event_delay_data_table, start_time, end_time, source_node, node) print('sql', sql) try: mysql_conn_dict = db_mysql.MyPymysqlPoolDict() result = mysql_conn_dict.select(sql) print('sql_result', result) except Exception as e: status_message = 'error: Database query failed. ' print(e, status_message) else: if not result: status_message = ' error : No eligible data.' else: date_list = [] valueAvg_list = [] valueMax_list = [] value9999_list = [] value9995_list = [] value999_list = [] value99_list = [] value98_list = [] value_all = {} for r in result: date_list.append(r['date']) valueAvg_list.append(r['valueAvg']) valueMax_list.append(r['valueMax']) value9999_list.append(r['value9999']) value9995_list.append(r['value9995']) value999_list.append(r['value999']) value99_list.append(r['value99']) value98_list.append(r['value98']) value_all['date'] = date_list value_all['valueAvg'] = valueAvg_list value_all['valueMax'] = valueMax_list value_all['value9999'] = value9999_list value_all['value9995'] = value9995_list value_all['value999'] = value999_list value_all['value99'] = value99_list value_all['value98'] = value98_list status_message = 'succes' code = 0 success = True body_dict['StandardData'] = result body_dict['LineChartData'] = value_all finally: mysql_conn_dict.dispose() result_json = to_json_result(code, status_message, success, body_dict) print(result_json) return HttpResponse(result_json) else: status_message = ' error : Please use post request.' result_json = to_json_result(code, status_message, success, body_dict) return HttpResponse(result_json)
def query_ak_info_list(request): table_name = 'ak_list' accesskey_id = request.GET['accessKeyId'] status = request.GET['status'] cost_item_id = request.GET['costItemId'] page = int(request.GET['page']) limit = int(request.GET['limit']) mysql_conn = db_mysql.MyPymysqlPoolDict() offset = (page - 1) * limit # (当前页数-1)*每页数量 = 每页的开始位置 data_list = [] body_dict = {} from src.lib import django_api django_api.DjangoApi().os_environ_update() result_dict = {'code': 500, 'success': False, 'message': "fail"} # 获取总条数,用于返回的json里面输出 sql = "select count(`id`) as count from %s where `accesskey_id` like '%%%s%%' and status like '%%%s%%' and " \ "cost_item_id like '%%%s%%'" % (table_name, accesskey_id, status, cost_item_id) try: print(sql) total_count = int(mysql_conn.select(sql)[0]['count']) except Exception as e: print("except: %s" % e) result_dict['message'] = "查询AK数量异常" total_count = -1 mysql_conn.dispose() else: if total_count > 0: # 查询ak信息 sql1 = "select * from %s where `accesskey_id` like '%%%s%%' and status like '%%%s%%' and " \ "cost_item_id like '%%%s%%' limit %s,%s" \ % (table_name, accesskey_id, status, cost_item_id, offset, limit) # 查询ak的使用场景 sql2 = "select * from ak_use_info" # 查询ak申请人信息 sql3 = "select * from ak_apply_list" try: print("sql: ", sql1) tmp_result1 = mysql_conn.select(sql1) tmp_result2 = mysql_conn.select(sql2) tmp_result3 = mysql_conn.select(sql3) except Exception as e: result_dict['message'] = "查询具体AK信息异常" else: result_dict['code'] = 0 result_dict['message'] = "success" result_dict['success'] = True for record in tmp_result1: record.pop('update_time') ak_list_id = record['id'] use_count = 0 # ak使用场景的计数,默认未使用 use_list = [] # 使用场景的列表 use_list_detail = {} # 使用场景的明细列表 apply_info = {} if record['policies']: policies_num = len(record['policies'].split(',')) if record['last_use_time']: record['last_use_time'] = record[ 'last_use_time'].strftime("%Y-%m-%d %H:%M:%S") if record['ak_status_change_time']: record['ak_status_change_time'] = record[ 'ak_status_change_time'].strftime( "%Y-%m-%d %H:%M:%S") if record['account_last_login_time']: record['account_last_login_time'] = record[ 'account_last_login_time'].strftime( "%Y-%m-%d %H:%M:%S") if tmp_result2: for i in tmp_result2: if ak_list_id == i['ak_list_id']: use_count += 1 use_name = i['use_name'] use_detail = i['use_detail'] use_list.append(use_name) use_list_detail[use_name] = use_detail if tmp_result3: for j in tmp_result3: if ak_list_id == j['ak_list_id']: j.pop('update_time') from src.lib import time_api j['start_time'] = time_api.timestamp_to_datetime( j['start_time']) apply_info = j record['use_num'] = use_count record['policies_num'] = policies_num record['use_list'] = use_list record['use_list_detail'] = use_list_detail record['applyInfo'] = apply_info data_list = tmp_result1 finally: mysql_conn.dispose() else: result_dict['code'] = 0 result_dict['message'] = "success" result_dict['success'] = True body_dict['data'] = data_list body_dict['count'] = total_count result_dict['body'] = body_dict result_json = json.dumps(result_dict, ensure_ascii=False) print(result_json) return HttpResponse(result_json, content_type="application/json,charset=utf-8")
def cost_judge(id_name, method_name): mysql_conn = db_mysql.MyPymysqlPoolDict() success = False problem_table = 'gps_problem' rules_table = 'gps_rules' cost_id_table = 'cost_item' file_path = os.path.join(os.path.dirname(__file__), "../../conf/key.conf") cf = configparser.ConfigParser() cf.read(file_path) section = 'cloud_id' cloud_id = cf.get(section, id_name) print(cloud_id) try: status, result = get_method_info(method_name) except Exception as e: print('do get_method_info failed %s' % e) return success else: if status is False: print('do get_method_info failed %s') return success else: source_table = result['source_table'] judge_metric = result['judge_metric'] judge_object = result['judge_object'] try: sql = "select * from %s where available = '1' and method_name = '%s';" % (rules_table, method_name) sql_result = mysql_conn.select(sql) except Exception as e: print('执行sql failed %s:' % e) return success else: expression_id = sql_result[0]['id'] level = sql_result[0]['level'] compare = sql_result[0]['compare'] reference_value = int(sql_result[0]['reference_value']) result_dict = dict() try: sql = "select item_remark from %s where id = '%s'" % (cost_id_table, cloud_id) sql_result = mysql_conn.select(sql) except Exception as e: print('执行sql failed %s:' % e) return success else: gps_object = sql_result[0]['item_remark'] try: # 根据账户id获取云账户的余额 sql_cmd = "select %s, update_time from %s where %s = '%s';" % (judge_metric, source_table, judge_object, cloud_id) result = mysql_conn.select(sql_cmd) except Exception as e: print('执行sql failed %s:' % e) return success else: result_dict['gps_object'] = gps_object value0 = result[0][judge_metric] result_dict['update_time'] = result[0]['update_time'] begin_time = result_dict['update_time'].strftime("%Y-%m-%d %H:%M:%S") end_time = result_dict['update_time'].strftime("%Y-%m-%d %H:%M:%S") reference_value = int(reference_value) now_time = datetime.now() # 判断执行的时间 value = value0 try: judge_result = judge_compare.do_compare(value, compare, reference_value) except Exception as e: print('judge_result failed %s:' % e) return success try: success = do_judge(problem_table, gps_object, expression_id, judge_result, level, value, now_time, begin_time, end_time) except Exception as e: print('do_judge failed %s:' % e) return success mysql_conn.dispose() return success
def get_pn_event(task_id=None): global start,stop,event,eventid_exists_list for i in range(-3, 0 ): mysql_conn = db_mysql.MyPymysqlPool('mysql') mysql_conn_dict = db_mysql.MyPymysqlPoolDict() eventid_exists_list = [] yesterday = date.today() + timedelta(days=i) # 昨天日期 print(type(yesterday), yesterday) start = int(time.mktime(time.strptime(str(yesterday), '%Y-%m-%d'))) stop = start + 86400 status = False #stop = time.time() # 当前时间的时间戳 #start = stop - 86400 #当前时间1天前的时间戳 #print(start,stop) #从源数据表中获取源为aws有关专线block和telnet异常的事件的源数据 select_source_aws_data = "select itemid,pn_eventid,clock,r_clock,source,pn_node,type from %s where clock BETWEEN '%s' AND '%s' AND TYPE IN %s AND source = '%s';" % (pn_event_source_data_table, start, stop, (0,2), aws_host) # 从源数据表中获取源为aliyun有关专线block和telnet异常的事件的源数据 select_source_aliyun_data = "select itemid,pn_eventid,clock,r_clock,source,pn_node,type from %s where clock BETWEEN '%s' AND '%s' AND TYPE IN %s AND source = '%s';" % (pn_event_source_data_table, start, stop, (0,2), aliyun_host) # 从专线事件表中筛选出该时段所有的异常事件数据,用于和筛选出来的源数据校验是否已入库 select_eventid = "select %s from %s where clock BETWEEN '%s' AND '%s';" % ('pn_eventid', pn_event_data_table, start, stop) print('select_source_aws_data', select_source_aws_data) print('select_source_aliyun_data', select_source_aliyun_data) try: eventid_exists = mysql_conn.select(select_eventid) source_aws_data = mysql_conn_dict.select(select_source_aws_data) source_aliyun_data = mysql_conn_dict.select(select_source_aliyun_data) mysql_conn.dispose() mysql_conn_dict.dispose() except Exception as e: print(e,'error:get mysql is fail.') mysql_conn.dispose() mysql_conn_dict.dispose() return status print('source_aws_data', source_aws_data) print('source_aliyun_data', source_aliyun_data) print('eventid',eventid_exists) if eventid_exists: for t in eventid_exists: eventid_exists_list.append(t[0]) #print(t[0]) eventid_exists_list = str(eventid_exists_list) print('eventid_exists_list',eventid_exists_list) if source_aliyun_data: aliyun_insert = analysis_insert(source_aliyun_data, 'aliyun-mark') if aliyun_insert: status = True if source_aws_data: aws_insert = analysis_insert(source_aws_data, 'aws-mark') if not aws_insert: status = False else: status = True if not status: print("error: One of aliyun_insert and aws_insert is fail with '%s' data." % (yesterday)) return True
#!/usr/bin/env python3 # coding:utf-8 from django.http import HttpResponse import json from src.lib import db_mysql, django_api # 调用mysql方法 mysql_conn = db_mysql.MyPymysqlPoolDict() def get_problem_data(request): expression_id_list = list() result_list = list() single_problem = dict() problem_list = list() problem_response_dict = dict() problem_body_dict = dict() success = False code = 1 message = '' # 设置默认的page和limit值 try: page = int(request.GET['page']) limit = int(request.GET['limit']) except Exception as e: print("%s:未传入page或limit,使用默认值" % e) page = 1 limit = 10 # 接收传入的类型,根据类型搜索相同方法的表达式id try: gps_type = str(request.GET['type']) sql_cmd = "select id from %s where method_name = '%s'" % ('gps_rules',
def get_init_info(method_name): mysql_conn = db_mysql.MyPymysqlPoolDict() result_list = list() gps_object_list = list() success = False problem_table = 'gps_problem' rules_table = 'gps_rules' try: status, result = get_method_info(method_name) except Exception as e: print('do get_init_info failed %s' % e) return success else: if status is False: print('do get_init_info failed %s') return success else: source_table = result['source_table'] judge_object = result['judge_object'] judge_metric = result['judge_metric'] try: sql = "select * from %s where available = '1' and method_name = '%s';" % (rules_table, method_name) sql_result = mysql_conn.select(sql) except Exception as e: print('do sql failed %s' % e) return success else: if sql_result: for i in sql_result: try: expression_id = i['id'] level = i['level'] compare = i['compare'] reference_value = i['reference_value'] except Exception as e: return success try: sql_cmd = "select id, %s, %s, update_time from %s;" % (judge_object, judge_metric, source_table) result = mysql_conn.select(sql_cmd) except Exception as e: print('do sql failed %s' % e) return success else: if result is not False and len(result) > 0: for m in result: result_dict = dict() try: result_dict['gps_object'] = m[judge_object] result_dict['value0'] = m[judge_metric] result_dict['update_time'] = m['update_time'] result_dict['expression_id'] = expression_id result_dict['level'] = level result_dict['compare'] = compare result_dict['reference_value'] = reference_value except Exception as e: print('表中无数据') result_list = None gps_object_list = None else: gps_object_list.append(m[judge_object]) result_list.append(result_dict) # delete已经不存在的对象,但是在异常数据表里的row try: sql_cmd = "select gps_object from %s where expression_id = '%s';" % (problem_table, expression_id) result = mysql_conn.select(sql_cmd) except Exception as e: print('do del old-sql failed %s' % e) return success else: if result is not False and len(result) > 0: sql_object_list = list() for n in result: sql_object_list.append(n['gps_object']) del_list = [y for y in sql_object_list if y not in gps_object_list] if len(del_list) > 0: for del_object in del_list: try: sql = "delete from %s where gps_object = '%s'" % (problem_table, del_object) mysql_conn.delete(sql) except Exception as e: print('do del old-sql failed %s' % e) return success success = True mysql_conn.dispose() return result_list, success, problem_table, rules_table