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")