def monitor_host_create(req):
values = []
for key in ['ip', 'mem', 'cpu', 'disk', 'm_time']:
values.append(req.get(key, ''))
values.append(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
dbutils.execute_sql(SQL_MONITOR_HOST_CREATE, values, False)
return True
def accesslog_ip_dist():
legend = []
geoCoord = {}
markline_data = []
markpoint_data = []
server_ip = '47.153.191.255'
geo_reader = geoip2.database.Reader(gconf.GeoLite2)
server_response = geo_reader.city(server_ip)
geo_reader.close()
server_city_name = server_response.city.names.get('zh-CN', u'北京')
legend.append(server_city_name)
geoCoord[server_city_name] = [
server_response.location.longitude, server_response.location.latitude
]
_, rt_list = dbutils.execute_sql(SQL_GEOIP_LIST, (), True)
for line in rt_list:
geoCoord[line[0]] = [line[2], line[1]]
_, rt_list = dbutils.execute_sql(SQL_ACCESS_IP_DIST, (), True)
for line in rt_list:
markline_data.append([{
'name': line[0]
}, {
'name': server_city_name,
'value': line[1]
}])
markpoint_data.append({'name': line[0], 'value': line[1]})
return legend, geoCoord, markline_data, markpoint_data
def save(self):
cnt, rt = dbutils.execute_sql(
self.SQL_SAVE,
(self.username, self.age, crypt.md5_str(self.password),
self.department, self.sex, self.birthday, self.email, self.hobby),
is_fetch=False)
return cnt != 0
def accesslog_code_time_dist():
legend = []
xAxis = []
data = []
temp_data = {}
_, rt_list = dbutils.execute_sql(SQL_ACCESSLOG_CODE_TIME_DIST, (), True)
for line in rt_list:
if line[0] not in xAxis:
xAxis.append(line[0]) # time
if line[1] not in legend: # code
legend.append(line[1])
#line[2] # count
temp_data.setdefault(line[1], {})
temp_data[line[1]][line[0]] = line[2]
xAxis.sort()
for code in temp_data:
code_time_data = []
for time in xAxis:
code_time_data.append(temp_data[code].get(time, 0))
data.append({
'name': code,
'type': 'bar',
'stack': 'code',
'data': code_time_data
})
return legend, xAxis, data
def monitor_host_list(ip):
start_time = (datetime.datetime.now() -
datetime.timedelta(hours=1)).strftime('%Y-%m-%d %H:%M:%S')
rt_cnt, rt_list = dbutils.execute_sql(SQL_MONITOR_HOST_LIST,
(ip, start_time), True)
categoy_list, cpu_list, disk_list, mem_list = [], [], [], []
for line in rt_list:
categoy_list.append(line[0].strftime('%H:%M'))
cpu_list.append(line[1])
mem_list.append(line[2])
disk_list.append(line[3])
return {
'categories':
categoy_list,
'series': [{
'name': 'cpu',
'data': cpu_list
}, {
'name': '内存',
'data': mem_list
}, {
'name': '磁盘',
'data': disk_list
}]
}
def get_list(cls):
users_list = []
rt_cnt, rt_list = dbutils.execute_sql(cls.SQL_LIST, (), is_fetch=True)
for line in rt_list:
user_list = dict(zip(cls.SQL_LIST_COLUMNS, line))
user_list['birthday'] = user_list['birthday'].strftime('%Y-%m-%d')
users_list.append(user_list)
return users_list
def validate_assets_update(uid, hostname):
if hostname.strip() == '':
return False, 'hostname is empty'
sql = SQL_VALIDATE_ASSETS_MODIFY
args = (uid, hostname.strip())
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=True)
if cnt != 0:
return False, 'name is same to other'
return True, ''
def add_assets_update(uid, sn, hostname, os, ip, machine_room_id, vendor,
model, ram, cpu, disk, time_on_shelves,
over_guaranteed_date, buiness, admin, status):
sql = SQL_ASSETS_MODIFY
args = (sn, hostname, os, ip, machine_room_id, vendor, model, ram, cpu,
disk, time_on_shelves, over_guaranteed_date, buiness, admin,
status, uid)
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=False)
return True
def get_asset_by_ip(ip):
rt_cnt, rt_list = dbutils.execute_sql(SQL_ASSET_BY_IP, (ip, ), True)
assets = []
for rt in rt_list:
asset = dict(zip(SQL_ASSET_LIST_COLUMNS, rt))
for key in 'time_on_shelves,over_guaranteed_date'.split(','):
if asset[key]:
asset[key] = asset[key].strftime('%Y-%m-%d')
assets.append(asset)
return assets[0] if assets else {}
def get_alert_by_id(uid):
rt_cnt, rt_list = dbutils.execute_sql(SQL_ALERT_BY_ID, (uid, ), True)
alerts = []
for rt in rt_list:
alert = dict(zip(SQL_ALERT_LIST_COLUMNS, rt))
for key in 'c_time'.split(','):
if alert[key]:
alert[key] = alert[key].strftime('%Y-%m-%d')
alerts.append(alert)
return alerts[0] if alerts else {}
def get_machine_by_id(uid):
args = (uid, )
machines_list = []
rc_cnt, rt_list = dbutils.execute_sql(SQL_MACHINE_BY_ID,
args,
is_fetch=True)
for rt in rt_list:
machine_list = dict(zip(SQL_MACHINE_BY_ID_COLUMNS, rt))
machines_list.append(machine_list)
return machines_list[0] if machines_list else {}
def get_alerts():
rc_cnt, rt_list = dbutils.execute_sql(SQL_ALERT_LIST, (), True)
alerts = []
for rt in rt_list:
alert = dict(zip(SQL_ALERT_LIST_COLUMNS, rt))
for key in 'c_time'.split(','):
if alert[key]:
alert[key] = alert[key].strftime('%Y-%m-%d')
alerts.append(alert)
return alerts
def accesslog_code_dist():
_, rt_list = dbutils.execute_sql(SQL_ACCESSLOG_CODE_DIST, (), True)
legend = []
data = []
for line in rt_list:
legend.append(line[0])
data.append({'value': line[1], 'name': line[0]})
return legend, data
def get_assets():
rc_cnt, rt_list = dbutils.execute_sql(SQL_ASSET_LIST, (), True)
assets = []
rooms = get_machine_rooms_index_by_id()
for rt in rt_list:
asset = dict(zip(SQL_ASSET_LIST_COLUMNS, rt))
for key in 'time_on_shelves,over_guaranteed_date'.split(','):
if asset[key]:
asset[key] = asset[key].strftime('%Y-%m-%d')
asset['machine_room_name'] = rooms.get(asset['machine_room_id'],
{}).get('name', '')
assets.append(asset)
return assets
def add_assets(sn, hostname, os, ip, machine_room_id, vendor, model, ram, cpu,
disk, time_on_shelves, over_guaranteed_date, buiness, admin,
status):
sql = SQL_ASSETS_SAVE
args = (sn, hostname, os, ip, machine_room_id, vendor, model, ram, cpu,
disk, time_on_shelves, over_guaranteed_date, buiness, admin,
status)
rc_cnt, rt_list = dbutils.execute_sql(SQL_ASSETS_SAVE, args, False)
assets = []
for rt in rt_list:
asset = dict(zip(SQL_ASSET_LIST_COLUMNS, rt))
for key in 'time_on_shelves,over_guaranteed_date'.split(','):
if asset[key]:
asset[key] = asset[key].strftime('%Y-%m-%d')
assets.append(asset)
return rc_cnt != 0
def validate_machine_modify(uid, name, addr, ip_ranges):
if not get_machine_by_id(uid):
return False, 'machine is not found'
if name.strip() == '':
return False, 'machine name is empty'
if len(name.strip()) > 25:
return False, 'name len is not gt 25'
if addr.strip() == '':
return False, 'addr is empty'
if len(addr.strip()) > 30:
return False, 'addr len is not gt 30'
if len(ip_ranges.strip()) > 100:
return False, 'addr len is not gt 100'
sql = SQL_VALIDATE_MACHINE_MODIFY
args = (uid, name.strip())
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=True)
if cnt != 0:
return False, 'name is same to other'
return True, ''
def nginxlog(n=10):
_, rt_list = dbutils.execute_sql(SQL_ACCESS_LIST, (n, ), True)
return rt_list
def machine_modify(uid, name, addr, ip_ranges):
sql = SQL_MACHINE_MODIFY
args = (name, addr, ip_ranges, uid)
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=False)
return True
def machine_delete(uid):
sql = SQL_MACHINE_DELETE
args = (uid, )
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=False)
return True
def delete_by_key(cls, value, key=None):
sql = cls.SQL_DELETE_BY_KEY.format(key=cls.KEY if key is None else key)
dbutils.execute_sql(sql, (value, ), False)
return True
def password_modify(self):
dbutils.execute_sql(self.SQL_PASSWORD_MODIFY,
(crypt.md5_str(self.password), self.id), False)
return True
def modify(self):
args = (self.username, self.age, self.department, self.sex,
self.birthday, self.email, self.hobby, self.id)
dbutils.execute_sql(self.SQL_MODIFY, args, is_fetch=False)
return True
def get_by_key(cls, value, key=None):
sql = cls.SQL_GET_BY_KEY.format(key=cls.KEY if key is None else key)
cnt, rt_list = dbutils.execute_sql(sql, (value, ), True)
return dict(zip(cls.SQL_GET_BY_KEY_COLUMNS,
rt_list[0])) if rt_list else None
def validate_password(cls, uid, password):
rt_cnt, rt = dbutils.execute_sql(cls.SQL_VALIDATE_PASSWORD,
(uid, crypt.md5_str(password)),
is_fetch=True)
return rt_cnt > 0
def login(cls, username, password):
rt_cnt, rt = dbutils.execute_sql(cls.SQL_LOGIN,
(username, crypt.md5_str(password)),
is_fetch=True)
return None if len(rt) == 0 else dict(zip(cls.SQL_LOGIN_COLUMNS,
rt[0]))
def get_machines():
sql = SQL_MACHINE_LIST
args = ()
rt_cnt, rt_list = dbutils.execute_sql(sql, args, is_fetch=True)
return [dict(zip(SQL_MACHINE_LIST_COLUMNS, line)) for line in rt_list]
def password_modify(uid, password):
dbutils.execute_sql(SQL_USER_PASSWORD_MODIFY,
(crypt.md5_str(password.strip()), uid), False)
return True
def add_machines(name, addr, ip_ranges):
sql = SQL_MACHINE_SAVE
args = (name, addr, ip_ranges)
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=False)
return cnt != 0
def alerts_delete(uid):
sql = SQL_ALERT_DELETE
args = (uid, )
cnt, rt = dbutils.execute_sql(sql, args, is_fetch=False)
return True
