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
