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