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