def post(self, *args, **kwargs):
data = json.loads(self.request.body.decode("utf-8"))
db_obj = {}
db_obj['host'] = data['db_host']
db_obj['port'] = int(data['db_port'])
db_obj['user'] = data['db_user']
db_obj['passwd'] = data['db_pwd']
db_obj['db'] = data['db_instance']
if len(data['db_pwd']) == 32:
db_obj['passwd'] = decrypt(data['db_pwd'])
try:
if data['db_type'] == 'mysql':
mysql_conn = MysqlBase(**db_obj)
res = mysql_conn.test()
elif data['db_type'] == 'oracle':
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.test()
except Exception as e:
return self.write(dict(code=-1, msg='%s' % e))
return self.write(dict(code=0, msg='连接成功'))
def get_serverObjList(ip_list=[]):
"""
:return: [{"ip": "39.104.83.140", "port": "22", "username": "******", "password": "******"}]
"""
serverObjList = []
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
if len(ip_list) > 0:
sql = '''
select a.ip,a.`port`,b.system_user,b.`password` from asset_server a,admin_users b
where a.admin_user = b.admin_user and a.ip in ("{}")
'''.format('","'.join(ip_list))
else:
sql = '''
select a.ip,a.`port`,b.system_user,b.`password` from asset_server a,admin_users b
where a.admin_user = b.admin_user
'''
server_info = mysql_conn.query(sql)
for ip, port, username, password in server_info:
data = {}
data['ip'] = ip
data['port'] = port
data['username'] = username
data['password'] = decrypt(password)
serverObjList.append(data)
return serverObjList
def toexcel(all_customized_list, asset_date, AssetSql_date):
for j in all_customized_list: # 执行时间表
for q_id, q_header, q_dbname_id, q_totype, q_sqlstr in AssetSql_date: # sql语句表
if q_id == int(j[1]): # 脚本id相等
for s_id, s_db_host, s_db_port, s_db_user, s_db_pwd, s_db_type, s_db_instance in asset_date: # 数据库源
if str(s_id) == q_dbname_id: # 数据源id相等
CUSTOM_DB_INFO = dict(
host=s_db_host,
port=s_db_port,
user=s_db_user,
passwd=decrypt(s_db_pwd),
db=s_db_instance # 库名
)
try:
if s_db_type == "mysql":
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
db_data = mysql_conn.query(q_sqlstr)
if s_db_type == "oracle":
oracle_conn = OracleBase(**CUSTOM_DB_INFO)
db_data = oracle_conn.query(q_sqlstr)
except:
traceback.print_exc()
db_data_list = []
for g in db_data:
db_data_list.append(list(g))
# 解析excel表头
# q_header.split('|')
temp_copy = copy.deepcopy(q_header)
temp_copy2 = temp_copy.split('|')
ins_log.read_log(
'info', "11111111111111111111111111111111111111")
ins_log.read_log('info', temp_copy2)
ins_log.read_log(
'info', "11111111111111111111111111111111111111")
# 保存文件
Base_DIR = os.path.dirname(
os.path.dirname(os.path.abspath(__file__)))
upload_path = '{}/static/report/'.format(Base_DIR)
upload_path = upload_path + j[3]
# 创建的目录
if not os.path.exists(upload_path):
os.mkdir(upload_path)
file_path = upload_path + '/' + j[0] + str(
datetime.datetime.now().strftime(
'%Y%m%d-%H:%M:%S')) + ".xls"
writer = pd.ExcelWriter(file_path, engine='xlsxwriter')
df = pd.DataFrame(db_data_list,
index=range(1,
len(db_data_list) + 1),
columns=temp_copy2)
df.to_excel(writer,
encoding='utf-8',
sheet_name='Sheet')
writer.save()
def run():
DB_INFO = getDBList()
for db in DB_INFO:
try:
# 解密密码
db_pwd = decrypt(db[5])
db_conf = {
'host': db[2],
'port': int(db[3]),
'user': db[4],
'passwd': db_pwd,
'db': db[6]
}
if not db[6]:
if db[1] == 'mysql':
db_conf['db'] = 'mysql'
if db[1] == 'oracle':
db_conf['db'] = 'orcl'
if db[1] == 'mysql':
db_conn = MysqlBase(**db_conf)
if db[1] == 'oracle':
db_conn = OracleBase(**db_conf)
if db_conn.test():
state = 'Running'
else:
state = 'failed'
CUSTOM_DB_INFO['db'] = 'codo_cmdb'
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
up_sql = '''update codo_cmdb.asset_db set state = '%s' where id = %s''' % (
state, db[0])
mysql_conn.change(up_sql)
except:
traceback.print_exc()
return 'failed'
print(state)
return 'ok'
def mysqlConnTest(data_dict):
############# 监测数据库连接情况，后续放着定时任务里面# ############
try:
# 解密密码
db_pwd = decrypt(data_dict['db_pwd'])
db_conf = {
'host': data_dict['db_host'],
'port': int(data_dict['db_port']),
'user': data_dict['db_user'],
'passwd': db_pwd,
'db': READONLY_DB_DBNAME
}
mysql_conn = MysqlBase(**db_conf)
if mysql_conn.test():
data_dict['state'] = 'Running'
else:
data_dict['state'] = 'failed'
del mysql_conn
except:
data_dict['state'] = 'failed'
def renlian():
print("人脸识别率统计：开始")
temp_sql_str5 = "select sum(decode(rlsbzt,'1',1,0)) as cgl , count(*) as zl ,sum(decode(rlsbzt,'1',1,0))/count(*) from net_mhs_face_result where cjsj>=to_date('startdate','yyyymmdd') and cjsj<to_date('enddate','yyyymmdd')"
today = str(datetime.now().strftime('%Y%m')) + "01" # 当前月份
now = datetime.now()
date = now + dateutil.relativedelta.relativedelta(months=-1) # 上个月时间
befor_day = str(date.strftime('%Y%m')) + "01" #上个月份
temp_sql_str5 = str(temp_sql_str5).replace('startdate', befor_day)
sql_str5 = str(temp_sql_str5).replace('enddate', today)
print("9999999999999999999999")
print(sql_str5)
print(today)
print(befor_day)
print(sql_str5)
print("9999999999999999999999")
sql_str = "select id,db_host,db_port,db_user,db_pwd,db_type,db_instance from asset_db"
asset_date = getdatebase("codo_cmdb", sql_str) # 数据库源
for s_id, s_db_host, s_db_port, s_db_user, s_db_pwd, s_db_type, s_db_instance in asset_date: # 数据库源
CUSTOM_DB_INFO = dict(
host=s_db_host,
port=s_db_port,
user=s_db_user,
passwd=decrypt(s_db_pwd),
db=s_db_instance # 库名
)
print("11111111111111111111111111111111111111")
print(CUSTOM_DB_INFO)
print("11111111111111111111111111111111111111")
try:
if s_db_type == "mysql":
mysql_conn = MysqlBase(**CUSTOM_DB_INFO)
db_data = mysql_conn.query(sql_str5)
if s_db_type == "oracle":
oracle_conn = OracleBase(**CUSTOM_DB_INFO)
db_data = oracle_conn.query(sql_str5)
except:
traceback.print_exc()
#duanxin_date = getdatebase("codo_task", sql_str5)
# 获取当前月份
set_day = "'" + datetime.now().strftime('%Y-%m') + "'"
sql_str = "select id,renlian_defen,renlian_remarks from scorelist where today = " + set_day
meter_date = getdatebase("codo_task", sql_str)
if len(meter_date):
# if toSeconds > 0:
defenlist = []
get_id, get_defen, get_remarks = meter_date[0]
defen_sum = int(get_defen)
remarkslist = eval(get_remarks)
remarks = {}
remarks["gettime"] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
remarks["getlongtime"] = 0
remarkslist.append(remarks)
for i in remarkslist:
defenlist.append(int(i["getlongtime"])) # 获取时长列表，取最长的时长
if max(defenlist) < 2 * 60: # 监测用户网上支付成功交易的平均操作时长，对于用户平均操作时长小于2分钟的，得5分
defen_sum = 5
toremark = '"' + str(remarkslist) + '"'
defen_str = '"' + str(defen_sum) + '"'
sql_str3 = "update scorelist set duanxin_defen = " + defen_str + "," + " duanxin_remarks=" + toremark + " where id=" + str(
get_id)
# print(sql_str3)
get_num = change_datebase("codo_task", sql_str3)
else:
# 新增一条数据
remarkslist = []
remarks = {} # {"gettime":"","getlongtime":""}
remarks["gettime"] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
remarks["getlongtime"] = 0
remarkslist.append(remarks)
toremark = '"' + str(remarkslist) + '"'
set_creattime = '"' + datetime.now().strftime(
'%Y-%m-%d %H:%M:%S') + '"'
defen_sum = 10
defen_str = '"' + str(defen_sum) + '"'
temp_str = " , " + defen_str + "," + toremark
sql_str2 = "insert into scorelist(today,yunxing_defen,yunxing_remarks,create_time) " \
+ " values(" + set_day + temp_str + set_creattime + ")"
get_num = change_datebase("codo_task", sql_str2)
def get(self):
qid = self.get_argument('qid', default=None, strip=True)
dict_list = []
errormsg = ''
try:
# 配置信息
query_info = {}
mysql_conn = getConn('codo_task')
sql = '''
select id,title,dblinkId,`database`,`user`,`password`,`sql`,colnames,timesTy,timesTyVal,colalarms,
`status`,create_time,update_time,description,seq,groupID from custom_query where id = %s
''' % qid
resp = mysql_conn.query(sql)
for id, title, dblinkId, database, user, password, sql, colnames, timesTy, timesTyVal, colalarms, status, \
create_time, update_time, description, seq, groupID in resp:
query_info['id'] = id
query_info['title'] = title
query_info['dblinkId'] = dblinkId
query_info['database'] = database
query_info['user'] = user
query_info['password'] = password
query_info['sql'] = sql
query_info['colnames'] = colnames
query_info['timesTy'] = timesTy
query_info['timesTyVal'] = timesTyVal
query_info['colalarms'] = colalarms
query_info['status'] = status
query_info['create_time'] = create_time
query_info['update_time'] = update_time
query_info['description'] = description
query_info['seq'] = seq
query_info['groupID'] = groupID
dblinkId = query_info['dblinkId']
# 获取数据库源 连接地址
select_db = '''
select db_type, db_host, db_port, db_user, db_pwd, db_instance
from codo_cmdb.asset_db where id = {}
'''.format(dblinkId)
db_info = mysql_conn.query(select_db)
except:
errormsg = '获取数据库源连接信息失败'
return self.write(dict(code=-1, msg='获取失败', errormsg=errormsg, data=[]))
if len(db_info) > 0:
db = db_info[0]
db_obj = {}
db_obj['host'] = db[1]
db_obj['port'] = int(db[2])
db_obj['user'] = db[3]
db_obj['passwd'] = decrypt(db[4])
if query_info['database']:
db_obj['db'] = query_info['database']
else:
db_obj['db'] = db[5]
sql = query_info['sql']
if query_info['user']:
db_obj['user'] = query_info['user']
if query_info['password']:
db_obj['passwd'] = decrypt(query_info['password'])
sql = re.sub('update|drop', '', sql, 0, re.I)
# ins_log.read_log('info', db_obj)
res = []
try:
if db[0] == 'mysql':
mysql_conn = MysqlBase(**db_obj)
res = mysql_conn.query(sql)
if db[0] == 'oracle':
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.query(sql)
except Exception as e:
errormsg = '%s 数据库: 查询失败, %s' % (db_obj['host'], e)
return self.write(dict(code=-1, msg='获取失败', errormsg=errormsg, data=[]))
if res:
try:
colnames = json.loads(query_info['colnames'])
colalarms = json.loads(query_info['colalarms'])
# 增加状态列
if len(colalarms) > 0:
colnames.append({'col': "target", 'name': "指标"})
dict_key = []
for i in colnames:
dict_key.append(i['col'])
for i in res:
_d = dict(zip(dict_key, i))
for selColObj in colalarms:
# 判断指标值 (同少取最少，同大取最大)
selCol = selColObj['selCol']
if selCol in _d:
dbval = _d[selCol]
if not dbval:
dbval = 0
subColList = selColObj['subColList']
subColList = sorted(subColList, key=lambda x: TypeObj[x['alarmType']], reverse=True)
# ins_log.read_log('info', subColList)
for alarmObj in subColList:
sign = alarmObj['sign']
alarmVal = alarmObj['alarmVal']
if sign == '>' and float(dbval) > float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '<' and float(dbval) < float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '>=' and float(dbval) >= float(alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<=' and float(dbval) <= float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '=' and float(dbval) == float(alarmVal):
_d['target'] = alarmObj['alarmType']
if 'target' not in _d:
_d['target'] = '未知'
# ins_log.read_log('info', _d)
dict_list.append(_d)
if len(colalarms) > 0:
dict_list.sort(key=lambda x: TypeObj[x['target']], reverse=True)
countObj = dict(Counter([i['target'] for i in dict_list]))
else:
countObj = {}
except Exception as e:
traceback.print_exc()
dict_list = []
countObj = {}
errormsg = '字段格式错误'
return self.write(dict(code=-2, msg='获取失败', errormsg=errormsg, data=[]))
# 转换 时间类型字段
for _d in dict_list:
for k, v in _d.items():
if isinstance(v, datetime.datetime):
_d[k] = v.strftime("%Y-%m-%d %H:%M:%S")
return self.write(dict(code=0, msg='获取成功', errormsg=errormsg, data=dict_list, count=countObj))
return self.write(dict(code=-1, msg='获取失败', errormsg=errormsg, data=[], count={}))
def getData(query_info, db_info):
dict_list = []
errormsg = ''
db = db_info[0]
db_obj = {}
db_obj['host'] = db[1]
db_obj['port'] = int(db[2])
db_obj['user'] = db[3]
db_obj['passwd'] = decrypt(db[4])
if query_info.database:
db_obj['db'] = query_info.database
else:
db_obj['db'] = db[5]
sql = query_info.sql
if query_info.user:
db_obj['user'] = query_info.user
if query_info.password:
db_obj['passwd'] = decrypt(query_info.password)
sql = re.sub('update|drop', '', sql, 0, re.I)
# ins_log.read_log('info', db_obj)
res = []
try:
if db[0] == 'mysql':
mysql_conn = MysqlBase(**db_obj)
res = mysql_conn.query(sql)
if db[0] == 'oracle':
oracle_conn = OracleBase(**db_obj)
res = oracle_conn.query(sql)
except Exception as e:
errormsg = '%s 数据库: 查询失败, %s' % (db_obj['host'], e)
return dict(code=-1, msg='获取失败', errormsg=errormsg, data=[])
if res:
try:
colnames = json.loads(query_info.colnames)
colalarms = json.loads(query_info.colalarms)
# 增加状态列
if len(colalarms) > 0:
colnames.append({'col': "target", 'name': "指标"})
dict_key = []
for i in colnames:
dict_key.append(i['col'])
for i in res:
_d = dict(zip(dict_key, i))
for selColObj in colalarms:
# 判断指标值 (同少取最少，同大取最大)
selCol = selColObj['selCol']
if selCol in _d:
dbval = _d[selCol]
if not dbval:
dbval = 0
subColList = selColObj['subColList']
subColList = sorted(
subColList,
key=lambda x: TypeObj[x['alarmType']],
reverse=True)
# ins_log.read_log('info', subColList)
for alarmObj in subColList:
sign = alarmObj['sign']
alarmVal = alarmObj['alarmVal']
if sign == '>' and float(dbval) > float(alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<' and float(dbval) < float(alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '>=' and float(dbval) >= float(
alarmVal):
_d['target'] = alarmObj['alarmType']
break
if sign == '<=' and float(dbval) <= float(
alarmVal):
_d['target'] = alarmObj['alarmType']
if sign == '=' and float(dbval) == float(alarmVal):
_d['target'] = alarmObj['alarmType']
if 'target' not in _d:
_d['target'] = '未知'
# ins_log.read_log('info', _d)
dict_list.append(_d)
if len(colalarms) > 0:
dict_list.sort(key=lambda x: TypeObj[x['target']],
reverse=True)
countObj = dict(Counter([i['target'] for i in dict_list]))
else:
countObj = {}
except Exception as e:
traceback.print_exc()
errormsg = '字段格式错误'
return dict(code=-2, msg='获取失败', errormsg=errormsg, data=[])
# 转换 时间类型字段
for _d in dict_list:
for k, v in _d.items():
if isinstance(v, datetime.datetime):
_d[k] = v.strftime("%Y-%m-%d %H:%M:%S")
return dict(code=0,
msg='获取成功',
errormsg=errormsg,
data=dict_list,
count=countObj)
else:
return dict(code=-1, msg='获取失败', errormsg='没有数据库源', data=[], count={})