def del_old_slb():
status = False
from src.lib import db_mysql
mysql_conn = db_mysql.MyPymysqlPool()
try:
sql = "select instance_id from {} where product_type = 'slb';".format(
'global_instance')
sql_result = mysql_conn.select(sql)
except Exception as e:
print('sql 执行失败：%s' % e)
return status
slb_intance_list = list()
for i in sql_result:
intance_id = i[0]
slb_intance_list.append(intance_id)
for slb_intanceid in slb_intance_list:
# 获取slb个数
format = 'json'
response = aliyun_api.AliyunApi().get_slbinstance_count(
format, slb_intanceid)
result = json.loads(response, encoding='utf-8')
instance_count = result['TotalCount'] # ecs总数
if instance_count == 0: # 如果在阿里云中查不到相关instance的ecs，在表中删除该instance响应数据
try:
del_sql = "delete from %s where instance_id = '%s'" % (
'global_instance', slb_intanceid)
mysql_conn.delete(del_sql)
except Exception as e:
print('sql 执行失败：%s' % e)
mysql_conn.dispose()
status = True
return status
def insert_event_source_data(problem_info, triggerid_info, event_type,
hostname):
mysql_conn = db_mysql.MyPymysqlPool('mysql')
for pi in problem_info:
select_eventid_sql = "select * from %s where pn_eventid = '%s';" % (
pn_event_source_data_table, pi[0]['eventid'])
result = mysql_conn.select(select_eventid_sql) # 根据传入的eventid值来匹配数据
#print('pi',pi,result)
if not result:
#print('mei you chong fu shu ju le')
node_name = pi[0]['name'].split(' ', )[0]
if node_name == hostname or pi[0]['r_clock'] == '0':
#print('test shi fou xiang deng')
break
for key in triggerid_info:
#print('triggerid_info',triggerid_info,pi[0]['name'])
if key == pi[0]['name']:
triggerid = triggerid_info[key]
itemid01 = get_itemid(triggerid)
#print('itemid01',itemid01)
sql_insert_problem_info = "insert into %s(pn_eventid,clock,r_clock,pn_node,type,itemid,source) " \
"values('%s','%s','%s','%s','%s','%s','%s')" % (
pn_event_source_data_table, pi[0]['eventid'], pi[0]['clock'], pi[0]['r_clock'],
node_name, event_type, itemid01, hostname)
mysql_conn.insert(sql_insert_problem_info)
mysql_conn.dispose()
def disaster_alert_listen(task_id=None):
mysql_conn = db_mysql.MyPymysqlPool()
# 告警统一聚合和过滤，如果有灾难级别报警，则发出电话告警
sql = "select * from %s where current_state = 'ALARM' and priority = 1 limit 1" % (
alert_conf.table_name)
print(sql)
#sql = "select * from %s where current_state = 'ALARM' limit 1" %(alert_conf.table_name)
try:
result = mysql_conn.select(sql)
except:
result = False
if result: # 检测到处于alarm状态的灾难告警
# 检查已记录的告警状态，确认是否已经告警过
key = 'cannon_voice_call_status'
try:
result = db_redis.RedisApi().check_exist_of_key(key)
except:
result = 1 # 查询不到，则默认存在，不告警，交给下次查询
if result == 0: # key不存在
ret = voice_call()
if ret:
ttl = 1800
value = '{}'
db_redis.RedisApi().set_key_with_ttl(key, value, ttl)
mysql_conn.dispose()
return True
def check_record_exist(sql):
# True = 记录已存在
mysql_conn = db_mysql.MyPymysqlPool()
try:
result = mysql_conn.select(sql)
except Exception as e:
print(e)
else:
if result:
return True
else:
return False
finally:
mysql_conn.dispose()
def query_account_balance(request):
from src.lib import django_api
django_api.DjangoApi().os_environ_update()
mysql_conn = db_mysql.MyPymysqlPool()
data_list = []
body_dict = {}
result_dict = {'code': 500, 'success': False, 'message': 'fail', 'body': body_dict}
table_name = 'cost_account_balance'
# 获取总条数，用于返回的json里面输出
try:
sql = "select count(id) from %s;" % table_name
total_count = int(mysql_conn.select(sql)[0][0])
except Exception as e:
total_count = 0
if total_count > 0:
try:
sql = "select balance.cost_item_id,item.item_remark,balance.balance,balance.update_time from " \
"cost_account_balance balance,cost_item item where item.id = balance.cost_item_id " \
"order by balance desc;"
print(sql)
tmp_result = mysql_conn.select(sql)
except Exception as e:
print('except, reason: %s' % e)
else:
result_dict['success'] = True
result_dict['message'] = 'success'
result_dict['code'] = 0
for record in tmp_result:
single_record = {}
item_id = record[0]
item_remark = record[1]
balance = record[2]
record_time = record[3].strftime("%Y-%m-%d %H:%M:%S")
single_record['item_id'] = item_id
single_record['item_remark'] = item_remark
single_record['balance'] = balance
single_record['record_time'] = record_time
data_list.append(single_record)
finally:
mysql_conn.dispose()
body_dict['data'] = data_list
result_dict['count'] = total_count
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 update_sql(instance_id, instance_name, product_type, public_ip, private_ip,
create_time, end_time, region_id, pay_type):
status = False
from src.lib import db_mysql
mysql_conn = db_mysql.MyPymysqlPool()
sql = "select count(*) from %s where instance_id = '%s'" % (
'global_instance', instance_id)
sql_result = mysql_conn.select(sql)
instance_count = sql_result[0][0]
if instance_count == 0:
try:
sql = "insert into %s(instance_id, instance_name, product_type, public_ip, private_ip, create_time, end_time, " \
"region_id, pay_type) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
('global_instance', instance_id, instance_name, product_type, public_ip, private_ip, create_time, end_time, region_id, pay_type)
mysql_conn.insert(sql)
except Exception as e:
print('数据表插入数据失败：%s' % e)
return status
elif instance_count == 1:
try:
sql = "select instance_name, product_type, public_ip, private_ip, end_time from %s where instance_id = '%s'" \
%('global_instance', instance_id)
sql_result = mysql_conn.select(sql)
except Exception as e:
print('搜索数据表失败：%s' % e)
return status
old_instance_name = sql_result[0][0]
old_product_type = sql_result[0][1]
old_public_ip = sql_result[0][2]
old_private_ip = sql_result[0][3]
old_end_time = sql_result[0][4]
if old_instance_name != instance_name or old_public_ip != public_ip or old_private_ip != private_ip or\
old_end_time != end_time or old_product_type != product_type:
try:
sql = "update %s set instance_name = '%s', product_type = '%s', public_ip = '%s', private_ip = '%s', " \
"end_time = '%s' where instance_id = '%s';" % ('global_instance', instance_name, product_type,
public_ip, private_ip, end_time, instance_id)
mysql_conn.update(sql)
except Exception as e:
print('更新数据表失败：%s' % e)
return status
else:
print("event_id 大于1，请查询数据库数据准确性")
return status
mysql_conn.dispose()
status = True
return status
def calculate_handler(task_id=None):
# 计算云账号费用数据，并汇总，写入分月费用总表
update_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
mysql_conn = db_mysql.MyPymysqlPool()
table_name = 'cost_cycle_cost'
# 计算后的数据
result = cloud_cost_calculate()
# 和数据库数据对比，并更新或写入数据库
sql = "select * from %s" % table_name
db_cycle_cost = mysql_conn.select(sql)
for cost_item_record in result:
cost_item_id = cost_item_record['cost_item_id']
cycle = cost_item_record['cycle']
cost = float('%.2f' % cost_item_record['cost'])
sql = "insert into %s (cost_item_id, month, total_cost, update_time) values (%s, '%s', %s, '%s')" % \
(table_name, cost_item_id, cycle, cost, update_time)
update = False
if not db_cycle_cost:
# insert
result = mysql_conn.insert(sql)
else:
for db_record in db_cycle_cost:
db_id = db_record[0]
db_cost_item_id = db_record[1]
db_cycle = db_record[2]
db_cost = db_record[3]
if cost_item_id == db_cost_item_id and cycle == db_cycle:
update = True
if cost != db_cost:
# update
sql = "update %s set total_cost = %s where id = %s" % (
table_name, cost, db_id)
result = mysql_conn.update(sql)
break
if not update:
# insert
result = mysql_conn.insert(sql)
mysql_conn.dispose()
return True
def update_sql(event_id, level, event_type, event_name, instance_id, start_time, last_time, alert_status, alert_id, now_time):
mysql_conn = db_mysql.MyPymysqlPool()
success = False
try:
sql = "select count(*) from %s where event_id = '%s'" % ('safe_sas_alert', event_id)
sql_result = mysql_conn.select(sql)
except Exception as e:
print('do sql failed %s' % e)
return success
else:
if result:
try:
event_count = sql_result[0][0]
except Exception as e:
print('maybe sql_result type is wrong: %s' % e)
return success
else:
print('云安全中心没有产生告警')
success = True
return success
if event_count == 0: # 如果表中没有同一个event_id的事件，则直接将alert数据写入表中
try:
sql = "insert into %s(event_id, level, event_type, event_name, instance_id, start_time, last_time, " \
"alert_status, alert_id, update_time) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'," \
" '%s')" % ('safe_sas_alert', event_id, level, event_type, event_name, instance_id, start_time,
last_time, alert_status, alert_id, now_time)
mysql_conn.insert(sql)
except Exception as e:
print('do sql failed %s' % e)
return success
elif event_count == 1: # 如果表中有同一个event_id的事件，则更新数据
try:
sql = "update %s set last_time = '%s', alert_status = '%s', alert_id = '%s', update_time = '%s' where " \
"event_id = '%s';" % ('safe_sas_alert', last_time, alert_status, alert_id, now_time, event_id)
mysql_conn.update(sql)
except Exception as e:
print('do sql failed %s' % e)
return success
else:
print("event_id 大于1，请查询数据库数据准确性")
return success
mysql_conn.dispose()
success = True
return success
def query_item_cost(request):
from src.lib import django_api
django_api.DjangoApi().os_environ_update()
mysql_conn = db_mysql.MyPymysqlPool()
result_dict = {}
body_dict = {}
data_list = []
code = 500
success = False
cost_item_id = str(request.GET['cost_item_id'])
month = str(request.GET['month'])
sql = "select cost.cost_item_id,item.item_remark,cost.month,cost.total_cost from cost_cycle_cost cost, " \
"cost_item item where item.id = cost.cost_item_id and cost.cost_item_id like '%%%s%%' " \
"and cost.month like '%%%s%%' order by month desc" % (cost_item_id, month)
print(sql)
try:
tmp_db_result = mysql_conn.select(sql)
print(tmp_db_result)
except Exception as e:
print('except, reason: %s' % e)
else:
if tmp_db_result:
for db_record in tmp_db_result:
item_dict = {
'item_id': db_record[0],
'item_remark': db_record[1],
'month': db_record[2],
'total_cost': db_record[3]
}
data_list.append(item_dict)
success = True
code = 0
finally:
mysql_conn.dispose()
body_dict['data'] = data_list
result_dict['code'] = code
result_dict['success'] = success
result_dict['message'] = ''
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 analysis_insert(source_data,mark):
if source_data:
mysql_conn = db_mysql.MyPymysqlPool('mysql')
for i in source_data:
#print(i,i['clock'])
if i['pn_node'] == 'aws-mark' or i['pn_node'] == 'aliyun-mark': #当节点为mark节点，则跳过这条源数据
continue
else:
for b in source_data: #第二次遍历源数据列表，通过是否存在相同时间的mark事件判断第一次遍历的源数据是否为有效的事件数据
if b['pn_node'] != mark: #当第二次遍历节点不是为mark直接跳过
#print(b['pn_node'])
continue
else:
if i['type'] != b['type']: #不是同一事件类型也不比较，跳过
continue
else:
#print('3333333333333333')
if abs(i['clock'] - b['clock']) >= 2: #第一次遍历和第二次遍历的出的数据clock相差超过2秒则理解为不同时间的事件，不做判断跳过
continue
else:
if i['source'] != b['source']: #判断源节点是否一致，否则不比较跳过
continue
else:
#print('111111111111111111',abs(i['clock'] - b['clock']),i['clock'])
break
else:
if str(i['pn_eventid']) in eventid_exists_list: #判断该有效的专线事件是否已在专线事件表中入库
pass
else:
key,value = get_col_and_value(i) #将字典的key和value拆分成
#print(a, '11111', b)
try:
insert_event = "insert into %s( %s ) VALUES (%s);" % (pn_event_data_table,key,value)
print(insert_event)
mysql_conn.insert(insert_event)
#mysql_conn.end()
except Exception as e:
print(e,'insert mysql is fail!')
mysql_conn.dispose()
return False
mysql_conn.dispose()
return True
def cloud_cost_calculate():
mysql_conn = db_mysql.MyPymysqlPool()
sql = "select cost_item_id, billing_cycle, pretax_amount from cost_bill_overview"
cycle_total_cost_list = []
try:
result = mysql_conn.select(sql)
except Exception as e:
print('except, reason: %s' % e)
else:
if result:
for record in result:
item_cost_dict = {}
cost_item_id = record[0]
cycle = record[1]
cost = record[2]
if not cycle_total_cost_list:
item_cost_dict['cost_item_id'] = cost_item_id
item_cost_dict['cycle'] = cycle
item_cost_dict['cost'] = cost
cycle_total_cost_list.append(item_cost_dict)
else:
update = False
for dict_in_list in cycle_total_cost_list:
cost_item_id_in_dict = dict_in_list['cost_item_id']
cycle_in_dict = dict_in_list['cycle']
if cost_item_id == cost_item_id_in_dict and cycle == cycle_in_dict:
dict_in_list['cost'] += cost
update = True
break
if not update:
item_cost_dict['cost_item_id'] = cost_item_id
item_cost_dict['cycle'] = cycle
item_cost_dict['cost'] = cost
cycle_total_cost_list.append(item_cost_dict)
finally:
mysql_conn.dispose()
return cycle_total_cost_list
def insert_event_source_data(problem_info, triggerid_info, event_type,
hostname):
sucess = False
mysql_conn = db_mysql.MyPymysqlPool('mysql')
for i in problem_info:
select_eventid_sql = "select * from %s where pn_eventid = '%s';" % (
pn_event_source_data_table, i[0]['eventid'])
try:
result = mysql_conn.select(
select_eventid_sql) # 根据传入的eventid值来匹配数据
except Exception as e:
print(e, 'sql-error:查询专线事件源数据表失败.')
else:
if not result:
#print('mei you chong fu shu ju le')
node_name = i[0]['name'].split(' ', )[0]
if node_name == hostname or i[0]['r_clock'] == '0':
#print('test shi fou xiang deng')
break
for key in triggerid_info:
if key == i[0]['name']:
triggerid = triggerid_info[key]
itemid = get_itemid(triggerid)
sql_insert_problem_info = "insert into %s(pn_eventid,clock,r_clock,pn_node,type,itemid,source) " \
"values('%s','%s','%s','%s','%s','%s','%s')" % (
pn_event_source_data_table, i[0]['eventid'], i[0]['clock'], i[0]['r_clock'],
node_name, event_type, itemid, hostname)
try:
mysql_conn.insert(sql_insert_problem_info)
except Exception as e:
print(e, 'sql-error:插入专线事件源数据表失败.')
else:
sucess = True
mysql_conn.dispose()
return sucess
def query_total_cost():
from src.lib import django_api
django_api.DjangoApi().os_environ_update()
mysql_conn = db_mysql.MyPymysqlPool()
month_total_list = []
status = False
try:
sql = "select month, sum(total_cost) from %s group by month order by month desc limit 12;" % table_name
tmp_db_result = mysql_conn.select(sql)
except Exception as e:
print('except, reason: %s' % e)
else:
status = True
if tmp_db_result:
for db_record in tmp_db_result:
month = db_record[0]
total_cost = db_record[1]
month_dict = {}
month_dict['month'] = month
month_dict['month_cost'] = total_cost
month_total_list.append(month_dict)
finally:
mysql_conn.dispose()
return status, month_total_list
def aliyun_alert_handler(task_id: int = 0) -> bool:
aliyun_alert_total = get_aliyun_alert()
aliyun_alert_list_filter = []
status = False # 默认未采集到数据
alert_from = 'aliyun'
if not aliyun_alert_total:
return aliyun_alert_list_filter, status
count = int(aliyun_alert_total['Total']) // page_size + 1 # 分页数量
mysql_conn = db_mysql.MyPymysqlPool()
for page in range(1, count + 1): # 分页获取告警数据
# 逐条分析json中的报警数据
aliyun_alert_total = get_aliyun_alert(page)
print('-------aliyun_alert_total--------', aliyun_alert_total)
for i in range(3):
try:
tmp = aliyun_alert_total['AlarmHistoryList']['AlarmHistory']
#print('-------tmp--------',tmp)
except:
if i == 2:
return False
time.sleep(5)
continue
status = True
for single_alert in aliyun_alert_total['AlarmHistoryList'][
'AlarmHistory']:
print('-------single_alert--------', single_alert)
production = single_alert['Namespace'].split('acs_')[1]
priority = single_alert['Level']
state = single_alert['Status']
alert_time = int(single_alert['AlertTime'] / 1000)
if production in alert_conf.black_list: # 不统计黑名单产品的告警
continue
if state != 0: # 阿里云告警状态，0表示告警或恢复，非0不需要关注
continue
if priority == 'P4': # 不统计P4级别告警
level = 3
continue
elif priority == 'P3':
level = 2
elif priority == 'P2':
level = 1
else:
level = 0
rule_name = single_alert['RuleName']
metric = single_alert['MetricName']
expression = single_alert['Expression']
current_state = single_alert['State']
Dimensions = single_alert['Dimensions']
instancename = single_alert['InstanceName']
resource = "%s InstanceName: %s" % (Dimensions, instancename)
# 反查slb获得LoadBalancerName
if production == "slb":
loadbalancerid = json.loads(
single_alert['Dimensions'].encode('utf-8'))['instanceId']
port = json.loads(
single_alert['Dimensions'].encode('utf-8'))['port']
loadbalancername = get_LoadBalancerName(loadbalancerid)
resource = "instanceid: %s, port: %s, LoadBalancerName: %s" % (
loadbalancerid, port, loadbalancername)
elif production == "ecs":
instanceid = json.loads(
single_alert['Dimensions'].encode('utf-8'))['instanceId']
resource = "instanceid: %s, InstanceName: %s" % (instanceid,
instancename)
start_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(alert_time))
tmp_result = "%s %s" % (rule_name, metric)
alert_detail = tmp_result
alert_value = float('%.2f' % float(single_alert['Value']))
'''
判断本条是告警，还是恢复
是告警，则:
1、判断 a + state 是否一致，一致to1.1，不一致to2
1.1、判断 a + expression + state是否一致，一致跳过，不一致写入一条新的
2、判断 a + expression + start_time是否一致，确认是否同一条记录反复写，一致跳过，不一致to2.1
2.1 取 a + expression + state = 'OK' 最后一条记录，对比时间，如果本条时间在OK这条的结束时间之前，则跳过，如果在之后，则写入
是恢复，判断下本条恢复的级别
则找寻之前处于告警状态的这个告警项，production + resource + alert_detail + status(告警) + start_time < 本条时间的，将其置为正常
'''
print(single_alert)
insert_sql = "insert into %s(alert_from,production,resource,current_state,alert_detail,expression," \
"alert_value,start_time,priority) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s)" \
% (alert_conf.table_name, alert_from, production, resource, current_state, alert_detail, expression,alert_value, start_time, level)
if current_state == 'ALARM':
sql = "select * from %s where production = '%s' and resource = '%s' and alert_detail = '%s' and current_state = '%s' limit 1;" % (
alert_conf.table_name, production, resource, alert_detail,
current_state)
result = check_record_exist(
sql) # 检查记录在数据库中是否已存在, True = 这条记录在数据库中存在
if result:
# 增加expression是否一致的判断
sql = "select * from %s where production = '%s' and resource = '%s' and alert_detail = '%s' and expression = '%s' and current_state = '%s' limit 1;" % (
alert_conf.table_name, production, resource,
alert_detail, expression, current_state)
result = check_record_exist(sql)
if not result:
result = mysql_conn.insert(insert_sql)
else:
# 判断 + expression + start_time是否一致
sql = "select * from %s where production = '%s' and resource = '%s' and alert_detail = '%s' and expression = '%s' and start_time = '%s' limit 1;" % (
alert_conf.table_name, production, resource,
alert_detail, expression, start_time)
result = check_record_exist(sql)
if not result:
# 取最后一条ok的记录，解决记录重复问题
sql = "select * from %s where resource='%s' and alert_detail='%s' and expression='%s' and current_state='OK' order by start_time desc limit 1" % \
(alert_conf.table_name, resource, alert_detail, expression)
result = mysql_conn.select(sql)
if result:
last_ok_record_endtime = int(
time.mktime(result[0][10].timetuple()))
if alert_time > last_ok_record_endtime:
result = mysql_conn.insert(insert_sql)
else:
result = mysql_conn.insert(insert_sql)
elif current_state == 'OK':
# 找寻之前处于告警状态的此告警项，production + resource + alert_detail + status(告警) + start_time < 本条时间的，将其置为正常
sql = "select * from %s where production = '%s' and resource = '%s' and alert_detail = '%s' and current_state = 'ALARM' and start_time < '%s'" %\
(alert_conf.table_name, production, resource, alert_detail, start_time)
result = mysql_conn.select(sql)
if result:
for single in result:
record_id = single[0]
end_time = start_time # 上一条的结束时间，等于本条的开始时间
sql = "UPDATE %s SET current_state='%s', alert_value=%s, end_time='%s' WHERE id=%s" % (
alert_conf.table_name, current_state, alert_value,
end_time, record_id)
result = mysql_conn.update(sql)
aliyun_alert_list_filter.append(single_alert)
# 当前线程的sql任务结束，提交或回滚
mysql_conn.dispose()
return status
def alert_listen(task_id=None):
mysql_conn = db_mysql.MyPymysqlPool()
status = False
# 查出所有zabbix的未恢复告警
sql = "select * from %s where current_state = 'ALARM' and alert_from = 'zabbix' order by start_time desc" %(alert_conf.table_name)
try:
db_result = mysql_conn.select(sql)
except:
print('except')
else:
if not db_result:
status = True
return status
detail_all = ""
top_level = '严重' # 默认最高级别是为严重
tmp_content = {}
alert_id_list = []
# 查询redis，判断是否处于沉默状态
key = "cannon_zabbix_alert_status"
for record in db_result:
alert_id = record[0]
alert_id_list.append(alert_id)
tmp_content['alert_id_list'] = alert_id_list
alert_num = len(alert_id_list)
redis_key_content = json.dumps(tmp_content, ensure_ascii=False)
print(redis_key_content)
# 检查key是否存在
result = query_redis_key_exist(key)
if result: # key存在
result = check_redis_key_content(alert_id_list, key)
# 检查内容是否一致
if result: # 内容一致，跳过本次告警检查
status = True
return status
for record in db_result:
alert_from = record[1]
resource = record[4]
alert_detail = record[6]
start_time = record[9]
if record[11] == 1:
level = '灾难'
top_level = '灾难'
# 暂时屏蔽电话告警的功能
# 触发电话告警
# voice_call_result = call_voice()
elif record[11] == 2:
level = '严重'
if alert_from == 'zabbix':
alert_url = 'https://cannon-test.ops.yangege.cn/#/alert'
tmp_detail = "> * Host: %s \n\n>[%s] %s [%s] \n\n" % (resource, level, alert_detail, start_time)
detail_all += tmp_detail
# 获取值班人信息
result = query_oncall_owner()
oncall = result['number']
dingtalk_content = "告警总数: %s   最高告警级别: %s \n %s \n @%s \n 点击[告警中心](%s)查看告警详情" % \
(alert_num, top_level, detail_all, oncall, alert_url)
print(dingtalk_content)
result = call_dingtalk(dingtalk_content, oncall, top_level)
result = set_redis_key(key, ttl_time, redis_key_content)
if result:
status = True
mysql_conn.dispose()
return status
def exec_search(git_host, git_api_host): # 通过ak_id调用git.py搜索ak的匹配结果
status = False
git_host = git_host
git_api_host = git_api_host
ak_list = get_ak_list()
now_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
from src.lib import db_mysql
mysql_conn = db_mysql.MyPymysqlPool()
for ak in ak_list:
attempts = 0
leak_list = list()
while attempts < 3:
try:
leak_list = git.GithubCrawl(git_host, git_api_host, user_name, password, ak, api_token).login()
break
except Exception as e:
attempts += 1
print("获取失败: %s," % e, "重试三次")
time.sleep(10)
if attempts == 3:
break
if len(leak_list) > 0:
print('查询到有%s的匹配记录' % ak)
project_url_list = list()
for i in leak_list:
project_url = i['project_url']
project_url_list.append(project_url)
file_path_list = i['file_path_list']
match_num = i['match_num']
user = i['user']
start_time = now_time
sql = "select count(id) from %s where ak = '%s' and project_url = '%s' and match_num != 0;" % \
('safe_ak_leak', ak, i['project_url'])
old_count = mysql_conn.select(sql)[0][0]
if old_count == 0:
try:
sql = 'insert into %s(ak, project_url, file_path_list, match_num, user, start_time) ' \
'values("%s", "%s", "%s", "%s", "%s", "%s");' % \
("safe_ak_leak", ak, project_url, file_path_list, match_num, user, start_time)
mysql_conn.insert(sql)
except Exception as e:
print('sql 执行失败：%s' % e)
if old_count > 0:
try:
sql = 'update %s set ak = "%s", project_url = "%s", file_path_list = "%s", match_num = "%s", ' \
'user = "******", update_time = "%s";' % \
('safe_ak_leak', ak, project_url, file_path_list, match_num, user, start_time)
mysql_conn.update(sql)
except Exception as e:
print('sql 执行失败：%s' % e)
# 删除已经没有泄漏的sql记录
sql = "select project_url from %s where ak = '%s' and match_num != 0;" % ('safe_ak_leak', ak)
result = mysql_conn.select(sql)
sql_project_url_list = []
for s in result:
sql_project_url_list.append(s[0])
del_list = [y for y in sql_project_url_list if y not in project_url_list]
for del_project_url in del_list:
sql = "delete from %s where ak = '%s' and project_url = '%s'" % ('safe_ak_leak', ak, del_project_url)
mysql_conn.delete(sql)
if len(leak_list) == 0:
print('本次查询没有%s的匹配记录' % ak)
sql = "select count(id) from %s where ak = '%s' and match_num != 0;" % ('safe_ak_leak', ak)
old_count = mysql_conn.select(sql)[0][0]
if old_count > 0:
try:
sql = "update %s set match_num = 0 where ak = '%s';" % ('safe_ak_leak', ak)
mysql_conn.update(sql)
except Exception as e:
print('sql 执行失败：%s' % e)
# 删除不在ak列表的原始数据
try: # 搜索ak_leak原始数据表的ak信息，组成列表
sql = "select ak from {} where match_num > 0;".format('safe_ak_leak')
result = mysql_conn.select(sql)
except Exception as e:
print('sql 执行失败：%s' % e)
return status
else:
sql_ak_list = list()
if result is not False and len(result) > 0:
for m in result:
sql_ak = m[0]
sql_ak_list.append(sql_ak)
del_list = [ak for ak in sql_ak_list if ak not in ak_list] # 需要在ak泄漏数据表中删除的ak列表
if len(del_list) > 0:
print('%s中ak已经不存在，即将删除ak泄漏表中相关数据' % del_list)
for del_ak in del_list:
try:
sql = "delete from %s where ak = '%s' and match_num > 0" % ('safe_ak_leak', del_ak)
mysql_conn.delete(sql)
except Exception as e:
print('sql 执行失败 删除老数据失败：%s' % e)
return status
status = True
mysql_conn.dispose()
return status
import datetime
from django.http import HttpResponse
from conf import voice_call_conf
from src.api.voice_call import voice_notification_api
from src.lib import db_mysql
token = voice_call_conf.token_voice_call #获取调用语音接口试需要的token
Duty_Roster = voice_call_conf.Duty_Roster #获取假期人员排班表
Employee_List = voice_call_conf.Employee_List #获取员工及电话号码对应信息
DefaultNum = voice_call_conf.DefaultNum #获取默认值班号码
Sre_Number = voice_call_conf.Sre_Number #获取日常按周轮班顺序列表
alert_content = voice_call_conf.alert_content #获取语音告警的内容
PeopleNum = len(Sre_Number)
appname = 'sre'
employee_list_table = 'employee_list'
mysql_conn = db_mysql.MyPymysqlPool()
def CheckPhone(PhoneNum):
if len(PhoneNum) != 11 or PhoneNum[0] != "1" or PhoneNum.isdigit(
) is False:
return DefaultNum
return PhoneNum
def duty_voice_call():
today = time.strftime("%Y%m%d")
voice_result = {
'code': 500,
'success': False,
'message': 'voice call is fail!!!',
def query_aliyun_bill(accessid, accesssecret, regionid, cost_item_id):
current_cycle = datetime.datetime.now().strftime('%Y-%m')
last_cycle = (datetime.date.today().replace(day=1) -
datetime.timedelta(days=1)).strftime('%Y-%m')
update_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
from src.lib.cloud import aliyun_api
for cycle in [last_cycle, current_cycle]:
try:
result_json = aliyun_api.AliyunApi(
accessid, accesssecret, regionid).query_bill_overview(cycle)
result_total = json.loads(result_json, encoding='utf-8')
tmp_result = result_total['Data']['Items']['Item']
billing_cycle = result_total['Data']['BillingCycle']
except aliyunsdkcore.acs_exception.exceptions.ServerException:
print("aliyunsdkcore.acs_exception.exceptions.ServerException")
except Exception as e:
print("except: %s" % e)
else:
if len(tmp_result) == 0:
continue
# 获取db中数据
mysql_conn = db_mysql.MyPymysqlPool()
sql = "select * from %s where billing_cycle = '%s' and cost_item_id = %s;" % (
table_name, cycle, cost_item_id)
db_result = mysql_conn.select(sql)
for record in tmp_result:
product_name = record['ProductName']
product_code = record['ProductCode']
product_detail = record['ProductDetail']
product_detail_code = record['ProductType']
pretax_amount = float(record['PretaxAmount'])
subscription_type = record['SubscriptionType']
bill_type = record['Item']
insert_sql = "INSERT INTO %s(cost_item_id,product_code,product_name,product_detail_code," \
"product_detail,billing_cycle,pretax_amount,subscription_type,bill_type, " \
"update_time) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %s, '%s', '%s', '%s');" \
% (table_name, cost_item_id, product_code, product_name, product_detail_code,
product_detail, billing_cycle, pretax_amount, subscription_type, bill_type,
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[0])
db_cost_item_id = int(db_record[1])
db_product_code = db_record[2]
db_product_detail = db_record[5]
db_pretax_amount = db_record[7]
db_subscription_type = db_record[8]
db_bill_type = db_record[9]
if cost_item_id == db_cost_item_id and product_code == db_product_code and product_detail == db_product_detail and subscription_type == db_subscription_type and bill_type == db_bill_type:
update = True
db_update_time = db_record[10]
db_update_time_month = datetime.datetime.strftime(
db_update_time, '%Y-%m')
if cycle != current_cycle and db_update_time_month != cycle:
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 True
def zabbix_monitor_switch(request):
action = int(request.GET['action'])
hostip = request.GET['privateIp']
zabbix_ak = request.GET['ak']
success = False
code = 500
message = 'fail'
result_dict = {'code': code, 'success': success, 'message': message}
mysql_conn = db_mysql.MyPymysqlPool()
if zabbix_ak != '1d42ee7b99a7d92bdbdaccc3edc30a9f' or action not in [0, 1]:
result_dict['message'] = 'Args error!'
result_json = json.dumps(result_dict, ensure_ascii=False)
return HttpResponse(result_json,
content_type="application/json,charset=utf-8")
retry = 0
zabbix = zabbix_api.ZabbixApi()
while retry < 3:
if action == 1:
action_name = 'enable'
result = zabbix.host_enable(hostip)
else: # action == 0
action_name = 'disable'
result = zabbix.host_disable(hostip)
if not result:
result_dict['message'] = '%s %s 失败!' % (action_name, hostip)
result_json = json.dumps(result_dict, ensure_ascii=False)
return HttpResponse(result_json,
content_type="application/json,charset=utf-8")
result_dict['code'] = 200
result_dict['success'] = True
result_dict['message'] = '%s %s 成功!' % (action_name, hostip)
# 监控启停的操作记录入库
action_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
host_name = zabbix.get_hostname_with_hostip(hostip)
if not host_name:
result_dict['message'] = '通过zabbix_api获取hostname失败!'
result_json = json.dumps(result_dict, ensure_ascii=False)
return HttpResponse(result_json,
content_type="application/json,charset=utf-8")
sql = "insert into %s(host_ip, host_name, action, time) values('%s', '%s', '%s', '%s')" %\
(alert_conf.zabbix_switch_table, hostip, host_name, action_name, action_time)
try:
result = mysql_conn.insert(sql)
except Exception as e:
result_dict['message'] = '写入操作记录失败!'
else:
result_dict['message'] = '%s %s, 所有动作完成, 最终成功!' % (action_name,
hostip)
break
retry += 1
time.sleep(2)
mysql_conn.dispose()
result_json = json.dumps(result_dict, ensure_ascii=False)
return HttpResponse(result_json,
content_type="application/json,charset=utf-8")
def query_account_balance(task_id=None):
import aliyunsdkcore
from aliyunsdkcore.acs_exception.exceptions import ClientException
mysql_conn = db_mysql.MyPymysqlPool()
for ak_section in cf.sections():
update_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
if ak_section.startswith('aliyun') or ak_section.startswith(
'tencentcloud'):
try:
accessid = cf.get(ak_section, 'AccessKeyId')
accesssecret = cf.get(ak_section, 'AccessKeySecret')
regionid = cf.get(ak_section, 'DefaultRegionId')
cost_item_id = cf.get(ak_section, 'CostItemId')
except Exception as e:
print("except, reason: %s" % e)
continue
if ak_section.startswith('aliyun'):
cloud_type = 'aliyun'
from src.lib.cloud import aliyun_api
try:
result_json = aliyun_api.AliyunApi(
accessid, accesssecret,
regionid).query_account_balance()
result_total = json.loads(result_json, encoding='utf-8')
tmp = float(
result_total['Data']['AvailableAmount'].replace(
',', ''))
except aliyunsdkcore.acs_exception.exceptions.ServerException:
print(
"aliyunsdkcore.acs_exception.exceptions.ServerException"
)
except Exception as e:
print("except, reason: %s" % e)
else:
balance = tmp
elif ak_section.startswith('tencentcloud'):
cloud_type = 'tencentcloud'
from src.lib.cloud import tencentcloud_api
try:
result_json = tencentcloud_api.TencentCloudApi(
accessid, accesssecret,
regionid).query_account_balance()
result_total = json.loads(result_json, encoding='utf-8')
tmp = float(int(result_total['Balance']) / 100)
except Exception as e:
print("except, reason: %s" % e)
else:
balance = tmp
# 检查是否存在记录，存在则update，不存在则insert
sql = "select * from %s where cost_item_id = '%s';" % (
table_name, cost_item_id)
print(sql)
try:
result = mysql_conn.select(sql)
except Exception as e:
print('except, reason: %s' % e)
else:
if result: # update
record_id = result[0][0]
sql = "update %s set balance=%s,update_time='%s' where id='%s';" % \
(table_name, balance, update_time, record_id)
mysql_conn.update(sql)
else: # insert
sql = "insert into %s(cost_item_id, balance, update_time) values('%s', %s, '%s');" % \
(table_name, cost_item_id, balance, update_time)
mysql_conn.insert(sql)
mysql_conn.dispose()
return True
def query_tencentcloud_bill(cost_item_id):
from src.lib.cloud import tencentcloud_api
from tencentcloud.common.exception.tencent_cloud_sdk_exception import TencentCloudSDKException
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]:
cycle_first_day = (cycle.replace(day=1)).strftime('%Y-%m-%d')
cycle_last_day = (cycle.replace(month=cycle.month + 1, day=1) -
datetime.timedelta(days=1)).strftime('%Y-%m-%d')
start_time = "%s 00:00:00" % cycle_first_day
end_time = "%s 23:59:59" % cycle_last_day
month = cycle.strftime('%Y-%m')
try:
result_json = tencentcloud_api.TencentCloudApi(
).query_product_bill(start_time, end_time)
result_total = json.loads(result_json, encoding='utf-8')
#print(result_total)
tmp_result = result_total['SummaryOverview']
except TencentCloudSDKException as err:
print(err)
except Exception as e:
print("except: %s" % e)
else:
mysql_conn = db_mysql.MyPymysqlPool()
if tmp_result:
# 获取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 record in tmp_result:
product_name = record['BusinessCodeName']
product_code = record['BusinessCode']
pretax_amount = float(record['RealTotalCost'])
insert_sql = "INSERT INTO %s(cost_item_id,product_code,product_name,billing_cycle," \
"pretax_amount, update_time) VALUES (%s, '%s', '%s', '%s', '%s', '%s');" \
% (table_name, cost_item_id, product_code, product_name, 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[0])
db_cost_item_id = int(db_record[1])
db_product_code = db_record[2]
db_pretax_amount = db_record[7]
if cost_item_id == db_cost_item_id and product_code == db_product_code:
update = True
db_update_time = db_record[10]
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 True
def insert_event_delay_data(item_result, start, stop, source_node):
for a in item_result:
pn_node = a['name'][:-5] #获取节点名称 去掉字符串末尾5个字符
print(pn_node)
#history_result = zabbix.get_history(a['itemid'],start,stop)
try:
conn = pymysql.connect(host='47.99.229.254',
user='******',
passwd='zabbix',
port=10029,
db='zabbix',
charset='utf8')
cur = conn.cursor(cursor=pymysql.cursors.DictCursor) # 生成游标对象
select_history = "SELECT * FROM %s WHERE clock BETWEEN %s and %s AND itemid = %s ORDER BY `value` DESC;" % (
'history', start, stop, a['itemid'])
print('select_history', select_history)
cur.execute(select_history)
rows = cur.fetchall()
cur.close() # 关闭游标
conn.close() # 关闭连接
except Exception as e:
print(e, 'error:select history is fail!!!')
return False
else:
if not rows:
print('error:select history is null.')
return True
#print(len(history_result),history_result)
print(len(rows))
s = 0
n = 0
for r in rows:
if r['value'] != 0: #判断该clock点的icmp的值是否为0，为0则跳过继续遍历
s = s + r['value']
n = n + 1
if n == 0 or n < 10000: #判断有效icmp值的个数是否为0或10000；满足则认为该天的icmp监控存在异常，需人为分析
print('The number of valid values is less than 10000. ', n)
return True
else:
print('s and n', s, n)
valueAvg = format(s / n, '.4f')
one_day = n
percent50 = int(one_day - (one_day * 0.5))
percent60 = int(one_day - (one_day * 0.6))
percent70 = int(one_day - (one_day * 0.7))
percent80 = int(one_day - (one_day * 0.8))
percent90 = int(one_day - (one_day * 0.9))
percent95 = int(one_day - (one_day * 0.95))
percent96 = int(one_day - (one_day * 0.96))
percent97 = int(one_day - (one_day * 0.97))
percent98 = int(one_day - (one_day * 0.98))
percent99 = int(one_day - (one_day * 0.99))
percent9999 = int(one_day - (one_day * 0.9999))
print(percent9999, percent99, percent98, percent97, percent96,
percent95, percent90, percent80, percent70, percent60,
percent50)
valueMax = rows[0]['value']
valueA = rows[percent9999]['value']
valueB = rows[percent99]['value']
valueC = rows[percent98]['value']
valueD = rows[percent97]['value']
valueE = rows[percent96]['value']
valueF = rows[percent95]['value']
valueG = rows[percent90]['value']
valueH = rows[percent80]['value']
valueI = rows[percent70]['value']
valueJ = rows[percent60]['value']
valueK = rows[percent50]['value']
print('valueAvg', valueAvg, valueMax, valueA, valueB, valueC,
valueD, valueE, valueF, valueG, valueH, valueI)
select_delay_data = "SELECT * FROM %s WHERE date = '%s' AND itemid = '%s' ;" % (
pn_event_delay_data_table, yesterday, a['itemid'])
print(select_delay_data)
try:
mysql_conn = db_mysql.MyPymysqlPool('mysql')
select_delay_data_result = mysql_conn.select(select_delay_data)
print('select_delay_data_result', select_delay_data_result)
except Exception as e:
print(e, 'error:select delay_data is fail!!!')
else:
if not select_delay_data_result:
insert_delay_data_result = "insert into %s(date,valueAvg,valueMax,valueA,valueB,valueC,valueD,valueE,valueF,valueG,valueH,valueI,valueJ,valueK,source,PNnode,itemid) " \
"values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
pn_event_delay_data_table, yesterday,valueAvg,valueMax,valueA,valueB,valueC,valueD,valueE,valueF,valueG,valueH,valueI,valueJ,valueK,source_node,pn_node,a['itemid'])
print('insert_delay_data_result', insert_delay_data_result)
try:
mysql_conn.insert(insert_delay_data_result)
#print('123123')
except Exception as e:
print(
e,
'error:insert pn_event_delay_data_table is fail!!! '
)
mysql_conn.dispose()
return True
def exec_ssl(task_id=False): # 获取所有开启443端口域名的证书信息，更新数据库
job_id = 'get_sslexpiretime'
# 从conf里的table配置文件里读取表名
file_path = os.path.join(os.path.dirname(__file__),
"../../conf/table.conf")
cf = configparser.ConfigParser()
cf.read(file_path)
table_section = 'table_name' # 读取表的名称
ssl_table = cf.get(table_section, 'ssl_table')
start_time = str(datetime.datetime.now())
status = False
real_name_dict = get_real_name()
real_domain_list = list(real_name_dict)
mysql_conn = db_mysql.MyPymysqlPool()
calculate_data_list = list()
for domainnames in real_domain_list:
calculate_data_dict = dict()
domain = real_name_dict[domainnames]
try: # curl命令获取域名详情
cmd = "curl --connect-timeout 2 -m 2 -lkvs https://{}/ |grep -E '^*'".format(
domainnames)
curl_result = sp.getstatusoutput(cmd)
except Exception as e:
print('%s %s' % (e, domainnames))
continue
else: # 获取到域名详情后
curl_result_str = str(curl_result)
try: # 从域名详情内容匹配查找证书的关键信息
m = re.search(
r"start date: (.*?)\\n.*?expire date: (.*?)\\n.*? ",
curl_result_str) # 获取证书开始时间和截止时间信息
if not m:
continue
gmt_format = '%b %d %H:%M:%S %Y GMT'
expire_time = str(
datetime.datetime.strptime(m.group(2),
gmt_format)) # 格式转化为str
cn_result = re.search(r"subject:.*?CN=(\*?.*?\..*?)\\n.\*?",
curl_result_str)
cn = cn_result.group(1)
match_status = str(domain in cn)
except Exception as e:
print('%s' % e)
continue
else:
# 将数据格式化，加入列表，再调用抽象化接口，将数据写入统一表
if task_id:
calculate_data_dict['task_id'] = task_id
else:
calculate_data_dict['job_id'] = job_id
calculate_data_dict['object_filed'] = 'domain_name'
calculate_data_dict['object_value'] = domainnames
metric_dict = dict()
metric_dict['ssl_expire_time'] = expire_time
metric_dict['match_status'] = match_status
calculate_data_dict['metric_dict'] = str(metric_dict)
calculate_data_list.append(calculate_data_dict)
try: # 判断是否是新增的域名，如果是插入一条新数据，不是则从表中读取数据
select_sql = "select count(*) from %s where domain_name = '%s'" % (
ssl_table, domainnames)
sql_result = mysql_conn.select(select_sql)
except Exception as e:
print('%s' % e)
continue
else:
if sql_result:
domain_data_count = sql_result[0][0]
else:
continue
update_time = datetime.datetime.now() # 判断执行的时间
if domain_data_count == 0:
try:
insert_sql = "insert into %s(domain, domain_name, expire_date, cn, match_status, update_time) values" \
"('%s', '%s', '%s', '%s', '%s', '%s')" % (ssl_table, domain, domainnames,
expire_time, cn, match_status, update_time)
mysql_conn.insert(insert_sql)
except Exception as e:
print(' %s:插入原始数据失败, 失败的域名为 %s' % (e, domainnames))
continue
else:
status = True
else:
# 从表中读取相关域名的证书有效期信息
try:
ssl_sql = "select CAST(expire_date AS CHAR) AS expire_date, cn, match_status, update_time from %s" \
" where domain_name = '%s'" % (ssl_table, domainnames)
ssl_result = mysql_conn.select(ssl_sql)
except Exception as e:
print(' %s:查询数据表失败, 失败的域名为 %s' % (e, domainnames))
continue
else:
if not ssl_result: # 如果搜索不到该域名的数据，那么插入该数据
try:
sql = "insert into %s(domain, domain_name, expire_date, cn, match_status, update_time) " \
"values('%s', '%s', '%s', '%s', '%s', '%s)" \
% (ssl_table, domain, domainnames, expire_time, cn, match_status, update_time)
mysql_conn.insert(sql)
except Exception as e:
print(' %s:插入数据表失败, 失败的域名为 %s' %
(e, domainnames))
continue
else:
status = True
elif ssl_result:
old_expire_time = ssl_result[0][
0] # 将从sql中查询的结果取str类型的过期时间
old_cn = ssl_result[0][1]
old_match_status = ssl_result[0][2]
old_time = ssl_result[0][3]
if expire_time != old_expire_time or cn != old_cn or match_status != old_match_status or update_time != old_time:
try: # 如果原始数据和新数据不一致，则更新数据
sql = "update %s set expire_date = '%s', cn = '%s', match_status = '%s', update_time = '%s' " \
"where domain_name = '%s';" % (ssl_table, expire_time, cn, match_status,
update_time, domainnames)
mysql_conn.update(sql)
except Exception as e:
print(' %s:更新数据表失败, 失败的域名为 %s' %
(e, domainnames))
continue
else:
status = True
if status is False:
mysql_conn.dispose()
return status
mysql_conn.dispose()
from src.judge import data_calculate
try: # 将抽象化数据列表传参到抽象化数据处理函数返回结果
print(calculate_data_list)
calculate_status = data_calculate.BecomeCalculate(
calculate_data_list, ).exec_data_list()
except Exception as e:
print('%s 抽象化处理数据失败' % e)
else:
if calculate_status:
print('抽象化数据处理成功')
else:
status = False
return status
mysql_conn = db_mysql.MyPymysqlPool()
# delete已经删除的域名在数据库的row
try:
sql_cmd = "select domain_name from ssl_expire_date"
result = mysql_conn.select(sql_cmd)
except Exception as e:
print(' %s:查询数据表失败, 失败的域名为 %s' % e)
status = False
return status
else:
if not result:
print('表中无数据')
else:
sql_domainname_list = []
for s in result:
sql_domainname_list.append(s[0])
del_list = [
y for y in sql_domainname_list if y not in real_domain_list
]
for del_domainname in del_list:
try:
sql = "delete from %s where domain_name = '%s'" % (
ssl_table, del_domainname)
mysql_conn.delete(sql)
except Exception as e:
print(' %s:删除数据失败, 失败的域名为 %s' % e)
status = False
return status
stop_time = str(datetime.datetime.now())
message = '开始时间:' + start_time + ' 结束时间:' + stop_time
mysql_conn.dispose()
return status, message
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
def collect_aliyun_resource_package(task_id=None):
mysql_conn = db_mysql.MyPymysqlPool()
table_name = 'cost_resource_package'
cost_item_id = 1
from src.lib.cloud import aliyun_api
try:
result_json = aliyun_api.AliyunApi().query_resource_package()
result_total = json.loads(result_json, encoding='utf-8')
tmp_result = result_total['Data']['Instances']['Instance']
#print(tmp_result)
except aliyunsdkcore.acs_exception.exceptions.ServerException:
print("aliyunsdkcore.acs_exception.exceptions.ServerException")
except:
print("except")
else:
if len(tmp_result) == 0:
return True
# 获取db中数据，只选取状态有效 + 扣费类型不为总量恒定型
sql = "select * from %s;" % table_name
db_result = mysql_conn.select(sql)
for record in tmp_result:
#print(record)
deduct_type = record['DeductType']
status = record['Status']
package_id = record['InstanceId']
package_name = record['Remark']
package_type = record['PackageType']
support_product = json.dumps(record['ApplicableProducts']['Product'])
total_amount = float(record['TotalAmount'])
total_amount_unit = record['TotalAmountUnit']
remaining_amount = float(record['RemainingAmount'])
remaining_amount_unit = record['RemainingAmountUnit']
effective_time = record['EffectiveTime']
expiry_time = record['ExpiryTime']
update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
if status == 'Available' and remaining_amount < 0.01:
status = 'Useup'
insert_sql = "INSERT INTO %s(cost_item_id,package_id,package_name,package_type,status," \
"support_product,total_amount,total_amount_unit,remaining_amount,remaining_amount_unit," \
"deduct_type,effective_time,expiry_time,update_time) VALUES " \
"(%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, '%s', '%s', '%s', '%s', '%s');" % \
(table_name,cost_item_id,package_id,package_name,package_type,status,support_product,
total_amount,total_amount_unit,remaining_amount,remaining_amount_unit,deduct_type,
effective_time,expiry_time,update_time)
#print(sql)
if not db_result:
result = mysql_conn.insert(insert_sql)
continue
update = False
for db_record in db_result:
db_record_id = int(db_record[0])
db_package_id = db_record[2]
db_status = db_record[5]
db_remaining_amount = db_record[9]
if package_id == db_package_id:
update = True
if db_status == 'Useup' or db_status == 'Expired' or deduct_type == 'Absolute':
break
elif db_remaining_amount == remaining_amount:
break
# update
#print(db_package_id, db_remaining_amount, package_id, remaining_amount)
if remaining_amount < 0.01 and db_status == 'Available':
status = 'Useup'
sql = "UPDATE %s set status = '%s', remaining_amount = '%s', remaining_amount_unit = '%s' where " \
"id = %s" % (table_name, status, remaining_amount, remaining_amount_unit, db_record_id)
result = mysql_conn.update(sql)
break
if not update:
result = mysql_conn.insert(insert_sql)
mysql_conn.dispose()
return True
def zabbix_alert_handler(task_id: int = 0) -> bool:
zabbix = zabbix_api.ZabbixApi()
result = zabbix.get_trigger()
success = False #默认未采集到数据
if not result:
return success
try:
alert_list = result['result']
print('---------alert_list-------', alert_list)
except:
return success
mysql_conn = db_mysql.MyPymysqlPool()
# 提前获取数据库中的所有在报警状态的zabbix告警记录
sql = "select * from %s where alert_from = 'zabbix' and current_state = 'ALARM' order by start_time desc;" % (
alert_conf.table_name)
db_zabbix_result = mysql_conn.select(sql)
print('---------db_zabbix_result-------', db_zabbix_result)
zabbix_result = []
success = True
alert_list_filter = []
for single_alert in alert_list:
try:
priority = single_alert['priority']
if priority == '2':
level = 3
continue
elif priority == '3' or priority == '4':
level = 2
elif priority == '5':
level = 1
description = single_alert['description']
resource = single_alert['hosts'][0]['host']
expression = single_alert['expression']
start_time = time.strftime(
"%Y-%m-%d %H:%M:%S",
time.localtime(int(single_alert['lastchange'])))
alert_detail = "%s" % (description)
current_state = "ALARM"
alert_from = "zabbix"
production = "ecs"
except:
print("error!")
continue
else:
alert_list_filter.append(single_alert)
tmp = [production, resource, alert_detail, expression]
zabbix_result.append(tmp)
'''检查production、resource、alert_detail是否有重复
如果不重复，直接写入db
如果有重复，例如同一个触发器连续报警，需要检查之前最后一条的报警状态：
if 之前状态是恢复，本次是告警，则直接写入db
if 之前状态是告警，本次是告警，则跳过。'''
insert_new_record_sql = "insert into %s(alert_from,production,resource,current_state,alert_detail,expression,start_time,priority) " \
"values('%s','%s','%s','%s','%s','%s','%s','%s')" % \
(alert_conf.table_name,alert_from,production,resource,current_state,alert_detail,expression,start_time,level)
# 判断产品+资源+告警详情是否重复
sql = "select * from %s where production = '%s' and resource = '%s' and alert_detail = '%s' limit 1;" % (
alert_conf.table_name, production, resource, alert_detail)
result = mysql_conn.select(sql)
if not result: # 不重复，则写入这条告警
mysql_conn.insert(insert_new_record_sql)
else:
# 再增加一个时间的判断
sql = "select * from %s where production='%s' and resource='%s' and alert_detail='%s' and start_time='%s' limit 1;" % (
alert_conf.table_name, production, resource, alert_detail,
start_time)
result = mysql_conn.select(sql)
if result: # 记录已存在
continue
sql = "select * from %s where production = '%s' and resource = '%s' and alert_detail = '%s' order by " \
"start_time desc limit 1" % (alert_conf.table_name, production, resource, alert_detail)
result = mysql_conn.select(sql)
last_state = result[0][5]
if last_state == "ALARM" and current_state == "ALARM":
continue
elif last_state == "OK" and current_state == "ALARM":
mysql_conn.insert(insert_new_record_sql)
'''
获取db中所有报警状态的zabbix报警项，和当前报警项做对比
如果存在，则跳过
如果不存在，则update该项状态，置为恢复，并获取当前时间，置为该条的结束时间
'''
if db_zabbix_result:
for record in db_zabbix_result:
record_id_in_db = record[0]
production_in_db = record[2]
resource_in_db = record[4]
alert_detail_in_db = record[6]
expression_in_db = record[7]
result = False
for i in zabbix_result:
current_production = i[0]
current_resource = i[1]
current_alert_detail = i[2]
current_expression = i[3]
if production_in_db == current_production and resource_in_db == current_resource and \
alert_detail_in_db == current_alert_detail and expression_in_db == current_expression:
result = True
break
if not result:
end_time = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
sql = "UPDATE %s SET current_state='OK', end_time='%s' WHERE id=%s" % (
alert_conf.table_name, end_time, record_id_in_db)
mysql_conn.update(sql)
mysql_conn.dispose()
return success
def get_alert_list(request):
mysql_conn = db_mysql.MyPymysqlPool()
alertfrom = str(request.GET['alertfrom'])
product = str(request.GET['product'])
level = str(request.GET['level'])
status = str(request.GET['status'])
page = int(request.GET['page'])
limit = int(request.GET['limit'])
data_list = []
result_dict = {}
body_dict = {}
from src.lib import django_api
django_api.DjangoApi().os_environ_update()
offset = (page-1)*limit # （当前页数-1）*每页数量 = 每页的开始位置
delta_hours = 720
starttime = datetime.datetime.now()-datetime.timedelta(hours=delta_hours)
code = 500
message = 'fail'
success = False
# 获取总条数，用于返回的json里面输出
try:
sql = "select count(id) from %s where alert_from like '%%%s%%' and production like '%%%s%%' and " \
"current_state like '%%%s%%' and priority like '%%%s%%' and start_time > '%s';" % \
(alert_conf.table_name, alertfrom, product, status, level, starttime)
print(sql)
total_count = int(mysql_conn.select(sql)[0][0])
except:
total_count = -1
if total_count > 0:
# 获取每页的告警信息
try:
sql = "select * from %s where alert_from like '%%%s%%' and production like '%%%s%%' and " \
"current_state like '%%%s%%' and priority like '%%%s%%' and " \
"start_time > '%s' order by start_time desc limit %s,%s;" %\
(alert_conf.table_name, alertfrom, product, status, level, starttime, offset, limit)
print(sql)
tmp_result = mysql_conn.select(sql)
except:
pass
else:
if tmp_result:
code = 0
success = True
for record in tmp_result:
alert_from = record[1]
production = record[2]
resource = record[4]
alert_state_tmp = record[5]
alert_detail = record[6]
expression = record[7]
if record[8] is not None:
value = int(record[8])
else:
value = None
start_time = record[9].strftime("%Y-%m-%d %H:%M:%S")
end_time = ""
if record[10]:
end_time = record[10].strftime("%Y-%m-%d %H:%M:%S")
priority = record[11]
priority_format = priority
single_alert = {}
if production in alert_conf.black_list:
continue
if priority == 1:
priority_format = "灾难"
elif priority == 2:
priority_format = "严重"
elif priority == 3:
priority_format = "一般"
else:
priority_format = "未知"
if alert_state_tmp == "OK":
alert_state = "恢复"
elif alert_state_tmp == "ALARM":
alert_state = "告警"
single_alert['alert_from'] = alert_from
single_alert['production'] = production
single_alert['resource'] = resource
single_alert['priority'] = priority_format
single_alert['alert_state'] = alert_state
single_alert['alert_detail'] = alert_detail
single_alert['expression'] = expression
single_alert['value'] = value
single_alert['start_time'] = start_time
single_alert['end_time'] = end_time
data_list.append(single_alert)
elif total_count == 0:
code = 0
message = 'success'
success = True
body_dict['data'] = data_list
body_dict['count'] = total_count
result_dict['code'] = code
result_dict['success'] = success
result_dict['message'] = message
result_dict['body'] = body_dict
mysql_conn.dispose()
result_json = json.dumps(result_dict, ensure_ascii=False)
return HttpResponse(result_json, content_type="application/json,charset=utf-8")
