def delete(self, *args, **kwargs): data = json.loads(self.request.body.decode("utf-8")) nickname = self.get_current_nickname() db_id = data.get('db_id', None) id_list = data.get('id_list', None) # 记录操作,不成功直接Pass try: with DBContext('w', None, True) as session: if db_id: data_info = session.query(DB).filter( DB.id == int(db_id)).all() for data in data_info: origion_data = model_to_dict(data) origion_data['create_time'] = str( origion_data['create_time']) origion_data['update_time'] = str( origion_data['update_time']) new_record = AssetOperationalAudit( username=nickname, request_object='数据库', request_host=origion_data.get('db_host'), request_method='删除', original_data=origion_data) session.add(new_record) elif id_list: for i in id_list: data_info = session.query(DB).filter(DB.id == i).all() for data in data_info: origion_data = model_to_dict(data) origion_data['create_time'] = str( origion_data['create_time']) origion_data['update_time'] = str( origion_data['update_time']) new_record = AssetOperationalAudit( username=nickname, request_object='数据库', request_host=origion_data.get('db_host'), request_method='删除', original_data=origion_data) session.add(new_record) except Exception as err: ins_log.read_log('error', 'operational_audit error:{err}'.format(err=err)) with DBContext('w', None, True) as session: if db_id: session.query(DB).filter(DB.id == int(db_id)).delete( synchronize_session=False) session.query(DBTag).filter(DBTag.db_id == int(db_id)).delete( synchronize_session=False) elif id_list: for i in id_list: session.query(DB).filter(DB.id == i).delete( synchronize_session=False) session.query(DBTag).filter(DBTag.db_id == i).delete( synchronize_session=False) else: return self.write(dict(code=1, msg='关键参数不能为空')) return self.write(dict(code=0, msg='删除成功'))
def get(self, *args, **kwargs): usage_data = [] #获取当前月份1号日期信息 this_month = datetime.now().replace(day=1, minute=0, hour=1, second=0, microsecond=0) #获取get请求的ec2_id参数 ec2_id = self.get_argument('ec2_id', default=None, strip=True) # 获取get请求的mouth参数 month = self.get_argument('month', default=None, strip=True) #判断get请求是否是查询具体某一个月的信息,如果没有就设置为当前月 if not month: month = this_month else: month = datetime.strptime(month, '%Y-%m') #获取month的下个月当天 next_month = month + relativedelta(months=+1) month = month.strftime("%Y-%m-01") #去除时间 到天 next_month = next_month.strftime("%Y-%m-01") with DBContext('r', const.DEFAULT_DB_KEY) as session: # 判断get请求是否是查询具体某一个ec2的信息,如果没有就查UsageReport表单的第一个 if not ec2_id: ec2 = session.query(UsageReport).filter( UsageReport.month == month).first() else: ec2 = session.query(UsageReport).filter( UsageReport.month == month, UsageReport.ec2_id == ec2_id).first() if ec2: #根据上面得到的ec2 去ResourceUsage表查询具体的使用情况 并过滤月份 usage_data = session.query(ResourceUsage) \ .filter(ResourceUsage.ec2_id == ec2.ec2_id, ResourceUsage.date >= month, ResourceUsage.date < next_month, ).all() ec2_data = model_to_dict(ec2) if ec2 else None ec2_data["cost_gap"] = str( decimal.Decimal(ec2_data['cost_gap']).quantize( decimal.Decimal('0.00000'))) usage_detail = { 'ec2_info': ec2_data, 'usage_list': [model_to_dict(u) for u in usage_data] } #返回数据 return self.write( dict(code=0, msg='获取成功', count=len(usage_detail), data=usage_detail))
def get(self, *args, **kwargs): pageNum = int(self.get_argument('pageNum', default='1', strip=True)) pageSize = int(self.get_argument('pageSize', default='10', strip=True)) bill_date = self.get_argument('bill_date', default=None, strip=True) if not bill_date: return self.write(dict(code=-2, msg='关键参数不能为空')) # 判断pageSize合法性 if not 5 <= pageSize <= 100: return self.write(dict(code=-2, msg='pageSize只能介于5和100之间。')) # 判断pageNum合法性 if not 0 < pageNum: return self.write(dict(code=-2, msg='pageSize只能介于5和100之间。')) bill_date = datetime.strptime(bill_date, '%Y-%m') with DBContext('r', const.DEFAULT_DB_KEY) as session: bill_data = session.query(AwsProjectBudgetControl) \ .filter(AwsProjectBudgetControl.bill_date == bill_date, ).all() bill_list = [model_to_dict(e) for e in bill_data] for bill in bill_list: bill["bill_date"] = bill["bill_date"][:7] for k, v in bill.items(): if isinstance(v, decimal.Decimal): bill[k] = str( decimal.Decimal(v).quantize( decimal.Decimal('0.00000'))) bill_data = bill_list[(pageNum - 1) * pageSize:pageNum * pageSize] return self.write( dict(code=0, msg='获取成功', count=len(bill_list), data=bill_data))
def get_project_budget(): with DBContext('w') as session: data = session.query(AwsProjectBillReport).all() bill_list = [model_to_dict(e) for e in data] for i in bill_list: data_old = session.query(AwsProjectBudgetControl) \ .filter(AwsProjectBudgetControl.userproject == i.get("userproject"), AwsProjectBudgetControl.bill_date == i.get("bill_date")).first() if data_old: session.query(AwsProjectBudgetControl) \ .filter(AwsProjectBudgetControl.userproject == i.get("userproject"), AwsProjectBudgetControl.bill_date == i.get("bill_date")) \ .update( { AwsProjectBudgetControl.aws_total_cost: i.get("aws_total_cost"), } ) else: new_data = AwsProjectBudgetControl( userproject=i.get("userproject"), aws_total_cost=i.get("aws_total_cost"), # aws_budget_cost=i.get("aws_total_cost"), aws_percentage=i.get("aws_total_cost") / i.get("aws_total_cost") if i.get("aws_total_cost") else 0, # aws_alert_percentage=1.2, bill_date=i.get("bill_date")) session.add(new_data) session.commit()
def put(self, *args, **kwargs): data = json.loads(self.request.body.decode("utf-8")) nickname = self.get_current_nickname() db_id = data.get('id', None) db_code = data.get('db_code', None) db_host = data.get('db_host', None) db_port = data.get('db_port', 3306) db_user = data.get('db_user', None) db_pwd = data.get('db_pwd', None) db_env = data.get('db_env', None) proxy_host = data.get('proxy_host', None) db_type = data.get('db_type', 'mysql') db_version = data.get('db_version', None) db_mark = data.get('db_mark', '写') idc = data.get('idc', None) tag_list = data.get('tag_list', []) db_detail = data.get('db_detail', None) db_instance = data.get('db_instance', '') if not db_id or not db_code or not db_host or not db_port or not db_user: return self.write(dict(code=-1, msg='关键参数不能为空')) with DBContext('r') as session: old_password = session.query(DB.db_pwd).filter(DB.id == int(db_id)).first()[0] if old_password != db_pwd: # 加密密码 db_pwd = encrypt(db_pwd) with DBContext('w', None, True) as session: all_tags = session.query(Tag.id).filter(Tag.tag_name.in_(tag_list)).order_by(Tag.id).all() session.query(DBTag).filter(DBTag.db_id == db_id).delete(synchronize_session=False) if all_tags: for tag_id in all_tags: session.add(DBTag(db_id=int(db_id), tag_id=tag_id[0])) session.query(DB).filter(DB.id == int(db_id)).update({DB.db_code: db_code, DB.db_host: db_host, DB.db_port: db_port, DB.db_user: db_user, DB.db_pwd: db_pwd, DB.db_env: db_env, DB.proxy_host: proxy_host, DB.db_type: db_type, DB.db_version: db_version, DB.idc: idc, DB.db_mark: db_mark, DB.db_detail: db_detail, DB.db_instance: db_instance }) # 记录操作,不成功直接Pass try: modify_data = data with DBContext('w', None, True) as session: data_info = session.query(DB).filter(DB.id == int(db_id)).all() for data in data_info: origion_data = model_to_dict(data) origion_data['create_time'] = str(origion_data['create_time']) origion_data['update_time'] = str(origion_data['update_time']) new_record = AssetOperationalAudit(username=nickname, request_object='数据库', request_host=db_host, request_method='更新', original_data=origion_data, modify_data=modify_data) session.add(new_record) except Exception as err: ins_log.read_log('error', 'operational_audit error:{err}'.format(err=err)) return self.write(dict(code=0, msg='编辑成功'))
def get(self, *args, **kwargs): end_day = datetime.now() start_day = end_day - timedelta(days=365) end_day = end_day.strftime("%Y-%m-%d") start_day = start_day.strftime("%Y-%m-%d") start_day = self.get_argument('key', default=start_day, strip=True) end_day = self.get_argument('key', default=end_day, strip=True) family = self.get_argument('family', default="c5", strip=True) size = self.get_argument('size', default="large", strip=True) platform = self.get_argument('platform', default="Linux", strip=True) with DBContext('r', const.DEFAULT_DB_KEY) as session: data = session \ .query(AWSRiUsageReport) \ .filter(AWSRiUsageReport.date >= start_day) \ .filter(AWSRiUsageReport.date < end_day) \ .filter(AWSRiUsageReport.family == family) \ .filter(AWSRiUsageReport.size == size) \ .filter(AWSRiUsageReport.platform == platform) \ .all() histories = [model_to_dict(e) for e in data] for history in histories: history["total_running"] = str( decimal.Decimal(history["total_running"]).quantize( decimal.Decimal('0.00000'))) history["total_ri"] = str( decimal.Decimal(history["total_ri"]).quantize( decimal.Decimal('0.00000'))) history["coverage_rate"] = str( decimal.Decimal(history["coverage_rate"]).quantize( decimal.Decimal('0.00000'))) return self.write( dict(code=0, msg='获取成功', count=len(histories), data=histories))
def get(self, *args, **kwargs): key = self.get_argument('key', default=None, strip=True) page_size = self.get_argument('page', default=1, strip=True) limit = int(self.get_argument('limit', default=15, strip=True)) limit_start = (int(page_size) - 1) * int(limit) result_list = [] with DBContext('r') as session: if key: # 模糊查所有 host_info = session.query(Unattach_Ebs).filter( or_(Unattach_Ebs.Attachments.like('%{}%'.format(key)), Unattach_Ebs.CreateTime.like('%{}%'.format(key)), Unattach_Ebs.Encrypted.like('%{}%'.format(key)), Unattach_Ebs.AvailabilityZone.like('%{}%'.format(key)), Unattach_Ebs.Size.like('%{}%'.format(key)), Unattach_Ebs.SnapshotId.like('%{}%'.format(key)), Unattach_Ebs.State.like('%{}%'.format(key)), Unattach_Ebs.VolumeId.like('%{}%'.format(key)), Unattach_Ebs.VolumeType.like('%{}%'.format(key)), Unattach_Ebs.Iops.like('%{}%'.format(key)))).order_by( Unattach_Ebs.id).all() count = host_info.count() else: host_info = session.query(Unattach_Ebs).order_by( Unattach_Ebs.id) count = host_info.count() host_info = host_info[limit_start:limit_start + limit] for data in host_info: data_dict = model_to_dict(data) result_list.append(data_dict) return self.write( dict(code=0, msg='获取成功', count=count, data=result_list))
def get(self, *args, **kwargs): with DBContext('r', const.DEFAULT_DB_KEY) as session: ec2_data = session.query(UsageReport).order_by( UsageReport.cost_gap.desc()).limit(10) ec2_list = [model_to_dict(e) for e in ec2_data] total = len(ec2_list) for ec2 in ec2_list: ec2["cost_gap"] = str( decimal.Decimal(ec2['cost_gap']).quantize( decimal.Decimal('0.00000'))) return self.write(dict(code=0, msg='获取成功', count=total, data=ec2_list))
def get(self, *args, **kwargs): this_year = datetime.now().replace(month=1, day=1, minute=0, hour=0, second=0, microsecond=0) year = self.get_argument('year', default=None, strip=True) if not year: year = this_year else: year = datetime.strptime(year, '%Y') next_year = datetime(year.year + 1, 1, 1) year = year.strftime("%Y-%m-01 00:00:00") next_year = next_year.strftime("%Y-01-01 00:00:00") with DBContext('r', const.DEFAULT_DB_KEY) as session: bill_data = session.query(AwsProjectBillReport) \ .filter( AwsProjectBillReport.bill_date >= year, AwsProjectBillReport.bill_date < next_year, ).all() bill_list = [model_to_dict(e) for e in bill_data] for bill in bill_list: for k, v in bill.items(): if isinstance(v, decimal.Decimal): bill[k] = str( decimal.Decimal(v).quantize( decimal.Decimal('0.00000'))) #获取月份列表 month_list = [] begin_date = datetime.strptime(year, "%Y-%m-%d %H:%M:%S") end_date = datetime.strptime(next_year, '%Y-%m-%d %H:%M:%S') while begin_date < datetime(end_date.year, 1, 1) - timedelta(days=1): date_str = begin_date month_list.append(date_str) if begin_date.month + 1 <= 12: begin_date = datetime(begin_date.year, begin_date.month + 1, 1) else: break #组织返回的数据结构 bill_dict = {} for i in bill_list: if i["userproject"] in bill_dict.keys(): bill_dict[i["userproject"]].update( {i["bill_date"]: i["aws_total_cost"]}) else: bill_dict.update({i["userproject"]: {}}) for month in month_list: bill_dict[i["userproject"]].update({str(month): None}) bill_dict[i["userproject"]].update( {i["bill_date"]: i["aws_total_cost"]}) return self.write( dict(code=0, msg='获取成功', count=len(bill_dict), data=bill_dict))
def get(self, *args, **kwargs): pageNum = int(self.get_argument('pageNum', default='1', strip=True)) pageSize = int(self.get_argument('pageSize', default='10', strip=True)) bill_date = self.get_argument('bill_date', default=None, strip=True) export_csv = self.get_argument('export_csv', default="0", strip=True) if not bill_date: return self.write(dict(code=-2, msg='关键参数不能为空')) # 判断pageSize合法性 if not 5 <= pageSize <= 100: return self.write(dict(code=-2, msg='pageSize只能介于5和100之间。')) # 判断pageNum合法性 if not 0 < pageNum: return self.write(dict(code=-2, msg='pageSize只能介于5和100之间。')) bill_date = datetime.strptime(bill_date, '%Y-%m') with DBContext('r', const.DEFAULT_DB_KEY) as session: bill_data = session.query(AwsProjectBillReport) \ .filter(AwsProjectBillReport.bill_date == bill_date, ).all() bill_list = [model_to_dict(e) for e in bill_data] for bill in bill_list: bill["bill_date"] = bill["bill_date"][:7] for k, v in bill.items(): if isinstance(v, decimal.Decimal): bill[k] = str( decimal.Decimal(v).quantize( decimal.Decimal('0.00000'))) bill_data = bill_list[(pageNum - 1) * pageSize:pageNum * pageSize] # 返回数据 if export_csv == "1": import csv filename = "buMonthlyBill.csv" data_dict = bill_list headers = [list(i.keys()) for i in data_dict][0] rows = [list(i.values()) for i in data_dict] with open(filename, "w", encoding="utf8", newline="") as csvfile: writer = csv.writer(csvfile) writer.writerow(headers) writer.writerows(rows) self.set_header('Content-Type', 'application/octet-stream') self.set_header('Content-Disposition', 'attachment; filename=' + filename) buf_size = 4096 with open(filename, 'rb') as f: while True: data = f.read(buf_size) if not data: break self.write(data) self.finish() else: return self.write( dict(code=0, msg='获取成功', count=len(bill_list), data=bill_data))
def update_project_budget(): with DBContext('w') as session: data = session.query(AwsProjectBudgetControl).all() bill_list = [model_to_dict(e) for e in data] for i in bill_list: session.query(AwsProjectBudgetControl) \ .filter(AwsProjectBudgetControl.userproject == i.get("userproject"), AwsProjectBudgetControl.bill_date == i.get("bill_date")) \ .update( { AwsProjectBudgetControl.aws_percentage: i.get("aws_total_cost") / i.get("aws_budget_cost") if i.get("aws_budget_cost") else 0, } ) session.commit()
def get(self, *args, **kwargs): resource_id = self.get_argument('resource_id', default=None, strip=True) service_name = self.get_argument('service_name', default=None, strip=True) this_month = datetime.now().replace(day=1, minute=0, hour=0, second=0, microsecond=0) month = self.get_argument('month', default=None, strip=True) if not month: month = this_month else: month = datetime.strptime(month, '%Y-%m') next_month = month + relativedelta(months=+1) month = month.strftime("%Y-%m-01 00:00:00") next_month = next_month.strftime("%Y-%m-01,00:00:00") if not resource_id or not service_name: return self.write(dict(code=-2, msg='关键参数不能为空')) with DBContext('r', const.DEFAULT_DB_KEY) as session: bill_data = session.query(AwsServiceBillReport) \ .filter(AwsServiceBillReport.resource_id == resource_id, AwsServiceBillReport.service_name == service_name, AwsServiceBillReport.bill_date >= month, AwsServiceBillReport.bill_date < next_month, ).all() bill_list = [model_to_dict(e) for e in bill_data] if bill_list: bill_list = sorted( bill_list, key=lambda bill_list: int(bill_list["bill_date"][8:10])) for bill in bill_list: bill["total_cost"] = str( decimal.Decimal(bill['total_cost']).quantize( decimal.Decimal('0.00000'))) # 返回数据 return self.write( dict(code=0, msg='获取成功', count=len(bill_list), data=bill_list))
def get(self, *args, **kwargs): end_day = datetime.now() start_day = end_day - timedelta(days=30) end_day = end_day.strftime("%Y-%m-%d") start_day = start_day.strftime("%Y-%m-%d") with DBContext('r', const.DEFAULT_DB_KEY) as session: data = session \ .query(AWSRiUsageReport) \ .filter(AWSRiUsageReport.date >= start_day) \ .filter(AWSRiUsageReport.date < end_day).all() histories = [model_to_dict(e) for e in data] for history in histories: history["total_running"] = str( decimal.Decimal(history["total_running"]).quantize( decimal.Decimal('0.00000'))) history["total_ri"] = str( decimal.Decimal(history["total_ri"]).quantize( decimal.Decimal('0.00000'))) history["coverage_rate"] = str( decimal.Decimal(history["coverage_rate"]).quantize( decimal.Decimal('0.00000'))) date_list = [] begin_date = datetime.strptime(start_day, "%Y-%m-%d") end_date = datetime.strptime(end_day, '%Y-%m-%d') while begin_date < end_date: date_str = begin_date date_list.append(date_str) begin_date += timedelta(days=1) data_dict = {} for i in histories: name = i["family"] + "." + i["size"] + "." + i["platform"] if name in data_dict.keys(): data_dict[name].update({i["date"]: i["coverage_rate"]}) else: data_dict.update({name: {}}) for date in date_list: data_dict[name].update({str(date): None}) data_dict[name].update({i["date"]: i["coverage_rate"]}) return self.write( dict(code=0, msg='获取成功', count=len(histories), data=data_dict))
def get(self, *args, **kwargs): key = self.get_argument('key', default=None, strip=True) pageNum = int(self.get_argument('page', default='1', strip=True)) pageSize = int(self.get_argument('limit', default='10', strip=True)) with DBContext('r') as session: if key: elasticache_data = session.query(ElastiCache).filter( or_( ElastiCache.idc.like('%{}%'.format(key)), ElastiCache.db_code.like('%{}%'.format(key)), ElastiCache.db_class.like('%{}%'.format(key)), ElastiCache.db_host.like('%{}%'.format(key)), ElastiCache.db_port.like('%{}%'.format(key)), ElastiCache.db_user.like('%{}%'.format(key)), ElastiCache.db_region.like('%{}%'.format(key)), ElastiCache.db_type.like('%{}%'.format(key)), ElastiCache.db_version.like('%{}%'.format(key)), ElastiCache.state.like('%{}%'.format(key)), ElastiCache.db_detail.like('%{}%'.format(key)), ElastiCache.tag_list.like('%{}%'.format(key)), ElastiCache.db_node_num.like('%{}%'.format(key)), )).all() else: elasticache_data = session.query(ElastiCache).all() data_dict = list() for msg in elasticache_data: msg = model_to_dict(msg) msg.pop("create_time") msg.pop("update_time") data_dict.append(msg) elasticache_list_re = data_dict[(pageNum - 1) * pageSize:pageNum * pageSize] self.write( dict(code=0, msg='获取成功', count=len(data_dict), data=elasticache_list_re))
def get(self, *args, **kwargs): nickname = self.get_current_nickname() key = self.get_argument('key', default=None, strip=True) value = self.get_argument('value', default=None, strip=True) page_size = self.get_argument('page', default=1, strip=True) limit = self.get_argument('limit', default='888', strip=True) limit_start = (int(page_size) - 1) * int(limit) db_list = [] with DBContext('r') as session: ### 通过TAG搜索 if key == 'tag_name' and value: count = session.query(DB).outerjoin( DBTag, DB.id == DBTag.db_id).outerjoin( Tag, Tag.id == DBTag.tag_id).filter( Tag.tag_name == value).count() db_info = session.query(DB).outerjoin( DBTag, DB.id == DBTag.db_id).outerjoin( Tag, Tag.id == DBTag.tag_id).filter( Tag.tag_name == value).order_by( DB.id).offset(limit_start).limit(int(limit)) for msg in db_info: tag_list = [] data_dict = model_to_dict(msg) data_dict['create_time'] = str(data_dict['create_time']) data_dict['update_time'] = str(data_dict['update_time']) db_tags = session.query(Tag.tag_name).outerjoin( DBTag, Tag.id == DBTag.tag_id).filter( DBTag.db_id == data_dict['id']).all() for t in db_tags: tag_list.append(t[0]) data_dict['tag_list'] = tag_list db_list.append(data_dict) return self.write( dict(code=0, msg='获取成功', count=count, data=db_list)) ### 监听搜索 if key and key != 'tag_name' and not value: # TODO 超管查所有 if self.is_superuser: count = session.query(DB).filter( or_(DB.db_code.like('%{}%'.format(key)), DB.db_host.like('%{}%'.format(key)), DB.idc.like('%{}%'.format(key)), DB.db_user.like('%{}%'.format(key)), DB.db_pwd.like('%{}%'.format(key)), DB.proxy_host.like('%{}%'.format(key)), DB.db_type.like('%{}%'.format(key)), DB.db_version.like('%{}%'.format(key)), DB.db_mark.like('%{}%'.format(key)), DB.state.like('%{}%'.format(key)), DB.db_env.like('%{}%'.format(key)))).count() db_info = session.query(DB).filter( or_(DB.db_code.like('%{}%'.format(key)), DB.db_host.like('%{}%'.format(key)), DB.db_user.like('%{}%'.format(key)), DB.db_pwd.like('%{}%'.format(key)), DB.proxy_host.like('%{}%'.format(key)), DB.db_type.like('%{}%'.format(key)), DB.db_version.like('%{}%'.format(key)), DB.db_mark.like('%{}%'.format(key)), DB.state.like('%{}%'.format(key)), DB.idc.like('%{}%'.format(key)), DB.db_env.like('%{}%'.format(key)))).order_by( DB.id) else: # TODO 普通用户只能看到有权限的 db_id_list = [] with DBContext('r') as session: the_dbs = session.query(DBTag.db_id).filter( DBTag.tag_id.in_( session.query(Tag.id).filter( or_(Tag.users.like( '%{}%'.format(nickname)))))) for s in the_dbs: db_id_list.append(s[0]) # 去重下列表,万一有重复的呢 set_db_id_list = set(db_id_list) count = session.query(DB).filter( DB.id.in_(set_db_id_list)).filter( or_(DB.db_code.like('%{}%'.format(key)), DB.db_host.like('%{}%'.format(key)), DB.db_user.like('%{}%'.format(key)), DB.db_pwd.like('%{}%'.format(key)), DB.proxy_host.like('%{}%'.format(key)), DB.db_type.like('%{}%'.format(key)), DB.db_version.like('%{}%'.format(key)), DB.db_mark.like('%{}%'.format(key)), DB.state.like('%{}%'.format(key)), DB.idc.like('%{}%'.format(key)), DB.db_env.like( '%{}%'.format(key)))).count() db_info = session.query(DB).filter( DB.id.in_(set_db_id_list)).filter( or_(DB.db_code.like('%{}%'.format(key)), DB.db_host.like('%{}%'.format(key)), DB.db_user.like('%{}%'.format(key)), DB.db_pwd.like('%{}%'.format(key)), DB.proxy_host.like('%{}%'.format(key)), DB.db_type.like('%{}%'.format(key)), DB.db_version.like('%{}%'.format(key)), DB.db_mark.like('%{}%'.format(key)), DB.state.like('%{}%'.format(key)), DB.idc.like('%{}%'.format(key)), DB.db_env.like( '%{}%'.format(key)))).order_by( DB.id).offset(limit_start).limit( int(limit)) for msg in db_info: tag_list = [] data_dict = model_to_dict(msg) data_dict['create_time'] = str(data_dict['create_time']) data_dict['update_time'] = str(data_dict['update_time']) db_tags = session.query(Tag.tag_name).outerjoin( DBTag, Tag.id == DBTag.tag_id).filter( DBTag.db_id == data_dict['id']).all() for t in db_tags: tag_list.append(t[0]) data_dict['tag_list'] = tag_list db_list.append(data_dict) return self.write( dict(code=0, msg='获取成功', count=count, data=db_list)) ### 888查看所有的数据库 if limit == '888': count = session.query(DB).count() db_info = session.query(DB).order_by(DB.id).all() else: # TODO 超管查所有 if self.is_superuser: if key and value: count = session.query(DB).filter_by(**{ key: value }).count() db_info = session.query(DB).filter_by(**{ key: value }).order_by(DB.id).offset(limit_start).limit( int(limit)) else: count = session.query(DB).count() db_info = session.query(DB).order_by( DB.id).offset(limit_start).limit(int(limit)) else: # TODO 普通用户只给有权限的DB,根据用户查Tagid, 根据Tagid查询出来关联的DBID,根据DBID返回主机详情 db_id_list = [] with DBContext('r') as session: # 子查询查出来server_id the_dbs = session.query(DBTag.db_id).filter( DBTag.tag_id.in_( session.query(Tag.id).filter( or_(Tag.users.like( '%{}%'.format(nickname)))))) for d in the_dbs: db_id_list.append(d[0]) # 去重下列表,万一有重复的呢 set_db_id_list = set(db_id_list) if key and value: # 根据Keyvalue获取 count = session.query(DB).filter( DB.id.in_(set_db_id_list)).filter_by(**{ key: value }).count() db_info = session.query(DB).filter( DB.id.in_(set_db_id_list)).filter_by(**{ key: value }).order_by(DB.id).offset(limit_start).limit( int(limit)) else: # 获取主机详情 count = session.query(DB).filter( DB.id.in_(set_db_id_list)).count() db_info = session.query(DB).filter( DB.id.in_(set_db_id_list)).offset( limit_start).limit(int(limit)) for msg in db_info: tag_list = [] data_dict = model_to_dict(msg) db_tags = session.query(Tag.tag_name).outerjoin( DBTag, Tag.id == DBTag.tag_id).filter( DBTag.db_id == data_dict['id']).all() for t in db_tags: tag_list.append(t[0]) data_dict['create_time'] = str(data_dict['create_time']) data_dict['update_time'] = str(data_dict['update_time']) data_dict['tag_list'] = tag_list db_list.append(data_dict) self.write(dict(code=0, msg='获取成功', count=count, data=db_list))
def get(self, *args, **kwargs): pageNum = int(self.get_argument('pageNum', default='1', strip=True)) pageSize = int(self.get_argument('pageSize', default='10', strip=True)) key = self.get_argument('key', default=None, strip=True) export_csv = self.get_argument('export_csv', default="0", strip=True) d = datetime.now().strftime('%Y-%m-%d') d_start = d + ' 00:00:00' d_end = d + ' 23:59:59' if not 5 <= pageSize <= 100: return self.write(dict(code=400, msg='pageSize只能介于5和100之间。')) if not 0 < pageNum: return self.write(dict(code=400, msg='pageSize只能介于5和100之间。')) with DBContext('r', const.DEFAULT_DB_KEY) as session: data = session\ .query(AWSRiUsageReport)\ .filter(AWSRiUsageReport.date >= d_start)\ .filter(AWSRiUsageReport.date <= d_end) if key is not None: data = data.filter( AWSRiUsageReport.platform.like("%" + key + "%")) data = data.all() data_num = session.query(AWSRiDateDB).all() usage_list = [model_to_dict(e) for e in data] usage_date_numlist = [model_to_dict(e) for e in data_num] for i in usage_date_numlist: i["total_ri"] = str( decimal.Decimal(i["total_ri"]).quantize( decimal.Decimal('0.00000'))) total_ri_num = 0 rotal_running = 0 for usage in usage_list: usage["total_running"] = int( decimal.Decimal(usage["total_running"]).quantize( decimal.Decimal('0.00000'))) usage["total_ri"] = int( decimal.Decimal(usage["total_ri"]).quantize( decimal.Decimal('0.00000'))) usage["coverage_rate"] = str( decimal.Decimal(usage["coverage_rate"]).quantize( decimal.Decimal('0.00000'))) usage["end"] = {} total_ri_num += usage["total_ri"] rotal_running += usage["total_running"] for ri in usage_date_numlist: for usage in usage_list: if ri["family"] == usage["family"] and ri["size"] == usage[ "size"] and ri["platform"] == usage["platform"]: if ri["end"] in usage["end"].keys(): usage["end"][ri["end"]] += ri["total_ri"] else: usage["end"].update({ri["end"]: ri["total_ri"]}) total = len(usage_list) pageTotal = (total + pageSize if total % pageSize >= 0 else 0) // pageSize pageNum = min([pageNum, pageTotal]) _pn = pageNum - 1 ec2_data = usage_list[_pn * pageSize:pageNum * pageSize + 1] if export_csv == "1": filename = "ri_report.csv" data_dict = ec2_data headers = [list(i.keys()) for i in data_dict][0] rows = [list(i.values()) for i in data_dict] with open(filename, "w", encoding="utf8", newline="") as csvfile: writer = csv.writer(csvfile) writer.writerow(headers) writer.writerows(rows) self.set_header('Content-Type', 'application/octet-stream') self.set_header('Content-Disposition', 'attachment; filename=' + filename) buf_size = 4096 with open(filename, 'rb') as f: while True: data = f.read(buf_size) if not data: break self.write(data) self.finish() else: return self.write( dict(code=0, msg='获取成功', count=total, pageTotal=pageTotal, total_ri=total_ri_num / rotal_running if total_ri_num / rotal_running < 1 else 1, data=ec2_data))
def get(self, *args, **kwargs): month_default = datetime.now().strftime( '%Y-%m-01') #当前月份的的1号 格式:2020-06-01 month = self.get_argument('month', default=month_default, strip=True) pageNum = int(self.get_argument('pageNum', default='1', strip=True)) pageSize = int(self.get_argument('pageSize', default='10', strip=True)) key = self.get_argument('key', default=None, strip=True) export_csv = self.get_argument('export_csv', default="0", strip=True) #判断pageSize合法性 if not 5 <= pageSize <= 100: return self.write(dict(code=400, msg='pageSize只能介于5和100之间。')) # 判断pageNum合法性 if not 0 < pageNum: return self.write(dict(code=400, msg='pageSize只能介于5和100之间。')) with DBContext('r', const.DEFAULT_DB_KEY) as session: #过滤要显示的月份 ec2_data = session.query(UsageReport).filter( UsageReport.month == month) #模糊查询key值处理 if key is not None: ec2_data = ec2_data.filter( UsageReport.host_name.like("%" + key + "%")) ec2_data = ec2_data.all() #处理页码和每页显示条数 ec2_list = [model_to_dict(e) for e in ec2_data] total = len(ec2_list) pageTotal = (total + pageSize if total % pageSize > 0 else 0) // pageSize # pageNum = min([pageNum, pageTotal]) # _pn = pageNum - 1 # ec2_data = ec2_list[_pn * pageSize: pageNum * pageSize] ec2_data = ec2_list[(pageNum - 1) * pageSize:pageNum * pageSize] for ec2 in ec2_data: # ec2["cpu_avg_usage"] = str(ec2["cpu_avg_usage"])+"%" if ec2["cpu_avg_usage"] else "" # ec2["mem_avg_usage"] = str(ec2["mem_avg_usage"])+"%" if ec2["mem_avg_usage"] else "" # ec2["disk_avg_usage"] = str(ec2["disk_avg_usage"])+"%" if ec2["disk_avg_usage"] else "" ec2["cost_gap"] = str( decimal.Decimal(ec2['cost_gap']).quantize( decimal.Decimal('0.00000'))) if export_csv == "1": import csv filename = "UsageReport.csv" data_dict = ec2_data headers = [list(i.keys()) for i in data_dict][0] rows = [list(i.values()) for i in data_dict] with open(filename, "w", encoding="utf8", newline="") as csvfile: writer = csv.writer(csvfile) writer.writerow(headers) writer.writerows(rows) self.set_header('Content-Type', 'application/octet-stream') self.set_header('Content-Disposition', 'attachment; filename=' + filename) buf_size = 4096 with open(filename, 'rb') as f: while True: data = f.read(buf_size) if not data: break self.write(data) self.finish() else: return self.write( dict(code=0, msg='获取成功', count=total, pageTotal=pageTotal, data=ec2_data))
def get(self, *args, **kwargs): key = self.get_argument('key', default=None, strip=True) page_size = self.get_argument('page', default=1, strip=True) limit = int(self.get_argument('limit', default=15, strip=True)) export_csv = self.get_argument('export_csv', default="0", strip=True) limit_start = (int(page_size) - 1) * int(limit) result_list = [] with DBContext('r') as session: if key: # 模糊查所有 host_info = session.query(Security_Host).filter( or_( Security_Host.server_name.like('%{}%'.format(key)), Security_Host.server_instance_id.like( '%{}%'.format(key)), Security_Host.server_private_ip.like( '%{}%'.format(key)), Security_Host.server_public_ip.like( '%{}%'.format(key)), Security_Host.server_Project.like('%{}%'.format(key)), Security_Host.server_mark.like('%{}%'.format(key)), Security_Host.security_state.like('%{}%'.format(key)), Security_Host.risk_port.like('%{}%'.format(key)), Security_Host.security_group.like( '%{}%'.format(key)))).order_by(Security_Host.id) count = host_info.count() else: host_info = session.query(Security_Host).order_by( Security_Host.id) count = host_info.count() host_info = host_info[limit_start:limit_start + limit] for data in host_info: data_dict = model_to_dict(data) result_list.append(data_dict) if export_csv == "1": import csv filename = "DangersOpenPort.csv" data_dict = result_list headers = [list(i.keys()) for i in data_dict][0] rows = [list(i.values()) for i in data_dict] with open(filename, "w", encoding="utf8", newline="") as csvfile: writer = csv.writer(csvfile) writer.writerow(headers) writer.writerows(rows) self.set_header('Content-Type', 'application/octet-stream') self.set_header('Content-Disposition', 'attachment; filename=' + filename) buf_size = 4096 with open(filename, 'rb') as f: while True: data = f.read(buf_size) if not data: break self.write(data) self.finish() else: return self.write( dict(code=0, msg='获取成功', count=count, data=result_list))
def get(self, *args, **kwargs): userproject = self.get_argument('userproject', default=None, strip=True) this_month = datetime.now().replace(day=1, minute=0, hour=0, second=0, microsecond=0) month = self.get_argument('month', default=None, strip=True) if not month: month = this_month else: month = datetime.strptime(month, '%Y-%m') next_month = month + relativedelta(months=+1) month = month.strftime("%Y-%m-01 00:00:00") next_month = next_month.strftime("%Y-%m-01 00:00:00") if not userproject: return self.write(dict(code=-2, msg='关键参数不能为空')) with DBContext('r', const.DEFAULT_DB_KEY) as session: bill_data = session.query(AwsServiceBillReport) \ .filter(AwsServiceBillReport.userproject == userproject, AwsServiceBillReport.bill_date >= month, AwsServiceBillReport.bill_date < next_month, ).all() bill_list = [model_to_dict(e) for e in bill_data] for bill in bill_list: bill["total_cost"] = str( decimal.Decimal(bill['total_cost']).quantize( decimal.Decimal('0.00000'))) date_list = [] begin_date = datetime.strptime(month, "%Y-%m-%d %H:%M:%S") end_date = datetime.strptime(next_month, '%Y-%m-%d %H:%M:%S') while begin_date < end_date: date_str = begin_date date_list.append(str(date_str)[:10]) begin_date += timedelta(days=1) #server_list = [] #[server_list.append(i["service_name"]) for i in bill_list if i["service_name"] not in server_list ] server_list = ["EC2", "ElastiCache", "RDS", "EBS"] bill_data = {} for i in server_list: bill_data.update({i: {}}) for j in date_list: bill_data[i].update({str(j): None}) for i in bill_list: i["bill_date"] = i["bill_date"][:10] if i["service_name"] in server_list: if i["bill_date"] in bill_data[i["service_name"]].keys(): if bill_data[i["service_name"]][i["bill_date"]] == None: bill_data[i["service_name"]][i["bill_date"]] = 0 bill_data[i["service_name"]][i["bill_date"]] += float( i["total_cost"]) else: bill_data[i["service_name"]][i["bill_date"]] = float( i["total_cost"]) return self.write( dict(code=0, msg='获取成功', count=len(bill_data), data=bill_data))
def get(self, *args, **kwargs): key = self.get_argument('key', default=None, strip=True) value = self.get_argument('value', default=None, strip=True) page_size = self.get_argument('page', default=1, strip=True) limit = self.get_argument('limit', default='888', strip=True) limit_start = (int(page_size) - 1) * int(limit) db_list = [] with DBContext('r') as session: ### 通过TAG搜索 if key == 'tag_name' and value: count = session.query(DB).outerjoin( DBTag, DB.id == DBTag.db_id).outerjoin( Tag, Tag.id == DBTag.tag_id).filter( Tag.tag_name == value).count() db_info = session.query(DB).outerjoin( DBTag, DB.id == DBTag.db_id).outerjoin( Tag, Tag.id == DBTag.tag_id).filter( Tag.tag_name == value).order_by( DB.id).offset(limit_start).limit(int(limit)) for msg in db_info: tag_list = [] data_dict = model_to_dict(msg) data_dict['create_time'] = str(data_dict['create_time']) db_tags = session.query(Tag.tag_name).outerjoin( DBTag, Tag.id == DBTag.tag_id).filter( DBTag.db_id == data_dict['id']).all() for t in db_tags: tag_list.append(t[0]) data_dict['tag_list'] = tag_list db_list.append(data_dict) return self.write( dict(code=0, msg='获取成功', count=count, data=db_list)) ### 监听搜索 if key and key != 'tag_name' and not value: count = session.query(DB).filter( or_(DB.db_code.like('%{}%'.format(key)), DB.db_host.like('%{}%'.format(key)), DB.db_user.like('%{}%'.format(key)), DB.db_pwd.like('%{}%'.format(key)), DB.proxy_host.like('%{}%'.format(key)), DB.db_type.like('%{}%'.format(key)), DB.db_version.like('%{}%'.format(key)), DB.db_mark.like('%{}%'.format(key)), DB.state.like('%{}%'.format(key)), DB.db_env.like('%{}%'.format(key)))).count() db_info = session.query(DB).filter( or_(DB.db_code.like('%{}%'.format(key)), DB.db_host.like('%{}%'.format(key)), DB.db_user.like('%{}%'.format(key)), DB.db_pwd.like('%{}%'.format(key)), DB.proxy_host.like('%{}%'.format(key)), DB.db_type.like('%{}%'.format(key)), DB.db_version.like('%{}%'.format(key)), DB.db_mark.like('%{}%'.format(key)), DB.state.like('%{}%'.format(key)), DB.db_env.like('%{}%'.format(key)))).order_by( DB.id).offset(limit_start).limit(int(limit)) for msg in db_info: tag_list = [] data_dict = model_to_dict(msg) data_dict['create_time'] = str(data_dict['create_time']) db_tags = session.query(Tag.tag_name).outerjoin( DBTag, Tag.id == DBTag.tag_id).filter( DBTag.db_id == data_dict['id']).all() for t in db_tags: tag_list.append(t[0]) data_dict['tag_list'] = tag_list db_list.append(data_dict) return self.write( dict(code=0, msg='获取成功', count=count, data=db_list)) ### 888查看所有的数据库 if limit == '888': count = session.query(DB).count() db_info = session.query(DB).order_by(DB.id).all() else: if key and value: count = session.query(DB).filter_by(**{key: value}).count() db_info = session.query(DB).filter_by(**{ key: value }).order_by(DB.id).offset(limit_start).limit(int(limit)) else: count = session.query(DB).count() db_info = session.query(DB).order_by( DB.id).offset(limit_start).limit(int(limit)) for msg in db_info: tag_list = [] data_dict = model_to_dict(msg) db_tags = session.query(Tag.tag_name).outerjoin( DBTag, Tag.id == DBTag.tag_id).filter( DBTag.db_id == data_dict['id']).all() for t in db_tags: tag_list.append(t[0]) data_dict['create_time'] = str(data_dict['create_time']) data_dict['tag_list'] = tag_list db_list.append(data_dict) self.write(dict(code=0, msg='获取成功', count=count, data=db_list))
def get(self, *args, **kwargs): pageNum = int(self.get_argument('pageNum', default='1', strip=True)) pageSize = int(self.get_argument('pageSize', default='10', strip=True)) service_name = self.get_argument('service_name', default=None, strip=True) this_month = datetime.now().replace(day=1, minute=0, hour=0, second=0, microsecond=0) month = self.get_argument('month', default=None, strip=True) export_csv = self.get_argument('export_csv', default="0", strip=True) if not month: month = this_month else: month = datetime.strptime(month, '%Y-%m') next_month = month + relativedelta(months=+1) month = month.strftime("%Y-%m-01 00:00:00") next_month = next_month.strftime("%Y-%m-01,00:00:00") if not 5 <= pageSize <= 100: return self.write(dict(code=-2, msg='pageSize只能介于5和100之间。')) if not 0 < pageNum: return self.write(dict(code=-2, msg='pageSize只能介于5和100之间。')) with DBContext('r', const.DEFAULT_DB_KEY) as session: bill_data = session.query(AwsServiceBillReport) \ .filter(AwsServiceBillReport.service_name == service_name, AwsServiceBillReport.bill_date >= month, AwsServiceBillReport.bill_date < next_month, ).all() bill_list = [model_to_dict(e) for e in bill_data] bill_dict = {} for bill in bill_list: if bill["resource_id"] in bill_dict.keys(): bill_dict[ bill["resource_id"]]["total_cost"] += bill["total_cost"] else: bill_dict.update({bill["resource_id"]: bill}) bill_dict[ bill["resource_id"]]["total_cost"] += bill["total_cost"] bill_list = [v for k, v in bill_dict.items()] for bill in bill_list: bill["total_cost"] = str( decimal.Decimal(bill['total_cost']).quantize( decimal.Decimal('0.00000'))) bill["bill_date"] = bill["bill_date"][:7] bill_data = bill_list[(pageNum - 1) * pageSize:pageNum * pageSize] # 返回数据 if export_csv == "1": import csv filename = "ServerMonthlyBill.csv" data_dict = bill_list headers = [list(i.keys()) for i in data_dict][0] rows = [list(i.values()) for i in data_dict] with open(filename, "w", encoding="utf8", newline="") as csvfile: writer = csv.writer(csvfile) writer.writerow(headers) writer.writerows(rows) self.set_header('Content-Type', 'application/octet-stream') self.set_header('Content-Disposition', 'attachment; filename=' + filename) buf_size = 4096 with open(filename, 'rb') as f: while True: data = f.read(buf_size) if not data: break self.write(data) self.finish() else: return self.write( dict(code=0, msg='获取成功', count=len(bill_list), data=bill_data))