def export_data(self):
params = self.req.dict_req
params['limit'] = int(params['limit']) if 'limit' in params else 10
params['page'] = int(params['page']) if 'page' in params else 1
result = self.mysqldb.find_data(self.table_name, params)
# print(result)
if result:
file_path = os.path.dirname(
os.path.dirname(__file__)
) + '/static/uploadFile/' + self.table_name + '_export.csv'
list_title = [
'设备编号', '设备名称', 'IP地址', '所属节点', '所属厂商', '设备类型', '操作系统', '版本号',
'CPU', '内存', '磁盘空间', '功率', '地址', '备注', '上线时间'
]
list_title_en = [
'code', 'name', 'ip', 'level', 'factory', 'device_type', 'os',
'os_version', 'cpu', 'ram', 'disk', 'power', 'address',
'message', 'online_time'
]
mycsv = my_csv.model()
result = mycsv.write(file_path, list_title, list_title_en,
result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def export_data(self):
params = self.req.dict_req
params['limit'] = -1
params['page'] = 1
if 'time_start' not in params['whereJson'] or 'time_end' not in params['whereJson']:
dict_res = {'code': 500, 'msg': 'whereJson错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
if params['whereJson']['time_start'] == '' or params['whereJson']['time_end'] == '':
dict_res = {'code': 500, 'msg': 'whereJson错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
if 'ip' in params['whereJson']:
params['whereJson']['server_ip'] = params['whereJson']['ip']
del params['whereJson']['ip']
params['whereJson']['log_time'] = {'$gte': params['whereJson']['time_start'], '$lt': params['whereJson']['time_end']}
del params['whereJson']['time_start']
del params['whereJson']['time_end']
params['sortJson'] = {'log_time': 1}
result = self.mysqldb.find_data(self.table_name, params)
# print(result)
if result:
file_path = os.path.dirname(os.path.dirname(__file__)) + '/static/uploadFile/'+ self.table_name +'_export.csv'
list_title = ['设备IP', '域名', '客户端IP', '端口', '类型', '时间']
list_title_en = ['server_ip','client_host','client_ip','client_port','type_name','log_time']
mycsv = my_csv.model()
result = mycsv.write(file_path,list_title,list_title_en,result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def export_data(self):
params = self.req.dict_req
params['limit'] = int(params['limit']) if 'limit' in params else 10
params['page'] = int(params['page']) if 'page' in params else 1
result = self.mysqldb.find_data(self.table_name, params)
# print(result)
if result:
file_path = os.path.dirname(
os.path.dirname(__file__)
) + '/static/uploadFile/' + self.table_name + '_export.csv'
list_title = ['用户', '设备IP', '操作类型', '操作信息', '时间']
list_title_en = [
'username', 'client_ip', 'operation_type', 'operation_msg',
'log_time'
]
mycsv = my_csv.model()
result = mycsv.write(file_path, list_title, list_title_en,
result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def export_data(self):
params = self.req.dict_req
params['limit'] = int(params['limit']) if 'limit' in params else 10
params['page'] = int(params['page']) if 'page' in params else 1
result = self.mysqldb.find_data(self.table_name, params)
# print(result)
if result:
file_path = os.path.dirname(
os.path.dirname(__file__)
) + '/static/uploadFile/' + self.table_name + '_export.csv'
list_title = [
'设备IP', 'CPU使用率', '内存使用率', '接收流量(Kb)', '发送流量(Kb)', '网速(Kb/s)',
'时间'
]
list_title_en = [
'client_ip', 'cpu_rate', 'ram_rate', 'net_flow_receive',
'net_flow_send', 'net_speed', 'log_time'
]
mycsv = my_csv.model()
result = mycsv.write(file_path, list_title, list_title_en,
result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def export_data(self):
params = self.req.dict_req
params['limit'] = -1
params['page'] = 1
if 'time_start' not in params['whereJson'] or 'time_end' not in params['whereJson']:
dict_res = {'code': 500, 'msg': 'whereJson错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
if params['whereJson']['time_start'] == '' or params['whereJson']['time_end'] == '':
dict_res = {'code': 500, 'msg': 'whereJson错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
str_gte = params['whereJson']['time_start']
str_lt = params['whereJson']['time_end']
whereStr = 'log_time >= "'+str_gte+'"' + ' and log_time < "'+str_lt+'"'
if 'ip' in params['whereJson']:
whereStr += ' and server_ip = "'+params['whereJson']['ip']+'"'
union_sql = self.mysqldb.get_union_sql('dns_req_list_latest', str_gte, str_lt, whereStr)
sql = 'select * from '+union_sql+' order by log_time asc'
result = self.mysqldb.find_data(self.table_name, params, sql)
# print(result)
if result:
file_path = os.path.dirname(os.path.dirname(__file__)) + '/static/uploadFile/'+ self.table_name +'_export.csv'
list_title = ['设备IP', '域名', '客户端IP', '类型', '时间']
list_title_en = ['server_ip','client_host','client_ip','type_name','log_time']
mycsv = my_csv.model()
result = mycsv.write(file_path,list_title,list_title_en,result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def import_data(self):
# dict_req = self.req.dict_req
file = self.req.files.get('file')
if not file:
dict_res = {'code': 200, 'msg': 'file不能为空'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
file_path = os.path.dirname(
os.path.dirname(__file__)
) + '/static/uploadFile/' + self.table_name + '_import.csv'
file.save(file_path)
mycsv = my_csv.model()
list_title_en = [
'code', 'name', 'ip', 'level', 'factory', 'device_type', 'os',
'os_version', 'cpu', 'ram', 'disk', 'power', 'address', 'message',
'online_time'
]
result = mycsv.read(file_path, list_title_en)
if result['msg']:
dict_res = {'code': 500, 'msg': result['msg']}
return make_response(json.dumps(dict_res, ensure_ascii=False))
list_data = result['rows']
list_ip = []
for item in list_data:
item['create_name'] = self.req.dict_tocken['username']
item['create_time'] = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
item['update_name'] = ''
item['update_time'] = ''
if 'ip' not in item or item['ip'] == '':
dict_res = {'code': 500, 'msg': 'ip错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
list_ip.append(item['ip'])
params = {
'whereJson': {
'ip': {
'$in': list_ip
}
},
'fieldJson': {
'id': 1,
'ip': 1
}
}
result = self.mysqldb.find_data(self.table_name, params)
if result['count'] != 0:
dict_res = {'code': 200, 'msg': 'ip已存在'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
result = self.mysqldb.insert_data(self.table_name, list_data)
# print(result)
if result:
dict_res = {'code': 200, 'msg': '操作成功'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def export_data(self):
params = self.req.dict_req
params['limit'] = int(params['limit']) if 'limit' in params else 10
params['page'] = int(params['page']) if 'page' in params else 1
params['offset'] = params['limit'] * (int(params['page']) - 1) if 'page' in params else 0
params['sortJson'] = params['sortJson'] if 'sortJson' in params else {}
if 'whereJson' not in params:
dict_res = {'code': 500, 'msg': 'miss whereJson'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
if 'type' not in params['whereJson']:
dict_res = {'code': 500, 'msg': 'whereJson错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
sortStr = ' order by num desc'
whereStr = ''
whereStr = ''
whereStr_err = 'req_status > 0'
str_gte = params['whereJson']['log_time']['$gte']
str_lt = params['whereJson']['log_time']['$lt']
if params['whereJson']['type'] == 'host':
del params['whereJson']['type']
if len(params['whereJson']) > 0:
#index索引排序
params['whereJson'] = {x:params['whereJson'][x] for x in ['log_time', 'server_ip', 'client_host'] if x in params['whereJson']}
whereStr = self.mysqldb.get_whereStr(params)
whereStr_err = whereStr_err +' and '+whereStr
union_sql = self.mysqldb.get_union_sql('dns_req_list_latest', str_gte, str_lt, whereStr)
union_sql_err = self.mysqldb.get_union_sql('dns_req_list_latest', str_gte, str_lt, whereStr_err)
sql = 'select t1.client_host, t1.num, t2.num_err from (select client_host, count(*) as num from '+union_sql+' group by client_host) t1 left join (select client_host, count(*) as num_err from '+union_sql_err+' group by client_host) t2 on t1.client_host=t2.client_host'+sortStr+' limit '+str(params['offset'])+','+str(params['limit'])
list_title = ['域名', 'NDS查询次数', '错误次数', '错误率']
list_title_en = ['client_host', 'num', 'num_err', 'err_rate']
else:
del params['whereJson']['type']
if len(params['whereJson']) > 0:
#index索引排序
params['whereJson'] = {x:params['whereJson'][x] for x in ['log_time', 'server_ip', 'client_ip'] if x in params['whereJson']}
whereStr = self.mysqldb.get_whereStr(params)
whereStr_err = whereStr_err +' and '+whereStr
union_sql = self.mysqldb.get_union_sql('dns_req_list_latest', str_gte, str_lt, whereStr)
union_sql_err = self.mysqldb.get_union_sql('dns_req_list_latest', str_gte, str_lt, whereStr_err)
sql = 'select t3.client_ip, t3.num_host, t3.num, t2.num_err from (select t1.client_ip, count(*) as num_host, cast(sum(t1.num_host) as signed) as num from (select client_ip, client_host, count(*) as num_host from '+union_sql+' group by client_ip, client_host) t1 group by t1.client_ip) t3 left join (select client_ip, count(*) as num_err from '+union_sql_err+' group by client_ip) t2 on t3.client_ip=t2.client_ip'+sortStr+' limit '+str(params['offset'])+','+str(params['limit'])
list_title = ['客户端IP', 'DNS查询次数', '请求域名次数', '错误次数', '错误率']
list_title_en = ['client_ip', 'num', 'num_host', 'num_err', 'err_rate']
result = self.mysqldb.find_data(self.table_name, params, sql)
# print(result)
if result:
for item in result['rows']:
item['num_err'] = item['num_err'] if item['num_err'] else 0
item['err_rate'] = str(int(item['num_err']/item['num']))+'%'
file_path = os.path.dirname(os.path.dirname(__file__)) + '/static/uploadFile/'+ self.table_name +'_export.csv'
# list_title = []
# list_title_en = []
mycsv = my_csv.model()
result = mycsv.write(file_path,list_title,list_title_en,result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
def export_data(self):
params = self.req.dict_req
params['limit'] = int(params['limit']) if 'limit' in params else 10
params['page'] = int(params['page']) if 'page' in params else 1
params['offset'] = params['limit'] * (int(params['page']) -
1) if 'page' in params else 0
params['sortJson'] = params['sortJson'] if 'sortJson' in params else {}
if 'whereJson' not in params:
dict_res = {'code': 500, 'msg': 'miss whereJson'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
if 'type' not in params['whereJson']:
dict_res = {'code': 500, 'msg': 'whereJson错误'}
return make_response(json.dumps(dict_res, ensure_ascii=False))
sortStr = ' order by num desc'
whereStr = ''
if params['whereJson']['type'] == 'host':
del params['whereJson']['type']
if len(params['whereJson']) > 0:
whereStr = ' where ' + self.mysqldb.get_whereStr(params)
sql = 'select t1.client_host, t1.num, t2.num_err from (select client_host, count(*) as num from dns_req_list' + whereStr + ' group by client_host) t1 left join (select client_host, count(*) as num_err from dns_err_list where req_status = "SERVFAIL" group by client_host) t2 on t1.client_host=t2.client_host' + sortStr + ' limit ' + str(
params['offset']) + ',' + str(params['limit'])
sql_count = 'select count(*) as number from (select count(*) as number from dns_req_list' + whereStr + ' group by client_host) gyd'
list_title = ['域名', 'NDS查询次数', '错误次数', '错误率']
list_title_en = ['client_host', 'num', 'num_err', 'err_rate']
else:
del params['whereJson']['type']
if len(params['whereJson']) > 0:
whereStr = ' where ' + self.mysqldb.get_whereStr(params)
sql = 'select t3.client_ip, t3.num_host, t3.num, t2.num_err from (select t1.client_ip, count(*) as num_host, cast(sum(t1.num_host) as signed) as num from (select client_ip, client_host, count(*) as num_host from dns_req_list' + whereStr + ' group by client_ip, client_host) t1 group by t1.client_ip) t3 left join (select client_ip, count(*) as num_err from dns_err_list where req_status = "SERVFAIL" group by client_ip) t2 on t3.client_ip=t2.client_ip' + sortStr + ' limit ' + str(
params['offset']) + ',' + str(params['limit'])
sql_count = 'select count(*) as number from (select t.client_ip, count(*) as num_host, sum(t.num_host) as num from (select client_ip, client_host, count(*) as num_host from dns_req_list' + whereStr + ' group by client_ip, client_host) t group by t.client_ip) gyd'
list_title = ['客户端IP', '解析次数', '请求域名次数', '错误次数', '错误率']
list_title_en = [
'client_ip', 'num', 'num_host', 'num_err', 'err_rate'
]
result = self.mysqldb.find_data(self.table_name, params, sql,
sql_count)
# print(result)
if result:
for item in result['rows']:
item['num_err'] = item['num_err'] if item['num_err'] else 0
item['err_rate'] = str(int(
item['num_err'] / item['num'])) + '%'
if result['count'] > 100:
result['count'] = 100
result['rows'] = result['rows'][:100]
file_path = os.path.dirname(
os.path.dirname(__file__)
) + '/static/uploadFile/' + self.table_name + '_export.csv'
# list_title = []
# list_title_en = []
mycsv = my_csv.model()
result = mycsv.write(file_path, list_title, list_title_en,
result['rows'])
str_url = result['url']
str_url = '/uploadFile' + str_url[str_url.rindex('/'):]
dict_res = {'code': 200, 'msg': '操作成功', 'url': str_url}
return make_response(json.dumps(dict_res, ensure_ascii=False))
else:
dict_res = {'code': 500, 'msg': '操作失败'}
return make_response(json.dumps(dict_res, ensure_ascii=False))