def monthly_select(request):
"""
:param request:
:return:
"""
# 获取年份
year = str(json.loads(request.body)['date'])
if year == '':
year = datetime.now().strftime('%Y')
# 初始化
res_list = []
# 按月查出所有数据
sql = "SELECT a.date,sum(a.scene_num) scene_num,sum(a.success_num) success_num, CONCAT(round(sum(a.success_num) / sum(a.scene_num) * 100, 2), '%') AS success_rate,sum(a.failed_num) failed_num,sum(a.alert_num) alert_num FROM(SELECT DATE_FORMAT(date, '%Y-%m') date,scene_num,failed_num,alert_num,success_num FROM td_operation_report) a GROUP BY a.date"
# 获取数据库来连接对象
db = get_db()
# 查询
cursor = db.cursor()
cursor.execute(sql)
month_datas = cursor.fetchall()
for month_data in month_datas:
if str(month_data[0])[:4] == str(year):
month_dict = {
'date': month_data[0],
'total': month_data[1],
'Success_num': month_data[2],
'success_rate': month_data[3],
'failure_num': month_data[4],
'alert_num': month_data[5],
}
res_list.append(month_dict)
return res_list
def get_week(request):
"""
周运行情况
:param request:
:return:
"""
res = json.loads(request.body)
# 初始化
res_list = []
# 获取一周的第一天
res['date'] = str(res['date'])[:10]
temp = datetime.strptime(res['date'], "%Y-%m-%d")
date1 = datetime.date(temp)
# 加6天
sixday = timedelta(days=6)
date2 = date1 + sixday
# 获取date到date2之间的数据
sql = "SELECT * FROM td_operation_report WHERE date between '" + str(date1) + "' and '" + str(date2) + "'"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
operation_reports = cursor.fetchall()
for rep in operation_reports:
dict = {
'date': rep[1],
'scene_num': rep[2],
'success_num': rep[3],
'success_rate': rep[4],
'failed_num': rep[5],
'alert_num': rep[6],
}
res_list.append(dict)
return res_list
def about_search(request):
"""
即将到期的table条件查询
:param request:
:return:
"""
res1 = json.loads(request.body)
limit = res1['limit']
page = res1['page']
search = res1['search'].strip() # 场景名称搜索
keyword = res1['keyword'] # 关键字搜索
res3 = "" # 按时间搜索的开始时间
res4 = "" # 按时间搜索的结束时间
if (res1['date_Choice']):
res3 = res1['date_Choice'][0]
res4 = res1['date_Choice'][1]
sql = "select e.scene_name,e.scene_id,e.item_id,e.monitor_name,e.start_time,e.end_time,e.minture" \
" from(SELECT round((UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(now(),'%Y-%m-%d'),' ',d.end_time)))/60) " \
"as minture ,d.*,c.* FROM(SELECT DISTINCT b.scene_id, a.scene_name, b.item_id " \
"FROM tb_monitor_scene AS a, tl_scene_monitor AS b " \
"WHERE a.id = b.scene_id ) AS c, tb_monitor_item AS d " \
"WHERE c.item_id = d.id ) e,tb_monitor_item as l " \
"where e.id = l.id"
# 用if组合来筛选掉不符合条件的数据
if (search):
sql = sql + " and e.scene_name LIKE '%" + search + "%'"
if (keyword):
sql = sql + " and (e.scene_id LIKE '%" + keyword + "%' or e.item_id LIKE '%" + keyword + "%' or e.monitor_name LIKE '%" + keyword + "%')"
if (res1['date_Choice']):
sql = sql + " and e.start_time between '" + res3 + "' and '" + res4 + "'"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
# 执行sql并分页
res = cursor.fetchall()
p = Paginator(res, limit)
count = p.page_range
pages = count
res_list = []
current_page = p.page(page)
res_list = list(current_page)
res_list2 = []
for i in range(0, len(res_list)):
dic = {
'scene_name': res_list[i][0],
'scene_id': res_list[i][1],
'item_id': res_list[i][2],
'monitor_name': res_list[i][3],
'start_time': str(res_list[i][4]),
'end_time': str(res_list[i][5]),
'minture': str(res_list[i][6]),
'pages': len(pages)
}
res_list2.append(dic)
db.close()
res2 = tools.success_result(res_list2)
return res2
def select_rules_pagination(request):
"""
分页获取告警规则
:param request:
:return:
"""
res1 = json.loads(request.body)
limit = res1['limit']
page = res1['page']
search = res1['search'].strip()
keyword = res1['keyword'].strip()
date_Choice = res1['date_Choice']
if (res1['date_Choice']):
res3 = res1['date_Choice'][0]
res4 = res1['date_Choice'][1]
db = get_db()
cursor = db.cursor()
sql = "select distinct e.scene_id,e.id,e.monitor_name,e.scene_name,f.alert_title,f.alert_content,f.alert_time,f.persons " \
"FROM(SELECT c.scene_id,c.item_id,c.scene_name,d.id,d.monitor_name " \
"FROM(SELECT DISTINCT b.scene_id, a.scene_name, b.item_id " \
"FROM tb_monitor_scene AS a, tl_scene_monitor AS b " \
"WHERE a.id = b.scene_id ) AS c, tb_monitor_item AS d " \
"WHERE c.item_id = d.id ) AS e, td_alert_log AS f " \
"WHERE e.item_id = f.item_id "
if (search):
sql = sql + "and e.scene_name LIKE '%" + search + "%'"
if (keyword):
sql = sql + "and (e.scene_id LIKE '%" + keyword + "%' or e.id LIKE '%" + keyword + "%' or e.monitor_name LIKE '%" + keyword + "%' or f.alert_title LIKE '%" + keyword + "%' or f.alert_content LIKE '%" + keyword + "%' or f.persons LIKE '%" + keyword + "%') "
if (date_Choice):
sql = sql + "and f.alert_time between '" + res3 + "' and '" + res4 + "'"
sql = sql + " ORDER BY e.scene_name"
cursor.execute(sql)
res = cursor.fetchall()
p = Paginator(res, limit)
count = p.page_range
pages = count
res_list = []
current_page = p.page(page)
res_list = list(current_page)
res_list2 = []
for i in range(0, len(res_list)):
dic = {
'scene_id': res_list[i][0],
'id': res_list[i][1],
'monitor_name': res_list[i][2],
'scene_name': res_list[i][3],
'alert_title': res_list[i][4],
'alert_content': res_list[i][5],
'alert_time': str(res_list[i][6]),
'persons': res_list[i][7],
'pages': len(pages)
}
res_list2.append(dic)
db.close()
res2 = tools.success_result(res_list2)
return res2
def get_icube_timeout():
"""
调用icube服务超时时长设置，默认5秒
:return:
"""
res = ""
try:
sql = "select IFNULL(time_interval,5) time_interval from td_scene_design where task_code = 'icube_timeout'"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
res = cursor.fetchall()
db.close()
except Exception as e:
return tools.error_result(e)
return res
def get_previous_second_ts():
"""
获取当前系统时间前1000秒的时间戳（具体时长在td_scene_design表配置，默认为1000）
:return:
"""
res = ""
try:
sql = "SELECT unix_timestamp(date_sub(now(), INTERVAL ifnull((SELECT time_interval FROM td_scene_design WHERE task_code = 'index_advance_duration'),1000) SECOND)) as timestamp;"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
res = cursor.fetchall()
db.close()
except Exception as e:
return tools.error_result(e)
# scene_list = list(res1)
return res
def get_previous_day_ts():
"""
获取当前系统时间前一天的时间戳
:return:
"""
res = ""
try:
sql = "SELECT unix_timestamp(date_sub(now(), INTERVAL 1 DAY)) as timestamp;"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
res = cursor.fetchall()
db.close()
except Exception as e:
return tools.error_result(e)
# scene_list = list(res1)
return res
def get_current_time():
"""
获取当前系统时间
:return:
"""
res = ""
try:
sql = "SELECT DATE_FORMAT(now(), '%H:%i:%S') AS cur_time;"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
res = cursor.fetchall()
db.close()
except Exception as e:
return tools.error_result(e)
# scene_list = list(res1)
return res
def select_all_rules(request):
"""
搜索分页的首页table
:param request:
:return:
"""
res1 = json.loads(request.body)
limit = res1['limit']
page = res1['page']
# 数据库的连接配置
db = get_db()
cursor = db.cursor()
cursor.execute(
"select distinct e.scene_id,e.id,e.monitor_name,e.scene_name,f.alert_title,f.alert_content,f.alert_time,f.persons "
"FROM(SELECT c.scene_id,c.item_id,c.scene_name,d.id,d.monitor_name "
"FROM(SELECT DISTINCT b.scene_id, a.scene_name, b.item_id "
"FROM tb_monitor_scene AS a, tl_scene_monitor AS b "
"WHERE a.id = b.scene_id ) AS c, tb_monitor_item AS d "
"WHERE c.item_id = d.id ) AS e, td_alert_log AS f "
"WHERE e.item_id = f.item_id ORDER BY e.scene_name")
res = cursor.fetchall()
# 对sql语句的返回结果进行分页
p = Paginator(res, limit)
count = p.page_range
pages = count
res_list = []
current_page = p.page(page)
res_list = list(current_page)
res_list2 = []
for i in range(0, len(res_list)):
dic = {
'scene_id': res_list[i][0],
'id': res_list[i][1],
'monitor_name': res_list[i][2],
'scene_name': res_list[i][3],
'alert_title': res_list[i][4],
'alert_content': res_list[i][5],
'alert_time': str(res_list[i][6]),
'persons': res_list[i][7],
'pages': len(pages)
}
res_list2.append(dic)
db.close()
res2 = tools.success_result(res_list2)
return res2
def about_select(request):
"""
即将到期table查询
:param request:
:return:
"""
res1 = json.loads(request.body)
limit = res1['limit']
page = res1['page']
sql = "select e.scene_name,e.scene_id,e.item_id,e.monitor_name,e.start_time,e.end_time,e.minture" \
" from(SELECT round((UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(now(),'%Y-%m-%d'),' ',d.end_time)))/60) " \
"as minture ,d.*,c.* FROM(SELECT DISTINCT b.scene_id, a.scene_name, b.item_id " \
"FROM tb_monitor_scene AS a, tl_scene_monitor AS b " \
"WHERE a.id = b.scene_id ) AS c, tb_monitor_item AS d " \
"WHERE c.item_id = d.id ) e;"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
res = cursor.fetchall()
p = Paginator(res, limit)
count = p.page_range
pages = count
res_list = []
current_page = p.page(page)
res_list = list(current_page)
res_list2 = []
for i in range(0, len(res_list)):
dic = {
'scene_name': res_list[i][0],
'scene_id': res_list[i][1],
'item_id': res_list[i][2],
'monitor_name': res_list[i][3],
'start_time': str(res_list[i][4]),
'end_time': str(res_list[i][5]),
'minture': str(res_list[i][6]),
'pages': len(pages)
}
res_list2.append(dic)
db.close()
res2 = tools.success_result(res_list2)
return res2
def select_scene_operation():
"""
同级一天的场景运行情况
:return:
"""
# 初始化
scenes2 = []
failed_num = 0
scene_num = 0
# 获取时间
yesterday = datetime.now().date() - timedelta(days=1)
# 获取所有场景
scenes = Scene.objects.all()
# 剔除非交易日
for scene in scenes:
if check_jobday(scene.scene_area, yesterday):
scenes2.append(scene)
# 遍历所有场景
for scene in scenes2:
scene_monitors = Scene_monitor.objects.filter(scene_id=scene.id)
# 遍历所有监控项
for scene_monitor in scene_monitors:
# 执行成功的状态信息，1为成功，0为失败
flag = 1
# 根据监控项id，找到监控项
item = Monitor.objects.get(id=scene_monitor.item_id)
# 判断是否成功
if u'基本单元类型' == item.monitor_type:
if 'sql' == item.gather_params:
temp = TDGatherData.objects.get(item_id=scene_monitor.item_id, data_key='DB_CONNECTION')
if temp.gather_time.strftime("%Y-%m-%d") == yesterday:
if temp.gather_error_log:
flag = 0
else:
temp = TDGatherHistory.objects.filter(item_id=scene_monitor.item_id,
data_key='DB_CONNECTION').last()
if temp.gather_error_log:
flag = 0
if 'file' == item.gather_params:
temp = TDGatherData.objects.get(item_id=scene_monitor.item_id, data_key='FILE_EXIST')
if temp.gather_time.strftime("%Y-%m-%d") == yesterday:
if temp.gather_error_log:
flag = 0
else:
temp = TDGatherHistory.objects.filter(item_id=scene_monitor.item_id,
data_key='FILE_EXIST').last()
if temp.gather_error_log:
flag = 0
if 'interface' == item.gather_params:
temp = TDGatherData.objects.get(item_id=scene_monitor.item_id, data_key='URL_CONNECTION')
if temp.gather_time.strftime("%Y-%m-%d") == yesterday:
if temp.gather_error_log:
flag = 0
else:
temp = TDGatherHistory.objects.filter(item_id=scene_monitor.item_id,
data_key='URL_CONNECTION').last()
if temp.gather_error_log:
flag = 0
if u'作业单元类型' == item.monitor_type:
temp = TDGatherData.objects.get(item_id=scene_monitor.item_id)
if temp.gather_time.strftime("%Y-%m-%d") == yesterday:
if temp.gather_error_log:
flag = 0
else:
temp = TDGatherHistory.objects.filter(item_id=scene_monitor.item_id).last()
if temp.gather_error_log:
flag = 0
if u'流程单元类型' == item.monitor_type:
temp = TDGatherHistory.objects.filter(item_id=scene_monitor.item_id)
for l in temp:
if l.gather_error_log:
flag = 0
break
if u'图表单元类型' == item.monitor_type:
temp = TDGatherData.objects.get(item_id=scene_monitor.item_id, data_key='DB_CONNECTION')
if temp.gather_time.strftime("%Y-%m-%d") == yesterday:
if temp.gather_error_log:
flag = 0
else:
temp = TDGatherHistory.objects.filter(item_id=scene_monitor.item_id,
data_key='DB_CONNECTION').last()
if temp.gather_error_log:
flag = 0
# 失败数
if flag == 0:
failed_num += 1
# 场景总数
scene_num += 1
# 成功数
success_num = scene_num - failed_num
# 成功率
if scene_num != 0:
success_rate = round(success_num / scene_num, 4)
success_rate = str(success_rate * 100) + '%'
else:
success_rate = 0
# 获取告警数目
date = str(yesterday) + '%'
sql = "SELECT count(*) from td_alert_log WHERE alert_time like " + "'" + date + "'"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
alert_num = cursor.fetchall()[0][0]
db.close()
dict = {
'date': str(yesterday),
'scene_num': scene_num,
'success_num': success_num,
'success_rate': success_rate,
'failed_num': failed_num,
'alert_num': alert_num
}
re = operation_report.objects.create(**dict)
return re
def selectScenes_ById(request):
"""
场景对比分析
:param request:
:return:
"""
res = json.loads(request.body)
m = Scene.objects.get(id=res['id'])
rid = model_to_dict(m)['scene_area']
# 根据场景id查监控项个数
sm = Scene_monitor.objects.filter(scene_id=res['id'])
item_len = sm.__len__()
# 获取开始结束时间
b_time = res['dataTime'][0]
e_time = res['dataTime'][1]
Alldays = (datetime.strptime(e_time, "%Y-%m-%d %H:%M:%S") - datetime.strptime(b_time, "%Y-%m-%d %H:%M:%S")).days
if Alldays > 15:
return tools.success_result(Alldays)
# 根据开始结束时间查询单个场景下所有监控项每一天最后一个采集到的数据
all_itemid = []
# 所有监控项,字符串拼接
str1 = ""
for scen_monitor in sm:
all_itemid.append(model_to_dict(scen_monitor)['item_id'])
for i, index in enumerate(all_itemid):
if (i + 1) < all_itemid.__len__():
str1 = str(index) + ","
else:
str1 += str(index)
try:
sql = "SELECT * from (select max(a.gather_time) AS mtime,a.item_id " \
"FROM (SELECT t.* FROM (SELECT DATE_FORMAT(tt.gather_time, '%Y-%m-%d') AS xx," \
"tt.gather_time,tt.gather_error_log,tt.item_id " \
"FROM td_gather_history tt WHERE item_id IN (" + str1 + ")) AS t WHERE " \
"gather_time BETWEEN '" + b_time + "' AND '" + e_time + "' ORDER BY item_id,gather_time) a group by " \
"a.item_id,a.xx) as m ORDER BY m.mtime"
db = get_db()
cursor = db.cursor()
cursor.execute(sql)
res1 = cursor.fetchall()
except Exception as e:
return tools.error_result(e)
scene_list = list(res1)
# 所有有错误的日期
d_data = []
h1_new = None
for i in scene_list:
h = TDGatherHistory.objects.filter(item_id=i[1])
for th in h:
h1 = model_to_dict(th)
h1['gather_time'] = th.gather_time
if h1['gather_time'] == i[0]:
h1_new = h1
if (h1_new['gather_error_log'] != '') and (h1_new['gather_error_log'] != None):
d_data.append(i[0])
else:
pass
# 查到的总天数
try:
sql1 = "select count(*) from (select DISTINCT DATE_FORMAT(tb.mtime,'%y-%m-%d') gather_time from (SELECT * from (select max(a.gather_time) AS mtime,a.item_id FROM (SELECT t.* FROM (SELECT DATE_FORMAT(tt.gather_time, '%Y-%m-%d') AS xx,tt.gather_time,tt.gather_error_log,tt.item_id FROM td_gather_history tt WHERE item_id IN (" + str1 + ")) AS t WHERE gather_time BETWEEN '" + b_time + "' AND '" + e_time + "' ORDER BY item_id,gather_time) a group by a.item_id,a.xx) as m ORDER BY m.mtime)as tb)as tf"
db1 = get_db()
cursor1 = db1.cursor()
cursor1.execute(sql1)
count = cursor1.fetchone()
except Exception as e:
return tools.error_result(e)
count = count[0]
# 时间间隔数
# 间隔天数小于3不查，大于3但是查到的有效天数小于3也不要
if Alldays < 3:
return tools.success_result(Alldays)
else:
if count < 3:
return tools.success_result(Alldays)
# 有效天大于3天小于7天，取所有天数
else:
return getPant_list(scene_list, d_data, all_itemid)
def paging(request):
"""
:param request:
:return: 返回当前页的创建信息
"""
res = json.loads(request.body)
page = res['page']
limit = res['limit']
search = res['search']
start_page = limit * page - 9
# 根据id倒排序
# monitor = Scene.objects.all().order_by("-id")[start_page - 1:start_page + 9]
# monitor2 = Scene.objects.all().values('id')
# page_count = math.ceil(len(monitor2) / 10)
# res_list = []
# for i in monitor:
# starttime = tran_china_time_other(i.scene_startTime, i.scene_area)
# endtime = tran_china_time_other(i.scene_endTime, i.scene_area)
# dic = {
# 'id': i.id,
# 'scene_name': i.scene_name,
# 'scene_startTime': str(starttime),
# 'scene_endTime': str(endtime),
# 'scene_creator': i.scene_creator,
# 'scene_creator_time': str(i.scene_creator_time),
# 'scene_editor': i.scene_editor,
# 'scene_editor_time': str(i.scene_editor_time),
# 'scene_area': i.scene_area,
# 'scene_content':i.scene_content,
# 'pos_name': '',
# 'page_count': page_count,
# }
# position = position_scene.objects.filter(scene=i.id)
# for c in position:
# job = pos_info.objects.filter(id=c.position_id)
# for j in job:
# jobs = {
# "pos_name": j.pos_name
# }
# dic['pos_name'] = jobs["pos_name"]
# res_list.append(dic)
#20190516 彭英杰 start
total = Scene.objects.filter(
Q(scene_name__contains=search)
| Q(scene_creator__contains=search)).count()
sql_str ="SELECT a.id,a.scene_name,a.scene_startTime,a.scene_endTime,a.scene_creator,"\
"a.scene_creator_time,a.scene_editor,a.scene_creator_time,a.scene_area "\
",a.scene_content,c.pos_name FROM"\
" tb_monitor_scene a LEFT JOIN "\
" tl_position_scene b on a.id = b.scene_id"\
" LEFT JOIN tb_pos_info c ON b.position_id=c.id "
page_start = (page - 1) * limit
if search != None and search != "":
sql_str = sql_str + " where ( a.scene_name like '%"+search+"%' "\
+" or a.scene_creator like '%"+search+"%' )"
sql_str = sql_str + " ORDER BY a.id DESC LIMIT " + str(
page_start) + "," + str(limit)
db = get_db()
cursor = db.cursor()
cursor.execute(sql_str)
res = cursor.fetchall()
cursor.close()
res_list = []
if len(res) > 0:
page_count = math.ceil(total / 10)
for obj in res:
dic = {
'id': obj[0],
'scene_name': obj[1],
'scene_startTime': str(obj[2]),
'scene_endTime': str(obj[3]),
'scene_creator': obj[4],
'scene_creator_time': str(obj[5]),
'scene_editor': obj[6],
'scene_editor_time': str(obj[7]),
'scene_area': obj[8],
'scene_content': obj[9],
'pos_name': obj[10],
'page_count': page_count,
}
res_list.append(dic)
#20190516 彭英杰 end
return res_list
