def oracle_switchover(p_host, p_user, p_password, s_host, s_user, s_password): log_type = 'Oracle容灾切换' tools.mysql_exec("delete from many_logs where log_type = 'Oracle容灾切换'", '') # 上传脚本 local_file = os.getcwd( ) + '/frame/oracle_tools/switchover/switchOverForPrimary.sh' sftp_upload_file(p_host, p_user, p_password, '/tmp/switchOverForPrimary.sh', local_file) # 执行命令 主转备 cmd = 'sh /tmp/switchOverForPrimary.sh > /tmp/switchOverForPrimary.log' exec_command(p_host, p_user, p_password, cmd) tools.my_log(log_type, 'ssh to %s:执行switchOverForPrimary.sh,主库切换为备库成功!' % p_host, '') # 上传脚本 local_file = os.getcwd( ) + '/frame/oracle_tools/switchover/startupPrimary.sh' sftp_upload_file(p_host, p_user, p_password, '/tmp/startupPrimary.sh', local_file) # 执行命令 开启主库 cmd = 'sh /tmp/startupPrimary.sh > /tmp/startupPrimary.log' exec_command(p_host, p_user, p_password, cmd) tools.my_log(log_type, 'ssh to %s:执行startupPrimary.sh,主库启动成功!' % p_host, '') # 上传脚本 local_file = os.getcwd( ) + '/frame/oracle_tools/switchover/switchOverForStandby.sh' sftp_upload_file(s_host, s_user, s_password, '/tmp/switchOverForStandby.sh', local_file) # 执行命令 备转主 cmd = 'sh /tmp/switchOverForStandby.sh > /tmp/switchOverForStandby.log' print cmd exec_command(s_host, s_user, s_password, cmd) tools.my_log(log_type, 'ssh to %s:执行switchOverForStandby.sh,备库切换为主库成功!' % s_host, '')
def oracle_install(host, user, password): log_type = 'Oracle部署' tools.mysql_exec("delete from many_logs where log_type = 'Oracle部署'", '') # 清除目标目录 cmd = 'rm -rf /tmp/oracle_install' exec_command(host, user, password, cmd) # 创建文件目录 cmd = 'mkdir -p /tmp/oracle_install/oracle_rpms/centos6' exec_command(host, user, password, cmd) # 上传安装部署文件 sftp_upload_dir(host, user, password, '/tmp', 'oracle_install') tools.my_log(log_type, '预上传文件完成!', '') #1. 安装rpm包 cmd = 'sh /tmp/oracle_install/1_ora_yum.sh > /tmp/oracle_install/1_ora_yum.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行1_ora_yum.sh,rpm包安装完成!', '') #2.环境初始化,建组、用户,配置资源限制,内核参数 cmd = 'sh /tmp/oracle_install/2_ora_init.sh > /tmp/oracle_install/2_ora_init.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行2_ora_init.sh,环境初始化完成!', '') #3. 创建目录 cmd = 'sh /tmp/oracle_install/3_fd_init.sh > /tmp/oracle_install/3_fd_init.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行3_fd_init.sh,目录创建完成!', '') #4. 配置Oracle用户环境变量 cmd = 'sh /tmp/oracle_install/4_ora_profile.sh > /tmp/oracle_install/4_ora_profile.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行4_ora_profile.sh,Oracle用户环境变量配置完成!', '') #5. 上传安装包和安装文件 cmd = 'sh /tmp/oracle_install/5_init_orahome.sh > /tmp/oracle_install/5_init_orahome.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行5_init_orahome.sh,Oracle安装文件传输完成!', '')
def mysql_startup(host, user, password): log_type = '启动Mysql数据库' tools.mysql_exec("delete from many_logs where log_type = '启动Mysql数据库'", '') # 上传脚本 local_file = os.getcwd() + '/frame/mysql_tools/mysql_startup.sh' sftp_upload_file(host, user, password, '/tmp/mysql_startup.sh', local_file) # 执行命令 cmd = 'sh /tmp/mysql_startup.sh > /tmp/mysql_startup.log' exec_command(host, user, password, cmd) tools.my_log(log_type, 'mysql_startup.sh,Mysql数据库启动成功!', '')
def mysql_shutdown(host, user, password, ssh_port): log_type = '关闭Mysql数据库' tools.mysql_exec("delete from many_logs where log_type = '关闭Mysql数据库'", '') # 上传脚本 local_file = os.getcwd() + '/frame/mysql_tools/mysql_shutdown.sh' sftp_upload_file(host, user, password, ssh_port, '/tmp/mysql_shutdown.sh', local_file) # 执行命令 cmd = 'sh /tmp/mysql_shutdown.sh > /tmp/mysql_shutdown.log' exec_command(host, user, password, ssh_port, cmd) tools.my_log(log_type, '执行mysql_shutdown.sh,Mysql数据库关闭成功!', '')
def save_oracle_alert_log(tags, host, log_meta): for key in KeyWordList: if log_meta: save = False if key in log_meta['log_content']: save = True if save: sql = "insert into oracle_alert_log(tags,host,log_time,log_level,log_content) values(%s,%s,%s,%s,%s)" values = (tags, host, log_meta['log_time'], log_meta['log_level'], log_meta['log_content']) tools.mysql_exec(sql, values) log_meta = []
def oracle_startup(host, user, password): log_type = '启动Oracle数据库' tools.mysql_exec("delete from many_logs where log_type = '启动Oracle数据库'", '') # 上传脚本 local_file = os.getcwd() + '/frame/oracle_tools/ora_startup.sh' print local_file sftp_upload_file(host, user, password, '/tmp/ora_startup.sh', local_file) # 执行命令 cmd = 'sh /tmp/ora_startup.sh > /tmp/ora_startup.log' exec_command(host, user, password, cmd) tools.my_log(log_type, 'ora_startup.sh,Oracle数据库启动成功!', '')
def get_report(tags, url, user, password, report_type, begin_snap, end_snap): # 获取报告相关信息 sql = "select instance_number from v$instance" res = tools.oracle_query(url, user, password, sql) instance_num = res[0][0] sql = "select dbid from v$database" res = tools.oracle_query(url, user, password, sql) dbid = res[0][0] sql = "select instance_name from v$instance" res = tools.oracle_query(url, user, password, sql) instance_name = res[0][0] if report_type == 'ash': report_begin_time = begin_snap report_end_time = end_snap else: sql = "select to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot a where a.snap_id=%s" % begin_snap res = tools.oracle_query(url, user, password, sql) report_begin_time = res[0][0] sql = "select to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot a where a.snap_id=%s" % end_snap res = tools.oracle_query(url, user, password, sql) report_end_time = res[0][0] # 生成报告 if report_type == 'awr': data = get_awr(url, user, password, dbid, instance_num, begin_snap, end_snap) elif report_type == 'addm': data = get_addm(url, user, password, instance_name, dbid, instance_num, begin_snap, end_snap) else: data = get_ash(url, user, password, dbid, instance_num, begin_snap, end_snap) if report_type == 'addm': suffix = 'txt' else: suffix = 'html' if report_type == 'ash': begin_time = datetime.strptime( begin_snap, '%Y-%m-%dT%H:%M').strftime("%Y%m%d-%H%M") end_time = datetime.strptime(end_snap, '%Y-%m-%dT%H:%M').strftime("%Y%m%d-%H%M") report_path = 'oracle_report/%s_%s_%s_%s_%s.%s' % ( report_type, dbid, instance_num, begin_time, end_time, suffix) else: report_path = 'oracle_report/%s_%s_%s_%s_%s.%s' % ( report_type, dbid, instance_num, begin_snap, end_snap, suffix) local_path = os.getcwd() + '/templates/' + report_path save_report(local_path, data) insert_sql = " INSERT INTO oracle_report(tags,report_begin_time,report_end_time,report_type,report_path,status) values('%s','%s','%s','%s','%s','已生成') " % ( tags, report_begin_time, report_end_time, report_type, report_path) tools.mysql_exec(insert_sql, '')
def go_collect(local_dir): log_type = '日志采集' tools.mysql_exec("delete from many_logs where log_type = '日志采集'", '') # 指定本地保存路径 today = time.strftime('%Y%m%d') now = time.strftime('%H%M%S') # 创建当天目录 local_today = local_dir + '/' + today isExists = os.path.exists(local_today) # cmd_find = "find %s -mtime 0 -name '*'" %log_path if not isExists: os.mkdir(local_today) tools.my_log(log_type, '成功创建本地目录:%s' % local_today, '') logs = tools.mysql_query( "select app_name, host,user,password,log_name,log_path from log_collect_conf order by id" ) print logs for log in logs: app_name = log[0] host = log[1] user = log[2] password = base64.decodestring(log[3]) log_name = log[4] log_path = log[5] # 创建程序目录 local_log_path = local_today + '/' + log_name isExists = os.path.exists(local_log_path) # cmd_find = "find %s -mtime 0 -name '*.trc'" %log_path if not isExists: os.makedirs(local_log_path) tools.my_log(log_type, '成功创建本地日志目录:%s' % local_log_path, '') # 将远端日志路径传入app_path_list,进行遍历抓取 log_list = [] log_list.append(log_path) for log_path in log_list: tools.my_log(log_type, '开始收集日志:%s->%s->%s' % (log_name, host, log_path), '') cmd_find = "find %s -newermt %s -type f -name '*' ! -name '.*'" % ( log_path, today) lt = sftp_exec_command(host, user, password, cmd_find) for each in lt: # 取文件名 filename = each.split('/')[-1] local_app_log = local_log_path + '/' + filename print each print local_app_log sftp_down_file(host, user, password, each, local_app_log) # alert_path_final=alert_path.decode('unicode-escape').encode('utf-8') tools.my_log(log_type, '成功获取日志:%s' % each, '')
def mysql_install(host, user, password, data_path, mysql_base, port): tools.mysql_exec("delete from many_logs where log_type = 'MySQL部署'", '') extra_params = '' mysql_home = '/%s/my%s' % (mysql_base, port) # 1. 创建目录 tools.my_log(log_type, '开始创建目录!', '') create_home_dir(host, user, password, port, data_path, mysql_base) # 2. 生成配置文件 tools.my_log(log_type, '生成配置文件!', '') generate_conf(host, user, password, port, data_path, mysql_base, mysql_home, extra_params) # 3. 初始化数据库 install_default_database(host, user, password, data_path, mysql_base, port, mysql_home)
def get_mysql_alert(conn, tags, host, user, password): # 取后台日志路径 alert_log = check_mysql.get_mysql_para(conn, 'log_error') # 建立ssh连接,抓取后台日志 ssh_client = paramiko.SSHClient() ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh_client.connect(host, 22, user, password) command = 'tail -300 %s' % alert_log std_in, std_out, std_err = ssh_client.exec_command(command) fd = std_out log = fd.read() # 清空原日志数据 sql = "delete from alert_log where tags='%s' and server_type='MySQL' " % tags tools.mysql_exec(sql, '') parse_mysql_alert_logs(tags, host, oracle_alert_stream(log))
def save_mysql_alert_log(tags,host,log_meta): for key in MysqlKeyWordList: if log_meta: save = False if key in log_meta['log_content']: save = True if save: if log_meta.has_key('log_time'): log_time = log_meta['log_time'] else: log_time = '' sql = "insert into alert_log(tags,host,server_type,log_time,log_level,log_content) values(%s,%s,%s,%s,%s,%s)" values = (tags, host,'MySQL',log_time, log_meta['log_level'], log_meta['log_content']) tools.mysql_exec(sql, values) log_meta = []
def mysql_install(host, user, password): log_type = 'Mysql部署' tools.mysql_exec("delete from many_logs where log_type = 'Mysql部署'", '') #3. 配置MySQL用户环境变量 cmd = 'sh /tmp/mysql_install/3_mysql_profile.sh > /tmp/mysql_install/3_mysql_profile.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行3_mysql_profile.sh,MySQL用户环境变量配置完成!', '') #4. 创建用户组和目录 cmd = 'sh /tmp/mysql_install/4_fd_init.sh > /tmp/mysql_install/4_fd_init.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行4_fd_init.sh,目录创建完成!', '') #5.解压、安装 cmd = 'sh /tmp/mysql_install/5_mysql_install.sh > /tmp/mysql_install/5_mysql_install.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行5_mysql_install.sh,mysql安装完成!', '')
def is_send_email(alarm_name,tags,url,email_header,alarm_content): receiver = conf.get("email", "receiver").split(',') is_send = conf.get("email","is_send") next_time_to_send_email = float(conf.get("policy", "next_send_email_time")) is_alarm_in_min_sql = "select count(*) from tab_alarm_email_info where tags = '%s' and url='%s' and alarm_type='%s' and email_header = '%s' and timestampdiff(minute,alarm_time,current_timestamp())<%s" % ( tags,url, alarm_name, email_header,next_time_to_send_email) # print is_alarm_in_15min_sql is_alarm_in_15min = tools.mysql_query(is_alarm_in_min_sql) cnt = is_alarm_in_15min[0][0] if cnt == 0 and float(is_send) <> 0: try: mail.send_email(receiver, email_header, alarm_content) my_log.logger.info('成功发送告警邮件: %s 到%s' % (email_header, receiver)) email_sql = 'insert into tab_alarm_email_info(tags,url,email_header,email_content,alarm_type) value(%s,%s,%s,%s,%s)' value = (tags,url, email_header, alarm_content, alarm_name) tools.mysql_exec(email_sql, value) except Exception, e: error_msg = "To %s 发送邮件失败:%s" % (receiver, str(e)) my_log.logger.error(error_msg)
def mysql_install(host,user,password): log_type = 'Mysql部署' tools.mysql_exec("delete from many_logs where log_type = 'Mysql部署'", '') # 清除目标目录 cmd = 'rm -rf /tmp/mysql_install' exec_command(host, user, password, cmd) # 创建文件目录 cmd = 'mkdir -p /tmp/mysql_install' exec_command(host, user, password, cmd) # 上传安装部署文件 sftp_upload_dir(host,user,password,'/tmp','mysql_install') tools.my_log(log_type, '预上传文件完成!','') # 1. 移除自带MySQL用户及安装包 cmd = 'sh /tmp/mysql_install/0_mysql_delold.sh > /tmp/mysql_install/0_mysql_delold.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行0_mysql_delold.sh,移除自带mysql完成!', '') # 1. 安装rpm包 cmd = 'sh /tmp/mysql_install/1_mysql_yum.sh > /tmp/mysql_install/1_mysql_yum.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行1_mysql_yum.sh,rpm包安装完成!', '') #2. 配置资源限制,内核参数 cmd = 'sh /tmp/mysql_install/2_mysql_init.sh > /tmp/mysql_install/2_mysql_init.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行2_mysql_init.sh,环境初始化完成!','') #3. 配置MySQL用户环境变量 cmd = 'sh /tmp/mysql_install/3_mysql_profile.sh > /tmp/mysql_install/3_mysql_profile.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行3_mysql_profile.sh,MySQL用户环境变量配置完成!', '') #4. 创建用户组和目录 cmd = 'sh /tmp/mysql_install/4_fd_init.sh > /tmp/mysql_install/4_fd_init.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行4_fd_init.sh,目录创建完成!', '') #5.解压、安装 cmd = 'sh /tmp/mysql_install/5_mysql_install.sh > /tmp/mysql_install/5_mysql_install.log' exec_command(host, user, password, cmd) tools.my_log(log_type, '执行5_mysql_install.sh,mysql安装完成!', '')
def get_oracle_alert(conn, tags, host, user, password): # 取后台日志路径 cur = conn.cursor() diag_trace_sql = "select value from v$diag_info where name = 'Diag Trace'" cur.execute(diag_trace_sql) diag_trace = cur.fetchall() diag_trace_dir = diag_trace[0][0] # 建立ssh连接,抓取后台日志 ssh_client = paramiko.SSHClient() ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh_client.connect(host, 22, user, password) command = 'tail -30000 %s/alert_*.log' % diag_trace_dir std_in, std_out, std_err = ssh_client.exec_command(command) fd = std_out log = fd.read() # 清空原日志数据 sql = "delete from oracle_alert_log" tools.mysql_exec(sql, '') parse_oracle_alert_logs(tags, host, oracle_alert_stream(log))
def oracle_install(host,user,password): log_type = 'Oracle部署' tools.mysql_exec("delete from many_logs where log_type = 'Oracle部署'",'') # 清除目标目录 cmd = 'rm -rf /tmp/oracle_install' exec_command(host, user, password, cmd) # 创建文件目录 cmd = 'mkdir -p /tmp/oracle_install/oracle_rpms/centos6' exec_command(host, user, password, cmd) # 上传安装部署文件 sftp_upload_dir(host,user,password,'/tmp','oracle_install') #1. 安装rpm包 cmd = 'sh /tmp/oracle_install/1_ora_yum.sh > /tmp/oracle_install/1_ora_yum.log' exec_command(host, user, password, cmd) #2.环境初始化,建组、用户,配置资源限制,内核参数 cmd = 'sh /tmp/oracle_install/2_ora_init.sh > /tmp/oracle_install/2_ora_init.log' exec_command(host, user, password, cmd) #3. 创建目录 cmd = 'sh /tmp/oracle_install/3_fd_init.sh > /tmp/oracle_install/3_fd_init.log' exec_command(host, user, password, cmd)
def oracle_exec_sql(): log_type = 'Oracle执行sql脚本' local_dir = os.getcwd() cmd = 'ls %s/frame/sqlscripts/*.sql' % local_dir status, result = commands.getstatusoutput(cmd) if status == 0: sql_list = result.split('\n') for sql in sql_list: print '开始执行脚本:%s' % sql sql_name = sql.split('/')[-1] sql_no = sql_name.split('_')[0] user = '******' password = '******' tnsname = sql.split('_')[1] cmd = 'sqlplus -s %s/%s@%s @%s' % (user, password, tnsname, sql) status, result = commands.getstatusoutput(cmd) if status == 0 and not 'ORA-' in result: print result print '%s执行成功!' % sql tools.my_log(log_type, '%s执行成功!' % sql, '') tools.my_log(log_type, '执行信息:%s' % result, '') cmd = 'rm %s' % sql status, result = commands.getstatusoutput(cmd) print '%s脚本已删除!' % sql tools.my_log(log_type, '%s脚本已删除!' % sql, '') sql = "update sql_list set result = '成功',result_color = 'green' where sql_no=%s" % sql_no tools.mysql_exec(sql, '') else: print result print '%s执行失败,已回滚!' % sql tools.my_log(log_type, '%s执行失败,已回滚!' % sql, '') tools.my_log(log_type, '', '异常信息:%s' % result) cmd = 'rm %s' % sql status, result = commands.getstatusoutput(cmd) print '%s脚本已删除!' % sql tools.my_log(log_type, '%s脚本已删除!' % sql, '') sql = "update sql_list set result = '失败',result_color = 'red' where sql_no=%s" % sql_no tools.mysql_exec(sql, '')
def save_data(host, port, tags, slow_log_file, SQL_META): if SQL_META: start_time = SQL_META['start_time'] host_client = SQL_META['host'] db_name = '' if 'db_name' in SQL_META: db_name = SQL_META['db_name'] sql_text = SQL_META['sql_text'] query_time = float(SQL_META['query_time']) lock_time = float(SQL_META['lock_time']) rows_examined = int(SQL_META['rows_examined']) rows_sent = int(SQL_META['rows_sent']) thread_id = SQL_META['thread_id'] if not sql_text.startswith('commit'): sql = "insert into mysql_slowquery(host,port,tags,slow_log_file,start_time,client_host,db_name,sql_text,query_time,lock_time," \ "rows_examined,rows_sent,thread_id) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" values = (host, port, tags, slow_log_file, start_time, host_client, db_name, sql_text, query_time, lock_time, rows_examined, rows_sent, thread_id) tools.mysql_exec(sql, values)
def collect_data(self): oracle_backup_set_list = self.collect_backup_setup_info() oracle_backup_list = self.collect_backup_info() if oracle_backup_set_list: for oracle_backup_set in oracle_backup_set_list: insert_sql = "insert into oracle_backup_info(tags,BS_KEY,RECID,STAMP,START_TIME,COMPLETE_TIME,ESPLASED_SECONDS,OUTPUT_BYTES,BACKUP_TYPE,STATUS) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" values = (self.tags, oracle_backup_set[0], oracle_backup_set[1], oracle_backup_set[2], oracle_backup_set[3], oracle_backup_set[4], oracle_backup_set[5], oracle_backup_set[6], oracle_backup_set[7], oracle_backup_set[8]) tools.mysql_exec(insert_sql, values) if oracle_backup_list: for oracle_backup in oracle_backup_list: insert_sql = "insert into oracle_backup_piece(tags,BACKUP_SET,SET_STAMP,BACKUP_TYPE,HAS_CTL,STATUS,DEVICE_TYPE,START_TIME,COMPLETION_TIME,ELAPSED_TIME,SIZE,COMPRESSED,TAG,PATH) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" values = (self.tags, oracle_backup[0], oracle_backup[1], oracle_backup[2], oracle_backup[3], oracle_backup[4], oracle_backup[5], oracle_backup[6], oracle_backup[7], oracle_backup[8], oracle_backup[9], oracle_backup[10], oracle_backup[11], oracle_backup[12]) tools.mysql_exec(insert_sql, values)
def do_collect(): # 清空旧的备份信息 sql = 'delete from oracle_backup_info' tools.mysql_exec(sql, '') sql = 'delete from oracle_backup_piece' tools.mysql_exec(sql, '') # 取存活Oracle信息 sql = '''select tags,host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags in (select tags from oracle_db where mon_status = 'connected') ''' oracle_list = tools.mysql_query(sql) if oracle_list: for oracle in oracle_list: tags = oracle[0] host = oracle[1] port = oracle[2] service_name = oracle[3] user = oracle[4] password = oracle[5] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name conn = cx_Oracle.connect(user, password, url) oracle_collector = OracleBackupoInfo(tags, conn) oracle_collector.collect_data()
def get_oracle_alert(conn, tags, host, user, password, ssh_port, version): # 取后台日志路径 cur = conn.cursor() diag_trace_sql = "select value from v$diag_info where name = 'Diag Trace'" cur.execute(diag_trace_sql) diag_trace = cur.fetchall() diag_trace_dir = diag_trace[0][0] # 建立ssh连接,抓取后台日志 ssh_client = paramiko.SSHClient() ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh_client.connect(host, ssh_port, user, password) logfile = '%s/alert_*.log' % diag_trace_dir # 更新配置表中日志路径 sql = "update tab_oracle_servers set logfile='%s' where tags='%s' " % ( logfile, tags) tools.mysql_exec(sql, '') cmd = 'tail -300 %s' % logfile std_in, std_out, std_err = ssh_client.exec_command(cmd) fd = std_out log = fd.read() # 清空原日志数据 sql = "delete from alert_log where tags='%s' and server_type='Oracle' " % tags tools.mysql_exec(sql, '') parse_oracle_alert_logs(tags, host, oracle_alert_stream(log), version)
def check_alarm(): my_log.logger.info('初始化告警信息表') ins_sql = "insert into tab_alarm_info_his select * from tab_alarm_info" tools.mysql_exec(ins_sql, '') delete_sql = "delete from tab_alarm_info" tools.mysql_exec(delete_sql, '') check_list = tools.mysql_query( "select alarm_name,jdg_value,select_sql,jdg_sql,conf_table,conf_col from tab_alarm_conf where jdg_sql is not null " ) for each_check in check_list: # 取告警名称和阈值 alarm_name, jdg_value, select_sql, jdg_sql_conf, conf_table, conf_col = each_check alarm_name = alarm_name.encode('utf-8') my_log.logger.info("开始巡检:%s" % alarm_name) # 判断目标表是否采集到告警数据 select_res = tools.mysql_query(select_sql) if select_res[0][0] == 0: my_log.logger.info("%s未采集到数据" % alarm_name) else: # 采集数据阈值检查 is_judge_sql = ' tags in (select tags from %s where %s =1)' % ( conf_table, conf_col) jdg_sql = jdg_sql_conf % ( jdg_value, is_judge_sql) if jdg_value else jdg_sql_conf % is_judge_sql check_res = tools.mysql_query(jdg_sql) if check_res == 0: my_log.logger.info("%s:正常" % alarm_name) else: for each in check_res: tags, alarm_url, alarm_content = each alarm_title = tags + ':' + alarm_name alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = (tags, alarm_url, alarm_name, alarm_title, alarm_content) my_log.logger.warning(alarm_content) tools.mysql_exec(alarm_sql, value) # 发送告警邮件 is_send_email(alarm_name, tags, alarm_url, alarm_title, alarm_content)
def alarm(): my_log.logger.info('初始化告警信息表') delete_sql = "delete from tab_alarm_info" tools.mysql_exec(delete_sql, '') my_log.logger.info('开始巡检主机数据') # 主机通断告警 alarm_name = 'Linux主机通断告警' host_stat = tools.mysql_query( "select tags,host,host_name,mon_status from os_info") if host_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in host_stat: tags = str(line[0].encode("utf-8")) host_ip = str(line[1].encode("utf-8")) host_name = str(line[2].encode("utf-8")) host_status = str(line[3].encode("utf-8")) url = host_ip + '/' + host_name is_alarm = tools.mysql_query( "select connect from tab_linux_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if host_status == 'connected error': alarm_content = '%s:Linux主机通断告警 \n 告警时间:%s \n 主机ip:%s \n 主机名:%s \n' % ( tags, tools.now(), host_ip, host_name) email_header = '%s:Linux主机通断告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # CPU使用率告警 cpu_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Linux' and alarm_name='Linux主机CPU使用率告警'" ) alarm_name = 'Linux主机CPU使用率告警' pct_alarm = cpu_conf[0][1] cpu_stat = tools.mysql_query( "select tags,host,host_name,cpu_used from os_info where cpu_used is not null" ) if cpu_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in cpu_stat: tags = str(line[0].encode("utf-8")) host_ip = str(line[1].encode("utf-8")) host_name = str(line[2].encode("utf-8")) cpu_used = float(line[3]) url = host_ip + '/' + host_name is_alarm = tools.mysql_query( "select cpu from tab_linux_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if cpu_used > pct_alarm: alarm_content = '%s:Linux主机CPU使用率告警 \n 告警时间:%s \n 主机ip:%s \n 主机名:%s \n CPU使用率:%s \n' % ( tags, tools.now(), host_ip, host_name, cpu_used) email_header = '%s:Linux主机CPU使用率告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 内存使用率告警 mem_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Linux' and alarm_name='Linux主机内存使用率告警'" ) alarm_name = 'Linux主机内存使用率告警' pct_alarm = mem_conf[0][1] mem_stat = tools.mysql_query( "select tags,host,host_name,mem_used from os_info where mem_used is not null" ) if mem_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in mem_stat: tags = str(line[0].encode("utf-8")) host_ip = str(line[1].encode("utf-8")) host_name = str(line[2].encode("utf-8")) mem_used = float(line[3]) url = host_ip + '/' + host_name is_alarm = tools.mysql_query( "select mem from tab_linux_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if mem_used > pct_alarm: alarm_content = '%s:Linux主机内存使用率告警 \n 告警时间:%s \n 主机ip:%s \n 主机名:%s \n 内存使用率:%s \n' % ( tags, tools.now(), host_ip, host_name, mem_used) email_header = '%s:Linux主机内存使用率告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # swap使用率告警 swap_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Linux' and alarm_name='Linux主机swap使用率告警'" ) alarm_name = 'Linux主机swap使用率告警' pct_alarm = swap_conf[0][1] swap_stat = tools.mysql_query( "select tags,host,host_name,swap_used,swap_free from os_info where swap_used is not null" ) if mem_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in swap_stat: tags = str(line[0].encode("utf-8")) host_ip = str(line[1].encode("utf-8")) host_name = str(line[2].encode("utf-8")) swap_used = float(line[3]) swap_free = float(line[4]) swap_used_pct = float(swap_used / (swap_used + swap_free) / 2) * 100 url = host_ip + '/' + host_name is_alarm = tools.mysql_query( "select mem from tab_linux_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if swap_used_pct > pct_alarm: alarm_content = '%s:Linux主机swap使用率告警 \n 告警时间:%s \n 主机ip:%s \n 主机名:%s \n swap使用率:%s \n' % ( tags, tools.now(), host_ip, host_name, swap_used_pct) email_header = '%s:Linux主机swap使用率告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 文件系统使用率告警 # 使用率 disk_conf_pct = tools.mysql_query( "select alarm_name,jdg_value,jdg_des from tab_alarm_conf where server_type='Linux' and alarm_name='Linux主机文件系统使用率告警'" "and judge='>=' ") alarm_name = 'Linux主机文件系统使用率告警' pct_alarm = disk_conf_pct[0][1] # 剩余空间 disk_conf_gb = tools.mysql_query( "select alarm_name,jdg_value,jdg_des from tab_alarm_conf where server_type='Linux' and alarm_name='Linux主机文件系统使用率告警'" "and judge='<=' ") gb_alarm = disk_conf_gb[0][1] disk_stat = tools.mysql_query( "select tags,host,host_name,name,size,avail,pct_used from os_filesystem" ) if disk_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in disk_stat: tags = str(line[0].encode("utf-8")) host_ip = str(line[1].encode("utf-8")) host_name = str(line[2].encode("utf-8")) disk_name = str(line[3].encode("utf-8")) disk_size = str(line[4].encode("utf-8")) disk_avail = str(line[5].encode("utf-8")) disk_used = float(line[6]) url = host_ip + '/' + host_name is_alarm = tools.mysql_query( "select disk from tab_linux_servers where host = '%s'" % host_ip) if is_alarm[0][0] == '1': if disk_used > pct_alarm and disk_avail < gb_alarm: alarm_content = '%s:Linux主机文件系统使用率告警 \n 告警时间:%s \n 主机ip:%s \n 主机名:%s \n 目录名称:%s \n 目录总大小:%s \n 目录可用:%s \n 目录使用率:%s \n' % ( tags, tools.now(), host_ip, host_name, disk_name, disk_size, disk_avail, disk_used) email_header = '%s:Linux主机文件系统使用率告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 数据库通断告警 my_log.logger.info('开始巡检Oracle数据库数据') alarm_name = 'Oracle数据库通断告警' db_stat = tools.mysql_query( "select tags,host,port,service_name,mon_status from oracle_db") if db_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in db_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) db_status = str(line[4].encode("utf-8")) is_alarm = tools.mysql_query( "select connect from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if db_status == 'connected error': alarm_content = '%s:数据库通断告警 \n 告警时间:%s \n 数据库url:%s \n' % ( tags, tools.now(), url) email_header = '%s:数据库通断告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # oracle数据库综合性能告警 my_log.logger.info('开始巡检Oracle数据库等待事件数据') event_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库综合性能告警'" ) num_max = event_conf[0][1] alarm_name = 'Oracle数据库综合性能告警' event_sql = ''' select tags, host, port, service_name, cnt_all from (select tags, host, port, service_name, sum(event_cnt) cnt_all from oracle_db_event_his where timestampdiff(minute, chk_time, current_timestamp()) < %s group by tags, host, port, service_name) t where cnt_all > %d ''' % ( 5, num_max) event_stat = tools.mysql_query(event_sql) if event_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in event_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) event_cnt = str(line[4]) is_alarm = tools.mysql_query( "select oracle_event from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': alarm_content = '%s:数据库综合性能告警 \n 告警时间:%s \n 数据库url:%s \n' % ( tags, tools.now(), url) email_header = '%s:数据库综合性能告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 归档使用率告警 archive_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库归档使用率告警'" ) alarm_name = 'Oracle数据库归档使用率告警' pct_alarm = archive_conf[0][1] archive_stat = tools.mysql_query( "select tags,host,port,service_name,archive_used from oracle_db where archive_used is not null" ) if archive_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in archive_stat: tags = str(line[0].encode("utf-8")) archive_used = float(line[4]) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) is_alarm = tools.mysql_query( "select archive from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if archive_used > pct_alarm: alarm_content = '%s:数据库归档使用率告警 \n 告警时间:%s \n 数据库url:%s \n 使用率:%s \n' % ( tags, tools.now(), url, archive_used) email_header = '%s:Oracle数据库归档使用率告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 表空间使用率告警 # 使用率 tbs_conf_pct = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库表空间使用率告警'" "and judge='>=' ") alarm_name = str(tbs_conf_pct[0][0].encode("utf-8")) pct_alarm = tbs_conf_pct[0][1] # 剩余空间 tbs_conf_gb = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库表空间使用率告警'" "and judge='<=' ") gb_alarm = tbs_conf_gb[0][1] tbs_stat = tools.mysql_query( 'select tags,host,port,service_name,tbs_name,size_gb,free_gb,pct_used from oracle_tbs' ) if tbs_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in tbs_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) tbs_name = str(line[4].encode("utf-8")) tbs_size_gb = float(line[5].encode("utf-8")) tbs_pct_used = float(line[7]) tbs_free_gb = float(line[6].encode("utf-8")) is_alarm = tools.mysql_query( "select tbs from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if tbs_pct_used > pct_alarm and tbs_free_gb < gb_alarm: alarm_content = '%s:表空间使用率告警 \n 告警时间:%s \n 数据库url:%s \n 表空间名:%s \n 表空间大小:%s \n 表空间使用率:%s \n 表空间剩余大小:%s \n' % ( tags, tools.now(), url, tbs_name, tbs_size_gb, tbs_pct_used, tbs_free_gb) email_header = '%s:%s表空间使用率告警' % (tags, tbs_name) my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # adg延迟告警 adg_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库adg延迟告警'" ) alarm_name = 'Oracle数据库adg延迟告警' time_alarm = adg_conf[0][1] adg_stat = tools.mysql_query( "select tags,host,port,service_name,adg_transport_lag,adg_transport_value,adg_apply_lag,adg_apply_value from oracle_db where adg_transport_lag is not null or adg_apply_lag is not null" ) if adg_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in adg_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) if line[5] == None: my_log.logger.warning('未采集到数据(transport lag)') adg_transport_value = '未采集到数据' else: adg_transport_value = float(line[5]) if line[7] == None: my_log.logger.warning('未采集到数据(apply lag)') adg_apply_value = '未采集到数据' else: adg_apply_value = float(line[7]) is_alarm = tools.mysql_query( "select adg from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if adg_transport_value > time_alarm or adg_apply_value > time_alarm: alarm_content = '%s:数据库adg延迟告警 \n 告警时间:%s \n 数据库url:%s \n 延迟时间(transport):%s \n 延迟时间(apply):%s \n' % ( tags, tools.now(), url, adg_transport_value, adg_apply_value) email_header = '%s:数据库adg延迟告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 临时表空间使用率告警 # 使用率 tmp_tbs_pct_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库临时表空间告警'" "and judge='>=' ") alarm_name = str(tmp_tbs_pct_conf[0][0].encode("utf-8")) pct_alarm = tmp_tbs_pct_conf[0][1] # 剩余空间 tmp_tbs_gb_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库临时表空间告警'" "and judge='<=' ") gb_alarm = tmp_tbs_gb_conf[0][1] tmp_tbs_stat = tools.mysql_query( 'select tags,host,port,service_name,tmp_tbs_name,total_mb,used_mb,pct_used from oracle_tmp_tbs' ) if tmp_tbs_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in tmp_tbs_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) tmp_tbs_name = str(line[4].encode("utf-8")) tmp_tbs_total_mb = float(line[5].encode("utf-8")) tmp_tbs_used_mb = float(line[6].encode("utf-8")) tmp_tbs_pct_used = float(line[7].encode("utf-8")) is_alarm = tools.mysql_query( "select temp_tbs from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if tmp_tbs_pct_used > pct_alarm and ( tmp_tbs_total_mb - tmp_tbs_used_mb) < gb_alarm: alarm_content = '%s:临时表空间使用率告警 \n 告警时间:%s \n 数据库url:%s \n 临时表空间名:%s \n 临时表空间大小(mb):%s \n 临时表空间已使用大小(mb):%s \n 临时表空间使用率:%s \n' % ( tags, tools.now(), url, tmp_tbs_name, tmp_tbs_total_mb, tmp_tbs_used_mb, tmp_tbs_pct_used) email_header = '%s:%s临时表空间使用率告警' % (tags, tmp_tbs_name) my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # undo空间使用率告警 undo_tbs_pct_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库Undo表空间告警'" "and judge='>=' ") alarm_name = str(undo_tbs_pct_conf[0][0].encode("utf-8")) pct_alarm = undo_tbs_pct_conf[0][1] # 剩余空间 undo_tbs_gb_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库Undo表空间告警'" "and judge='<=' ") gb_alarm = undo_tbs_gb_conf[0][1] undo_tbs_stat = tools.mysql_query( 'select tags,host,port,service_name,undo_tbs_name,total_mb,used_mb,pct_used from oracle_undo_tbs' ) if undo_tbs_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in undo_tbs_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) undo_tbs_name = str(line[4].encode("utf-8")) undo_tbs_total_mb = float(line[5].encode("utf-8")) undo_tbs_used_mb = float(line[6].encode("utf-8")) undo_tbs_pct_used = float(line[7].encode("utf-8")) is_alarm = tools.mysql_query( "select undo_tbs from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if undo_tbs_pct_used > pct_alarm and ( undo_tbs_total_mb - undo_tbs_used_mb) < gb_alarm: alarm_content = '%s:undo表空间使用率告警 \n 告警时间:%s \n 数据库url:%s \n undo表空间名:%s \n undo表空间大小(mb):%s \n undo表空间已使用大小(mb):%s \n undo表空间使用率:%s \n' % ( tags, tools.now(), url, undo_tbs_name, undo_tbs_total_mb, undo_tbs_used_mb, undo_tbs_pct_used) email_header = '%s:%sundo表空间使用率告警' % (tags, undo_tbs_name) my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 连接数使用率告警 conn_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库连接数告警'" ) alarm_name = str(conn_conf[0][0].encode("utf-8")) pct_alarm = conn_conf[0][1] process_stat = tools.mysql_query( 'select tags,host,port,service_name,max_process,current_process,percent_process from oracle_db where current_process is not null' ) if process_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in process_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) if line[4] == None or line[5] == None or line[6] == None: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: max_process = float(line[4]) current_process = float(line[5]) process_pct_used = float(line[6]) is_alarm = tools.mysql_query( "select conn from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if process_pct_used > pct_alarm: alarm_content = '%s:连接数告警 \n 告警时间:%s \n 数据库url:%s \n 最大连接数:%s \n 已使用连接数:%s \n 连接数使用率百分比:%s \n' % ( tags, tools.now(), url, max_process, current_process, process_pct_used) email_header = '%s:连接数告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 无效索引告警 alarm_name = 'Oracle失效索引告警' index_stat = tools.mysql_query( "select tags,host,port,service_name,owner,index_name,partition_name,status from oracle_invalid_index" ) if index_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in index_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) is_alarm = tools.mysql_query( "select invalid_index from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': owner = str(line[4]) index_name = str(line[5]) partition_name = str(line[6]) status = str(line[7]) alarm_content = '%s:数据库失效索引 \n 告警时间:%s \n 数据库url:%s \n 用户:%s \n 索引名称:%s \n 分区名称:%s \n 索引状态:%s \n' % ( tags, tools.now(), url, owner, index_name, partition_name, status) email_header = '%s:数据库失效索引告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 锁异常告警 alarm_name = 'Oracle锁异常告警' oracle_lock_conf = tools.mysql_query( "select alarm_name,jdg_value from tab_alarm_conf where server_type='Oracle' and alarm_name='Oracle数据库锁异常告警'" ) time_max = oracle_lock_conf[0][1] lock_sql = "select tags,host,port,service_name,session,ctime,inst_id,type from oracle_lock where session like 'Waiter%%' and ctime > '%s'" % time_max lock_stat = tools.mysql_query(lock_sql) if lock_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in lock_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) is_alarm = tools.mysql_query( "select oracle_lock from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': session = str(line[4]) ctime = str(line[5]) inst_id = str(line[6]) type = str(line[7]) alarm_content = '%s:数据库锁异常 \n 告警时间:%s \n 数据库url:%s \n 会话SID:%s \n 等待时间:%s \n 实例编号:%s \n 锁类型:%s \n' % ( tags, tools.now(), url, session, ctime, inst_id, type) email_header = '%s:数据库锁异常告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 密码过期告警 alarm_name = '密码过期告警' pwd_stat = tools.mysql_query( "select tags,host,port,service_name,username,result_number from oracle_expired_pwd" ) if pwd_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in pwd_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) is_alarm = tools.mysql_query( "select oracle_pwd from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': username = str(line[4]) result_number = str(line[5]) alarm_content = '%s:数据库用户密码过期 \n 告警时间:%s \n 数据库url:%s \n 用户名:%s \n 到期时间:%s \n' % ( tags, tools.now(), url, username, result_number) email_header = '%s:数据库用户密码过期告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # 数据库后台日志告警 alarm_name = 'Oracle后台日志告警' db_stat = tools.mysql_query( "select tags,host,port,service_name,err_info from oracle_db") if db_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in db_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str( line[2]) + '/' + str(line[3].encode("utf-8")) is_alarm = tools.mysql_query( "select err_info from tab_oracle_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if str(line[4]) != 'None' and str(line[4]): err_info = str(line[4]) alarm_content = '%s:数据库后台日志 \n 告警时间:%s \n 数据库url:%s \n 异常信息:%s \n' % ( tags, tools.now(), url, err_info) email_header = '%s:数据库后台日志告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name)) # mysql数据库通断告警 my_log.logger.info('开始巡检Mysql数据库数据') alarm_name = 'MySQL数据库通断告警' db_stat = tools.mysql_query( "select tags,host,port,mon_status from mysql_db") if db_stat == 0: my_log.logger.warning('未采集到数据:%s' % alarm_name) else: for line in db_stat: tags = str(line[0].encode("utf-8")) url = str(line[1].encode("utf-8")) + ':' + str(line[2]) db_status = str(line[3].encode("utf-8")) is_alarm = tools.mysql_query( "select connect from tab_mysql_servers where tags = '%s'" % tags) if is_alarm[0][0] == '1': if db_status == 'connected error': alarm_content = '%s:MySQL数据库通断告警 \n 告警时间:%s \n 数据库url:%s \n' % ( tags, tools.now(), url) email_header = '%s:MySQL数据库通断告警' % tags my_log.logger.info(alarm_content) alarm_sql = 'insert into tab_alarm_info(tags,url,alarm_type,alarm_header,alarm_content) value(%s,%s,%s,%s,%s)' value = ( tags, url, alarm_name, email_header, alarm_content, ) tools.mysql_exec(alarm_sql, value) is_send_email(alarm_name, tags, url, email_header, alarm_content) else: my_log.logger.info('%s未设置%s' % (tags, alarm_name))
def ora_check(tags_l, begin_time, end_time, file_name, file_tag): # 写excel file_path = os.getcwd() + '/check_result/' template = 'oracheck.xls' rb = xlrd.open_workbook(file_path + template, formatting_info=True) wb = copy(rb) row = 1 for tags in tags_l: ws = wb.get_sheet(0) errinfo = '' check_txt_file = file_path + '/oracheck_' + file_tag + '.txt' check_txt = file(check_txt_file, 'a+') print >> check_txt, '*******oracle数据库:%s 开始时间:%s 结束时间:%s *******\n' % ( tags, begin_time, end_time) # 检查连接数 # 当前连接数 print >> check_txt, '-------连接数-------' current_conn_sql = tools.mysql_query( "select percent_process from oracle_db where tags= '%s'" % tags) current_conn = int(current_conn_sql[0][0]) # 最大连接数 para_conn_sql = tools.mysql_query( "select max_process from oracle_db where tags= '%s'" % tags) para_conn = int(para_conn_sql[0][0]) print >> check_txt, '总连接数:%s' % para_conn # 指定时间段内连接数使用情况] conn_sql = tools.mysql_query( "select instance_name, max(percent_process),min(percent_process),avg(percent_process) from oracle_db_his where tags= '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S')> '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S') < '%s' group by instance_name" % (tags, begin_time, end_time)) instance_name = conn_sql[0][0] max_conn = conn_sql[0][1] min_conn = conn_sql[0][2] avg_conn = round(conn_sql[0][3], 2) conn_range = '最大使用率:%s%%,\n' % max_conn + '最小使用率:%s%%,\n' % min_conn + '平均使用率:%s%%,\n' % avg_conn print >> check_txt, '最大使用率:%s 最小使用率:%s 平均使用率:%s \n' % ( max_conn, min_conn, avg_conn) if max_conn > 10: conn_errinfo = '连接数最大使用率%s%%,超过10%%\n' % max_conn insert_sql = "insert into check_info(check_tag,check_type,server_tag,check_err_type,check_err,begin_time,end_time) values(%s,%s,%s,%s,%s,%s,%s)" value = (file_tag, 'Oracle数据库', tags, '连接数', conn_errinfo, begin_time, end_time) tools.mysql_exec(insert_sql, value) errinfo = conn_errinfo # 检查表空间 print >> check_txt, '-------表空间-------' tbs_sql = tools.mysql_query( "select tbs_name,free_gb,pct_used from oracle_tbs where tags = '%s' and pct_used > 90 and free_gb < 5" % tags) tbsinfo = '' err_tbsinfo = '' for tbs in tbs_sql: tbs_name = str(tbs[0].encode("utf-8")) free_gb = float(tbs[1].encode("utf-8")) pct_used = tbs[2] each_tbs_info = '表空间名称:%s,剩余空间:%sGB,使用率:%s%% \n' % ( tbs_name, free_gb, pct_used) each_err_tbsinfo = '%s表空间,' % tbs_name if free_gb < 5: each_err_tbsinfo = each_err_tbsinfo + '剩余空间%sG,小于5G ' % free_gb if pct_used > 90: each_err_tbsinfo = each_err_tbsinfo + '使用率%s%%,大于90%%' % pct_used tbsinfo = tbsinfo + each_tbs_info err_tbsinfo = err_tbsinfo + each_err_tbsinfo + '\n' insert_sql = "insert into check_info(check_tag,check_type,server_tag,check_err_type,check_err,begin_time,end_time) values(%s,%s,%s,%s,%s,%s,%s)" value = (file_tag, 'Oracle数据库', tags, '表空间', each_err_tbsinfo, begin_time, end_time) tools.mysql_exec(insert_sql, value) errinfo = errinfo + err_tbsinfo if not tbsinfo: err_tbsinfo = '正常' print >> check_txt, tbsinfo # 检查Undo表空间 print >> check_txt, '-------undo表空间-------' undo_tbs_sql = tools.mysql_query( "select undo_tbs_name,max(used_mb),max(pct_used),min(pct_used),avg(pct_used) from oracle_undo_tbs_his where tags = '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S')> '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S') < '%s' group by undo_tbs_name" % (tags, begin_time, end_time)) undotbsinfo = '' err_undotbsinfo = '' if undo_tbs_sql: for undotbs in undo_tbs_sql: undotbs_name = str(undotbs[0].encode("utf-8")) used_mb = float(undotbs[1].encode("utf-8")) max_pct_used = float(undotbs[2].encode("utf-8")) min_pct_used = float(undotbs[3].encode("utf-8")) avg_pct_used = round(float(undotbs[4]), 2) each_undotbs_info = 'UNDO表空间名称:%s,已使用空间:%sMB,最大使用率:%s%% 最小使用率:%s%% 平均使用率:%s%% \n' % ( undotbs_name, used_mb, max_pct_used, min_pct_used, avg_pct_used) each_err_undotbsinfo = '' if max_pct_used > 1: each_err_undotbsinfo = '%s表空间,' % undotbs_name each_err_undotbsinfo = each_err_undotbsinfo + ' 最大使用率%s%%,大于90%%' % max_pct_used err_undotbsinfo = err_undotbsinfo + each_err_undotbsinfo + '\n' insert_sql = "insert into check_info(check_tag,check_type,server_tag,check_err_type,check_err,begin_time,end_time) values(%s,%s,%s,%s,%s,%s,%s)" value = (file_tag, 'Oracle数据库', tags, 'undo表空间', each_err_undotbsinfo, begin_time, end_time) tools.mysql_exec(insert_sql, value) undotbsinfo = undotbsinfo + each_undotbs_info errinfo = errinfo + err_undotbsinfo print >> check_txt, undotbsinfo # 检查临时表空间 print >> check_txt, '-------临时表空间-------' tmp_tbs_sql = tools.mysql_query( "select tmp_tbs_name,max(used_mb),max(pct_used),min(pct_used),avg(pct_used) from oracle_tmp_tbs_his where tags = '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S')> '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S') < '%s' group by tmp_tbs_name" % (tags, begin_time, end_time)) tmptbsinfo = '' err_tmptbsinfo = '' if tmptbsinfo: for tmptbs in tmp_tbs_sql: tmptbs_name = str(tmptbs[0].encode("utf-8")) used_mb = float(tmptbs[1].encode("utf-8")) max_pct_used = float(tmptbs[2].encode("utf-8")) min_pct_used = float(tmptbs[3].encode("utf-8")) avg_pct_used = round(float(tmptbs[4]), 2) each_tmptbs_info = 'TMP表空间名称:%s,使用空间:%sMB,最大使用率:%s%% 最小使用率:%s%% 平均使用率:%s%% \n' % ( tmptbs_name, used_mb, max_pct_used, min_pct_used, avg_pct_used) each_err_tmptbsinfo = '' if max_pct_used > 1: each_err_tmptbsinfo = '%s表空间,' % tmptbs_name each_err_tmptbsinfo = each_err_tmptbsinfo + ' 最大使用率%s%%,大于90%%' % max_pct_used err_tmptbsinfo = err_tmptbsinfo + each_err_tmptbsinfo + '\n' insert_sql = "insert into check_info(check_tag,check_type,server_tag,check_err_type,check_err,begin_time,end_time) values(%s,%s,%s,%s,%s,%s,%s)" value = (file_tag, 'Oracle数据库', tags, 'TMP表空间', each_err_tmptbsinfo, begin_time, end_time) tools.mysql_exec(insert_sql, value) tmptbsinfo = tmptbsinfo + each_tmptbs_info print >> check_txt, undotbsinfo errinfo = errinfo + err_tmptbsinfo # 检查cpu使用率,内存使用率 # 当前使用率 linux_used = tools.mysql_query( "select cpu_used,mem_used from os_info where host = (select host from tab_oracle_servers where tags = '%s')" % tags) cpu_used = float(linux_used[0][0]) mem_used = float(linux_used[0][1]) # cpu_range = tools.mysql_query( "select max(cpu_used),min(cpu_used),avg(cpu_used),max(mem_used),min(mem_used),avg(mem_used) from os_info_his where host = (select host from tab_oracle_servers where tags = '%s') and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S')> '%s' and DATE_FORMAT(chk_time,'%%Y-%%m-%%d %%H:%%i:%%S') < '%s'" % (tags, begin_time, end_time)) cpu_max = round(float(cpu_range[0][0]), 2) cpu_min = round(float(cpu_range[0][1]), 2) cpu_avg = round(float(cpu_range[0][2]), 2) mem_max = round(float(cpu_range[0][3]), 2) mem_min = round(float(cpu_range[0][4]), 2) mem_avg = round(float(cpu_range[0][5]), 2) print >> check_txt, '-------cpu使用率-------' cpu_range_value = '最大使用率:%s%%,\n' % cpu_max + '最小使用率:%s%%,\n' % cpu_min + '平均使用率:%s%%,\n' % cpu_avg print >> check_txt, cpu_range_value if cpu_max > 10: cpu_errinfo = ' CPU最大使用率%s%%,超过10%%\n' % cpu_max insert_sql = "insert into check_info(check_tag,check_type,server_tag,check_err_type,check_err,begin_time,end_time) values(%s,%s,%s,%s,%s,%s,%s)" value = (file_tag, 'Oracle数据库', tags, 'CPU使用率', cpu_errinfo, begin_time, end_time) tools.mysql_exec(insert_sql, value) errinfo = errinfo + cpu_errinfo print >> check_txt, '-------内存使用率-------' mem_range_value = '最大使用率:%s%%,\n' % mem_max + '最小使用率:%s%%,\n' % mem_min + '平均使用率:%s%%,\n' % mem_avg print >> check_txt, mem_range_value if mem_max > 10: mem_errinfo = '内存最大使用率%s%%,超过10%%\n' % mem_max insert_sql = "insert into check_info(check_tag,check_type,server_tag,check_err_type,check_err,begin_time,end_time) values(%s,%s,%s,%s,%s,%s,%s)" value = (file_tag, 'Oracle数据库', tags, '内存使用率', mem_errinfo, begin_time, end_time) tools.mysql_exec(insert_sql, value) errinfo = errinfo + mem_errinfo ws.write(row, 0, begin_time) ws.write(row, 1, end_time) ws.write(row, 2, tags) ws.write(row, 3, instance_name) ws.write(row, 4, current_conn) ws.write(row, 5, para_conn) ws.write(row, 6, conn_range) ws.write(row, 7, unicode('正常', 'utf-8')) ws.write(row, 8, unicode(tbsinfo, 'utf-8')) ws.write(row, 9, unicode(undotbsinfo, 'utf-8')) ws.write(row, 10, unicode(tmptbsinfo, 'utf-8')) ws.write(row, 11, unicode('正常', 'utf-8')) ws.write(row, 12, unicode('正常', 'utf-8')) ws.write(row, 13, unicode('正常', 'utf-8')) ws.write(row, 14, unicode(cpu_range_value, 'utf-8')) ws.write(row, 15, unicode(mem_range_value, 'utf-8')) check_path = os.getcwd() + '\check_result' + '\\' wb.save(check_path + file_name) print >> check_txt, '-------%s巡检异常-------' % tags print >> check_txt, errinfo check_txt.close() row += 1
ssh_client = paramiko.SSHClient() ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh_client.connect(host, 22, user, password) cmd = 'tail -300 %s' % alert_log std_in, std_out, std_err = ssh_client.exec_command(cmd) fd = std_out log = fd.read() # 清空原日志数据 sql = "delete from alert_log where tags='%s' and server_type='MySQL' " % tags tools.mysql_exec(sql, '') parse_mysql_alert_logs(tags, host, oracle_alert_stream(log)) if __name__ == '__main__': tags = 'mysql' host = '192.168.48.50' port = 3306 user = '******' password = '******' conn = MySQLdb.connect(host=host, user='******', passwd='mysqld', port=int(port), connect_timeout=5, charset='utf8') # 清空原日志数据 sql = "delete from alert_log where tags='%s' " % tags tools.mysql_exec(sql, '') get_mysql_alert(conn, tags, host, user, password)
def check_oracle(tags, host, port, service_name, user, password, user_os, password_os): my_log.logger.info('%s等待2秒待linux主机信息采集完毕' % tags) time.sleep(2) password = base64.decodestring(password) password_os = base64.decodestring(password_os) url = host + ':' + port + '/' + service_name # 转移监控当前表数据到历史表 # tools.mysql_exec('insert into oracle_db_his select * from oracle_db', '') # tools.mysql_exec('insert into oracle_tbs_his select * from oracle_tbs', '') # tools.mysql_exec('insert into oracle_tmp_tbs_his select * from oracle_tmp_tbs', '') # tools.mysql_exec('insert into oracle_undo_tbs_his select * from oracle_undo_tbs', '') # tools.mysql_exec('insert into os_info_his select * from os_info', '') # tools.mysql_exec('insert into os_filesystem_his select * from os_filesystem', '') # tools.mysql_exec('delete from oracle_db', '') # tools.mysql_exec('delete from oracle_tbs', '') # tools.mysql_exec('delete from oracle_tmp_tbs', '') # tools.mysql_exec('delete from oracle_undo_tbs', '') # tools.mysql_exec('delete from os_info', '') # tools.mysql_exec('delete from os_filesystem', '') try: conn = cx_Oracle.connect(user, password, url) # 表空间监控 my_log.logger.info('%s:开始获取Oracle数据库表空间监控信息' % tags) my_log.logger.info('%s:初始化oracle_tbs表' % tags) insert_sql = "insert into oracle_tbs_his select * from oracle_tbs where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from oracle_tbs where tags = '%s' " % tags tools.mysql_exec(delete_sql, '') tbs = check_ora.check_tbs(conn) for line in tbs: if not line[6]: line[6] = 0 tbs_percent = float(line[6]) tbs_rate_level = tools.get_rate_level(tbs_percent) insert_tbs_sql = "insert into oracle_tbs(tags,host,port,service_name,tbs_name,datafile_count,size_gb,free_gb,used_gb,max_free,pct_used,pct_free,rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], tbs_rate_level) my_log.logger.info('%s:获取Oracle数据库表空间使用率(表空间名:%s 使用率:%s)' % (tags, line[0], line[6])) tools.mysql_exec(insert_tbs_sql, value) # db信息监控 my_log.logger.info('%s:开始获取Oracle数据库监控信息' % tags) my_log.logger.info('%s:初始化oracle_db表' % tags) insert_sql = "insert into oracle_db_his select * from oracle_db where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from oracle_db where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') dbnameinfo = check_ora.get_dbname_info(conn) instance_info = check_ora.get_instance_info(conn) process = check_ora.check_process(conn) asm = check_ora.check_asm(conn) archive_used = check_ora.get_archived(conn) if not archive_used: archive_used = [('None'), 0] archive_rate_level = 'None' else: archive_rate_level = tools.get_rate_level(archive_used[0][0]) adg_trs = check_ora.check_adg_trs(conn) adg_apl = check_ora.check_adg_apl(conn) err_info = check_ora.check_err(conn, host, user_os, password_os) db_rate_level = 'green' # 连接数评级 conn_percent = float(process[0][3]) conn_rate_level = tools.get_rate_level(conn_percent) # adg if len(adg_trs) > 0: # adg传输评级 transport_value = float(adg_trs[0][1]) if transport_value >= 60 * 5: transport_rate_level = 'red' elif transport_value > 0 and transport_value < 60 * 5: transport_rate_level = 'yellow' else: transport_rate_level = 'green' apply_value = float(adg_apl[0][1]) if apply_value >= 60 * 5: apply_rate_level = 'red' elif apply_value > 0 and transport_value < 60 * 5: apply_rate_level = 'yellow' else: apply_rate_level = 'green' insert_db_sql = "insert into oracle_db(tags,host,port,service_name,dbname,db_unique_name,database_role,open_mode,log_mode,archive_used,archive_rate_level,inst_id,instance_name,host_name,max_process,current_process,percent_process,conn_rate_level,adg_transport_lag,adg_apply_lag,adg_transport_value,adg_transport_rate_level,adg_apply_value,adg_apply_rate_level,mon_status,err_info,rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, dbnameinfo[0][0], dbnameinfo[0][1], dbnameinfo[0][2], dbnameinfo[0][3], dbnameinfo[0][4], archive_used[0][0], archive_rate_level, instance_info[0][0], instance_info[0][1], instance_info[0][2], process[0][2], process[0][1], process[0][3], conn_rate_level, adg_trs[0][0], adg_apl[0][0], adg_trs[0][1], transport_rate_level, adg_apl[0][1], apply_rate_level, 'connected', err_info, db_rate_level) tools.mysql_exec(insert_db_sql, value) my_log.logger.info( '%s:获取Oracle数据库监控数据(数据库名:%s 数据库角色:%s 数据库状态:%s 连接数使用率:%s )' % (tags, dbnameinfo[0][0], dbnameinfo[0][2], dbnameinfo[0][3], process[0][3])) # not adg else: insert_db_sql = "insert into oracle_db(tags,host,port,service_name,dbname,db_unique_name,database_role,open_mode,log_mode,archive_used,archive_rate_level,inst_id,instance_name,host_name,max_process,current_process,percent_process,conn_rate_level,mon_status,err_info,rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, dbnameinfo[0][0], dbnameinfo[0][1], dbnameinfo[0][2], dbnameinfo[0][3], dbnameinfo[0][4], archive_used[0][0], archive_rate_level, instance_info[0][0], instance_info[0][1], instance_info[0][2], process[0][2], process[0][1], process[0][3], conn_rate_level, 'connected', err_info, db_rate_level) tools.mysql_exec(insert_db_sql, value) my_log.logger.info( '%s:获取Oracle数据库监控数据(数据库名:%s 数据库角色:%s 数据库状态:%s 连接数使用率:%s )' % (tags, dbnameinfo[0][0], dbnameinfo[0][2], dbnameinfo[0][3], process[0][3])) # 密码过期信息监控 my_log.logger.info('%s:开始获取Oracle数据库用户密码过期信息' % tags) my_log.logger.info('%s:初始化oracle_expired_pwd表' % tags) delete_sql = "delete from oracle_expired_pwd where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') pwd_info = check_ora.get_pwd_info(conn) for line in pwd_info: insert_pwd_info_sql = "insert into oracle_expired_pwd(tags,host,port,service_name,username,result_number) values(%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[1]) tools.mysql_exec(insert_pwd_info_sql, value) # 等待事件监控 my_log.logger.info('%s:开始获取Oracle数据库等待事件信息' % tags) my_log.logger.info('%s:初始化oracle_db_event表' % tags) insert_sql = "insert into oracle_db_event_his select * from oracle_db_event where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from oracle_db_event where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') event_info = check_ora.get_event_info(conn) for line in event_info: insert_event_info_sql = "insert into oracle_db_event(tags,host,port,service_name,event_no,event_name,event_cnt) values(%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[1], line[2]) tools.mysql_exec(insert_event_info_sql, value) # 锁等待监控 my_log.logger.info('%s:开始获取Oracle数据库锁等待信息' % tags) my_log.logger.info('%s:初始化oracle_Lock表' % tags) delete_sql = "delete from oracle_lock where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') lock_info = check_ora.get_lock_info(conn) for line in lock_info: insert_lock_info_sql = "insert into oracle_lock(tags,host,port,service_name,session,lmode,ctime,inst_id,lmode1,type) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[1], line[2], line[3], line[6], line[8]) tools.mysql_exec(insert_lock_info_sql, value) my_log.logger.info('%s 获取Oracle数据库锁等待信息' % tags) # 无效索引监控 my_log.logger.info('%s:开始获取Oracle数据库无效索引信息' % tags) my_log.logger.info('%s:初始化oracle_invalid_index表' % tags) delete_sql = "delete from oracle_invalid_index where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') invalid_index_info = check_ora.get_invalid_index(conn) for line in invalid_index_info: insert_invalid_index_info_sql = "insert into oracle_invalid_index(tags,host,port,service_name,owner,index_name,partition_name,status) values(%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[1], line[2], line[3]) tools.mysql_exec(insert_invalid_index_info_sql, value) my_log.logger.info('%s 获取Oracle数据库无效索引信息' % tags) # 临时表空间监控 my_log.logger.info('%s:开始获取Oracle数据库临时表空间监控信息' % tags) my_log.logger.info('%s:初始化oracle_tmp_tbs表' % tags) insert_sql = "insert into oracle_tmp_tbs_his select * from oracle_tmp_tbs where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from oracle_tmp_tbs where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') tmp_tbs = check_ora.check_tmp_tbs(conn) for line in tmp_tbs: tmp_pct_used = float(line[3]) tmp_rate_level = tools.get_rate_level(tmp_pct_used) insert_tmp_tbs_sql = "insert into oracle_tmp_tbs(tags,host,port,service_name,tmp_tbs_name,total_mb,used_mb,pct_used,rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[1], line[2], line[3], tmp_rate_level) tools.mysql_exec(insert_tmp_tbs_sql, value) my_log.logger.info('%s:获取Oracle数据库临时表空间使用率(temp表空间名:%s 使用率:%s)' % (tags, line[0], line[3])) # undo表空间监控 my_log.logger.info('%s:开始获取Oracle数据库undo表空间监控信息' % tags) my_log.logger.info('%s:初始化oracle_undo_tbs表' % tags) insert_sql = "insert into oracle_undo_tbs_his select * from oracle_undo_tbs where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from oracle_undo_tbs where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') undo_tbs = check_ora.check_undo_tbs(conn) for line in undo_tbs: undo_pct_used = float(line[3]) undo_rate_level = tools.get_rate_level(undo_pct_used) insert_undo_tbs_sql = "insert into oracle_undo_tbs(tags,host,port,service_name,undo_tbs_name,total_mb,used_mb,pct_used,rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, line[0], line[2], line[1], line[3], undo_rate_level) tools.mysql_exec(insert_undo_tbs_sql, value) my_log.logger.info('%s:获取Oracle数据库undo表空间使用率(undo表空间名:%s 使用率:%s)' % (tags, line[0], line[3])) # 更新数据库打分信息 my_log.logger.info('%s :开始更新Oracle数据库评分信息' % tags) delete_sql = "delete from oracle_db_rate where tags= '%s'" % tags tools.mysql_exec(delete_sql, '') # 内存使用率扣分 mem_stat = tools.mysql_query( "select host,host_name,mem_used from os_info where mem_used is not null and host = '%s'" % host) if mem_stat == 0: my_log.logger.warning('%s:内存使用率未采集到数据' % tags) db_mem_decute = 0 db_mem_decute_reason = '' else: db_mem_used = float(mem_stat[0][2]) db_mem_decute = tools.get_decute(db_mem_used) if db_mem_decute <> 0: db_mem_decute_reason = '内存使用率:%d%% \n' % db_mem_used else: db_mem_decute_reason = '' # cpu使用率扣分 cpu_stat = tools.mysql_query( "select host,host_name,cpu_used from os_info where cpu_used is not null and host = '%s'" % host) if cpu_stat == 0: my_log.logger.warning('%s:CPU使用率未采集到数据' % tags) db_cpu_decute = 0 db_cpu_decute_reason = '' else: db_cpu_used = float(cpu_stat[0][2]) db_cpu_decute = tools.get_decute(db_cpu_used) if db_cpu_decute <> 0: db_cpu_decute_reason = 'CPU使用率:%d%% \n' % db_cpu_used else: db_cpu_decute_reason = '' # 连接数扣分 process_stat = tools.mysql_query( "select max_process,current_process,percent_process from oracle_db where current_process is not null and tags = '%s'" % tags) if process_stat == 0: my_log.logger.warning('%s:连接数未采集到数据' % tags) db_conn_decute = 0 db_conn_decute_reason = '' else: db_conn_used = float(process_stat[0][2]) db_conn_decute = tools.get_decute(db_conn_used) if db_conn_decute <> 0: db_conn_decute_reason = '连接数:%d%% \n' % db_conn_used else: db_conn_decute_reason = '' # 归档使用率扣分 archive_stat = tools.mysql_query( "select archive_used from oracle_db where archive_used is not null and tags = '%s'" % tags) if archive_stat == 0: my_log.logger.warning('%s:归档未采集到数据' % tags) db_archive_decute = 0 db_archive_decute_reason = '' else: db_archive_used = float(archive_stat[0][0]) db_archive_decute = tools.get_decute(db_archive_used) if db_archive_decute <> 0: db_archive_decute_reason = '归档使用率:%d%% \n' % db_archive_used else: db_archive_decute_reason = '' # 综合性能扣分 event_sql = ''' select tags, host, port, service_name, cnt_all from (select tags, host, port, service_name, sum(event_cnt) cnt_all from oracle_db_event_his where tags = '%s' and timestampdiff(minute, chk_time, current_timestamp()) < %s group by tags, host, port, service_name) t ''' % ( tags, 10) event_stat = tools.mysql_query(event_sql) if event_stat == 0: my_log.logger.warning('%s:归档未采集到数据' % tags) db_event_decute = 0 db_event_decute_reason = '' else: db_event_cnt = float(event_stat[0][4]) db_event_decute = db_event_cnt / 10 if db_event_decute <> 0: db_event_decute_reason = '等待时间数量:%d \n' % db_event_cnt else: db_event_decute_reason = '' # 表空间使用率扣分 tbs_stat = tools.mysql_query( "select host,port,service_name,tbs_name,size_gb,free_gb,pct_used from oracle_tbs where tags='%s'" % tags) if tbs_stat == 0: my_log.logger.warning('%s:表空间使用率未采集到数据' % host) db_tbs_decute_reason = '' db_tbs_decute = 0 else: db_tbs_decute_reason = '' db_tbs_decute = 0 for each_tbs_stat in tbs_stat: each_tbs_name = each_tbs_stat[3] each_tbs_free = float(each_tbs_stat[5]) each_tbs_used = float(each_tbs_stat[6]) db_each_tbs_decute = tools.get_decute_tbs( each_tbs_used, each_tbs_free) if db_each_tbs_decute <> 0: db_each_tbs_decute_reason = '%s表空间使用率:%d%% 剩余空间:%d \n' % ( each_tbs_name, each_tbs_used, each_tbs_free) else: db_each_tbs_decute_reason = '' db_tbs_decute = max(db_tbs_decute, db_each_tbs_decute) db_tbs_decute_reason = db_tbs_decute_reason + db_each_tbs_decute_reason # 临时表空间使用率扣分 tmp_tbs_stat = tools.mysql_query( "select host,port,service_name,tmp_tbs_name,total_mb,used_mb,pct_used from oracle_tmp_tbs where tags='%s'" % tags) if tmp_tbs_stat == 0: my_log.logger.warning('%s:临时表空间使用率未采集到数据' % tags) db_tmp_tbs_decute_reason = '' db_tmp_tbs_decute = 0 else: db_tmp_tbs_decute_reason = '' db_tmp_tbs_decute = 0 for each_tmp_tbs_stat in tmp_tbs_stat: each_tmp_tbs_name = each_tmp_tbs_stat[3] each_tmp_tbs_free = float(each_tmp_tbs_stat[4]) - float( each_tmp_tbs_stat[5]) each_tmp_tbs_used = float(each_tmp_tbs_stat[6]) db_each_tmp_tbs_decute = tools.get_decute_tmp_tbs( each_tmp_tbs_used, each_tmp_tbs_free) if db_each_tmp_tbs_decute <> 0: db_each_tmp_tbs_decute_reason = '%s临时表空间使用率:%d%% 剩余空间:%d \n' % ( each_tmp_tbs_name, each_tmp_tbs_used, each_tmp_tbs_free) else: db_each_tmp_tbs_decute_reason = '' db_tmp_tbs_decute = max(db_tmp_tbs_decute, db_each_tmp_tbs_decute) db_tmp_tbs_decute_reason = db_tmp_tbs_decute_reason + db_each_tmp_tbs_decute_reason # undo表空间使用率扣分 undo_tbs_stat = tools.mysql_query( "select host,port,service_name,undo_tbs_name,total_mb,used_mb,pct_used from oracle_undo_tbs where tags='%s'" % tags) if undo_tbs_stat == 0: my_log.logger.warning('%s:undo表空间使用率未采集到数据' % tags) db_undo_tbs_decute_reason = '' db_undo_tbs_decute = 0 else: db_undo_tbs_decute_reason = '' db_undo_tbs_decute = 0 for each_undo_tbs_stat in undo_tbs_stat: each_undo_tbs_name = each_undo_tbs_stat[3] each_undo_tbs_free = float(each_undo_tbs_stat[4]) - float( each_undo_tbs_stat[5]) each_undo_tbs_used = float(each_undo_tbs_stat[6]) db_each_undo_tbs_decute = tools.get_decute_undo_tbs( each_undo_tbs_used, each_undo_tbs_free) if db_each_undo_tbs_decute <> 0: db_each_undo_tbs_decute_reason = "'%s'undo表空间使用率:'%d'%% 剩余空间:%d \n" % ( each_undo_tbs_name, each_undo_tbs_used, each_undo_tbs_free) else: db_each_undo_tbs_decute_reason = '' db_undo_tbs_decute = max(db_undo_tbs_decute, db_each_undo_tbs_decute) db_undo_tbs_decute_reason = db_undo_tbs_decute_reason + db_each_undo_tbs_decute_reason db_top_decute = max(db_conn_decute, db_archive_decute, db_event_decute, db_tbs_decute, db_cpu_decute, db_mem_decute, db_tmp_tbs_decute, db_undo_tbs_decute) db_all_rate = 100 - db_top_decute if db_all_rate >= 60: db_rate_color = 'green' db_rate_level = 'success' elif db_all_rate >= 20 and db_all_rate < 60: db_rate_color = 'yellow' db_rate_level = 'warning' else: db_rate_color = 'red' db_rate_level = 'danger' db_all_decute_reason = db_conn_decute_reason + db_archive_decute_reason + db_event_decute_reason + db_tbs_decute_reason + db_cpu_decute_reason + db_mem_decute_reason + db_tmp_tbs_decute_reason + db_undo_tbs_decute_reason # 插入总评分及扣分明细 insert_sql = "insert into oracle_db_rate(tags,host,port,service_name,conn_decute,archive_decute,event_decute,tbs_decute,tmp_decute,undo_decute,cpu_decute,mem_decute,db_rate,db_rate_level,db_rate_color,db_rate_reason) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, db_conn_decute, db_archive_decute, db_event_decute, db_tbs_decute, db_tmp_tbs_decute, db_undo_tbs_decute, db_cpu_decute, db_mem_decute, db_all_rate, db_rate_level, db_rate_color, db_all_decute_reason) tools.mysql_exec(insert_sql, value) my_log.logger.info( '%s扣分明细,连接数扣分:%s,归档使用率扣分:%s, 等待事件扣分:%s, 表空间扣分:%s,临时表空间扣分:%s,Undo表空间扣分:%s,cpu使用率扣分:%s,内存使用率扣分:%s,总评分:%s,扣分原因:%s' % (tags, db_conn_decute, db_archive_decute_reason, db_event_decute_reason, db_tbs_decute, db_tmp_tbs_decute, db_undo_tbs_decute, db_cpu_decute, db_mem_decute, db_all_rate, db_all_decute_reason)) except Exception, e: error_msg = "%s 数据库连接失败:%s" % (tags, unicode(str(e), errors='ignore')) db_rate_level = 'red' my_log.logger.error(error_msg) my_log.logger.info('%s:初始化oracle_db表' % tags) insert_sql = "insert into oracle_db_his select * from oracle_db where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from oracle_db where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') error_sql = "insert into oracle_db(tags,host,port,service_name,mon_status,rate_level) values(%s,%s,%s,%s,%s,%s)" value = (tags, host, port, service_name, 'connected error', db_rate_level) tools.mysql_exec(error_sql, value) # 更新数据库打分信息 my_log.logger.info('%s :开始更新数据库评分信息' % tags) delete_sql = "delete from oracle_db_rate where tags= '%s'" % tags tools.mysql_exec(delete_sql, '') insert_sql = "insert into oracle_db_rate(tags,host,port,service_name,db_rate,db_rate_level,db_rate_color,db_rate_reason) select tags,host,port,service_name,'0','danger','red','connected error' from tab_oracle_servers where tags ='%s'" % tags tools.mysql_exec(insert_sql, '') my_log.logger.info('%s扣分明细,总评分:%s,扣分原因:%s' % (tags, '0', 'connected error'))
#! /usr/bin/python
def check_linux(tags, host, host_name, user, password): # 密钥解密 password = base64.decodestring(password) my_log.logger.info('%s:开始获取系统监控信息' % tags) try: my_log.logger.info('%s:初始化os_info表' % tags) insert_sql = "insert into os_info_his select * from os_info where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from os_info where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') # 发送网络流量,接收网络流量,cpu使用率 recv_kbps, send_kbps, cpu_used = check_os.os_get_info( host, user, password) # cpu使用率评级 cpu_rate_level = tools.get_rate_level(float(cpu_used)) mem_used = check_os.os_get_mem(host, user, password) # 内存使用率评级 mem_rate_level = tools.get_rate_level(float(mem_used)) # 主机状态评级 os_rate_level = 'green' insert_os_used_sql = 'insert into os_info(tags,host,host_name,recv_kbps,send_kbps,cpu_used,cpu_rate_level,mem_used,mem_rate_level,mon_status,rate_level) value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' value = (tags, host, host_name, recv_kbps, send_kbps, cpu_used, cpu_rate_level, mem_used, mem_rate_level, 'connected', os_rate_level) my_log.logger.info('%s:获取系统监控数据(CPU:%s MEM:%s)' % (tags, cpu_used, mem_used)) # print insert_cpu_used_sql tools.mysql_exec(insert_os_used_sql, value) my_log.logger.info('%s:开始获取文件系统监控信息' % tags) my_log.logger.info('%s:初始化os_filesystem表' % tags) insert_sql = "insert into os_filesystem_his select * from os_filesystem where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from os_filesystem where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') file_sys = check_os.os_get_disk(host, user, password) for i in xrange(len(file_sys)): disk_rate = float(file_sys[i]['used'].replace('%', '')) disk_rate_level = tools.get_rate_level( float(file_sys[i]['used'].replace('%', ''))) insert_file_sys_sql = "insert into os_filesystem(tags,host,host_name,filesystem_name,size,avail,pct_used,disk_rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, host, host_name, file_sys[i]['name'], file_sys[i]['size'], file_sys[i]['avail'], file_sys[i]['used'].replace('%', ''), disk_rate_level) my_log.logger.info( '%s:获取文件系统使用率(路径名:%s 使用率:%s)' % (tags, file_sys[i]['name'], file_sys[i]['used'])) tools.mysql_exec(insert_file_sys_sql, value) # 更新主机评分信息 my_log.logger.info('%s :开始更新Linux主机评分信息' % tags) delete_sql = "delete from linux_rate where tags= '%s'" % tags tools.mysql_exec(delete_sql, '') # 内存使用率扣分 linux_mem_decute_reason = '' mem_stat = tools.mysql_query( "select host,host_name,mem_used from os_info where mem_used is not null and host = '%s'" % host) if mem_stat == 0: my_log.logger.warning('%s:内存使用率未采集到数据' % host) linux_mem_decute = 0 else: linux_mem_used = float(mem_stat[0][2]) linux_mem_decute = tools.get_decute(linux_mem_used) if linux_mem_decute <> 0: linux_mem_decute_reason = '内存使用率:%d%% \n' % linux_mem_used else: linux_mem_decute_reason = '' # cpu使用率扣分 linux_cpu_decute_reason = '' cpu_stat = tools.mysql_query( "select host,host_name,cpu_used from os_info where cpu_used is not null and host = '%s'" % host) if cpu_stat == 0: my_log.logger.warning('%s:CPU使用率未采集到数据' % host) linux_cpu_decute = 0 else: linux_cpu_used = float(cpu_stat[0][2]) linux_cpu_decute = tools.get_decute(linux_cpu_used) if linux_cpu_decute <> 0: linux_cpu_decute_reason = 'CPU使用率:%d%% \n' % linux_cpu_used else: linux_cpu_decute_reason = '' linux_top_decute = max(linux_cpu_decute, linux_mem_decute) linux_all_rate = 100 - linux_top_decute if linux_all_rate >= 60: linux_rate_color = 'green' linux_rate_level = 'success' elif linux_all_rate >= 20 and linux_all_rate < 60: linux_rate_color = 'yellow' linux_rate_level = 'warning' else: linux_rate_color = 'red' linux_rate_level = 'danger' linux_all_decute_reason = linux_cpu_decute_reason + linux_mem_decute_reason # 插入总评分及扣分明细 insert_sql = "insert into linux_rate(host,tags,cpu_decute,mem_decute,linux_rate,linux_rate_level,linux_rate_color,linux_rate_reason) select host,tags,'%s','%s','%s','%s','%s','%s' from tab_linux_servers where tags ='%s'" % ( linux_cpu_decute, linux_mem_decute, linux_all_rate, linux_rate_level, linux_rate_color, linux_all_decute_reason, tags) tools.mysql_exec(insert_sql, '') my_log.logger.info('%s扣分明细,cpu使用率扣分:%s,内存使用率扣分:%s,总评分:%s,扣分原因:%s' % (tags, linux_cpu_decute, linux_mem_decute, linux_all_rate, linux_all_decute_reason)) except Exception, e: error_msg = "%s 目标主机连接失败:%s" % (tags, str(e)) os_rate_level = 'red' my_log.logger.error(error_msg) insert_sql = "insert into os_info_his select * from os_info where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from os_info where tags='%s'" % tags tools.mysql_exec(delete_sql, '') error_sql = "insert into os_info(tags,host,host_name,mon_status,rate_level) values(%s,%s,%s,%s,%s)" value = (tags, host, host_name, 'connected error', os_rate_level) tools.mysql_exec(error_sql, value) # 更新linux主机打分信息 my_log.logger.info('%s :开始更新linux主机评分信息' % tags) delete_sql = "delete from linux_rate where tags= '%s'" % tags tools.mysql_exec(delete_sql, '') insert_sql = "insert into linux_rate(host,tags,linux_rate,linux_rate_level,linux_rate_color,linux_rate_reason) select host,tags,'0','danger','red','connected error' from tab_linux_servers where tags ='%s'" % tags tools.mysql_exec(insert_sql, '') my_log.logger.info('%s扣分明细,总评分:%s,扣分原因:%s' % (tags, '0', 'connected error'))
def check_mysql(tags, host, port, user, password): my_log.logger.info('等待2秒待Linux主机信息采集完毕') time.sleep(2) password = base64.decodestring(password) try: conn = MySQLdb.connect(host=host, user=user, passwd=password, port=int(port), connect_timeout=5, charset='utf8') my_log.logger.info('%s:开始获取mysql数据库监控信息' % tags) # 归档历史监控数据 my_log.logger.info('%s:初始化mysql_db表' % tags) insert_sql = "insert into mysql_db_his select * from mysql_db where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from mysql_db where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') db_rate_level = 'green' # 获取两次状态值 my_log.logger.info('%s:获取第一次MySQL状态采样' % tags) mysql_stat = check_msql.get_mysql_status(conn) time.sleep(1) my_log.logger.info('%s:获取第二次MySQL状态采样' % tags) mysql_stat_next = check_msql.get_mysql_status(conn) # 基础信息 mysql_version = check_msql.get_mysql_para(conn, 'version') mysql_uptime = float(mysql_stat['Uptime']) / 86400 # 连接信息 mysql_max_connections = check_msql.get_mysql_para( conn, 'max_connections') current_conn = mysql_stat['Threads_connected'] threads_running = mysql_stat['Threads_running'] threads_created = mysql_stat['Threads_created'] threads_cached = mysql_stat['Threads_cached'] threads_waited = int(check_msql.get_mysql_waits(conn)) mysql_conn_rate = "%2.2f" % (float(current_conn) / float(mysql_max_connections)) # QPS,TPS mysql_qps = int(mysql_stat_next['Questions']) - int( mysql_stat['Questions']) mysql_tps = int(mysql_stat_next['Com_commit']) - int( mysql_stat['Com_commit']) # 流量 mysql_bytes_received = (int(mysql_stat_next['Bytes_received']) - int(mysql_stat['Bytes_received'])) / 1024 mysql_bytes_sent = (int(mysql_stat_next['Bytes_sent']) - int(mysql_stat['Bytes_sent'])) / 1024 # 连接数评级 conn_rate_level = tools.get_rate_level(float(mysql_conn_rate)) insert_db_sql = "insert into mysql_db(host,port,tags,version,uptime,max_connections,threads_connected,threads_running,threads_created,threads_cached,threads_waited,conn_rate,conn_rate_level,QPS,TPS,bytes_received,bytes_send,mon_status,rate_level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (host, port, tags, mysql_version, mysql_uptime, mysql_max_connections, current_conn, threads_running, threads_created, threads_cached, threads_waited, mysql_conn_rate, conn_rate_level, mysql_qps, mysql_tps, mysql_bytes_received, mysql_bytes_sent, 'connected', 'green') tools.mysql_exec(insert_db_sql, value) my_log.logger.info( '%s:获取Mysql数据库监控数据(IP:%s 端口号:%s 连接使用率:%s 连接状态:%s )' % (tags, host, port, mysql_conn_rate, 'connected')) # 复制 # 初始化mysql_repl表 my_log.logger.info('%s:初始化mysql_repl表' % tags) insert_sql = "insert into mysql_repl_his select * from mysql_repl where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from mysql_repl where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') server_id = check_msql.get_mysql_para(conn, 'server_id') is_slave = '' is_master = '' read_only_result = '' master_server = '' master_port = '' slave_io_run = '' slave_io_rate = '' slave_sql_run = '' slave_sql_rate = '' delay = '-' delay_rate = '' current_binlog_file = '' current_binlog_pos = '' master_binlog_file = '' master_binlog_pos = '' master_binlog_space = '' curs = conn.cursor() master_thread = curs.execute( "select * from information_schema.processlist where COMMAND = 'Binlog Dump'" ) slave_stats = curs.execute('show slave status;') # 判断Mysql角色 if master_thread: is_master = 'YES' if slave_stats: is_slave = 'YES' mysql_role = '' if is_master == 'YES' and is_slave <> 'YES': mysql_role = 'master' if is_master <> 'YES' and is_slave == 'YES': mysql_role = 'slave' if is_master == 'YES' and is_slave == 'YES': mysql_role = 'master/slave' if slave_stats: read_only = curs.execute( "select * from information_schema.global_variables where variable_name='read_only';" ) read_only_query = curs.fetchone() read_only_result = read_only_query[1] slave_info = curs.execute("show slave status;") slave_result = curs.fetchone() master_server = slave_result[1] master_port = slave_result[3] slave_io_run = slave_result[10] if slave_io_run == 'Yes': slave_io_rate = 'green' else: slave_io_rate = 'red' slave_sql_run = slave_result[11] if slave_sql_run == 'Yes': slave_sql_rate = 'green' else: slave_sql_rate = 'red' delay = slave_result[32] if delay is None: delay_rate = 'red' else: if int(delay) == 0: delay_rate = 'green' elif int(delay) > 0 and int(delay) < 300: delay_rate = 'yellow' else: delay_rate = 'red' current_binlog_file = slave_result[9] current_binlog_pos = slave_result[21] master_binlog_file = slave_result[5] master_binlog_pos = slave_result[6] elif master_thread: read_only = curs.execute( "select * from information_schema.global_variables where variable_name='read_only';" ) read_only_query = curs.fetchone() read_only_result = read_only_query[1] master_info = curs.execute('show master status;') master_result = curs.fetchone() master_binlog_file = master_result[0] master_binlog_pos = master_result[1] if master_thread: binlog_file = curs.execute('show master logs;') binlogs = 0 if binlog_file: for row in curs.fetchall(): binlogs = binlogs + row[1] master_binlog_space = int(binlogs) / 1024 / 1024 insert_repl_sql = "insert into mysql_repl(tags,server_id,host,port,is_master,is_slave,mysql_role,read_only,master_server,master_port,slave_io_run,slave_io_rate,slave_sql_run,slave_sql_rate,delay,delay_rate,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,master_binlog_space) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" value = (tags, server_id, host, port, is_master, is_slave, mysql_role, read_only_result, master_server, master_port, slave_io_run, slave_io_rate, slave_sql_run, slave_sql_rate, delay, delay_rate, current_binlog_file, current_binlog_pos, master_binlog_file, master_binlog_pos, master_binlog_space) tools.mysql_exec(insert_repl_sql, value) my_log.logger.info('%s:获取Mysql数据库复制数据' % tags) # 更新数据库打分信息 my_log.logger.info('%s :开始更新Mysql数据库评分信息' % tags) delete_sql = "delete from mysql_db_rate where tags= '%s'" % tags tools.mysql_exec(delete_sql, '') # 内存使用率扣分 db_mem_decute = 0 db_mem_decute_reason = '' mem_stat = tools.mysql_query( "select host,host_name,mem_used from os_info where mem_used is not null and host = '%s'" % host) if mem_stat == 0: my_log.logger.warning('%s:内存使用率未采集到数据' % host) db_mem_decute = 0 else: db_mem_used = float(mem_stat[0][2]) db_mem_decute = tools.get_decute(db_mem_used) if db_mem_decute <> 0: db_mem_decute_reason = '内存使用率:%d%% \n' % db_mem_used else: db_mem_decute_reason = '' # cpu使用率扣分 db_cpu_decute_reason = '' cpu_stat = tools.mysql_query( "select host,host_name,cpu_used from os_info where cpu_used is not null and host = '%s'" % host) if cpu_stat == 0: my_log.logger.warning('%s:CPU使用率未采集到数据' % host) db_cpu_decute = 0 else: db_cpu_used = float(cpu_stat[0][2]) db_cpu_decute = tools.get_decute(db_cpu_used) if db_cpu_decute <> 0: db_cpu_decute_reason = 'CPU使用率:%d%% \n' % db_cpu_used else: db_cpu_decute_reason = '' # 连接数扣分 db_conn_decute = 0 db_conn_decute_reason = '' db_conn_decute = tools.get_decute(float(mysql_conn_rate)) if db_conn_decute <> 0: db_conn_decute_reason = '连接数:%d%% \n' % mysql_conn_rate else: db_conn_decute_reason = '' db_top_decute = max(db_conn_decute, db_cpu_decute, db_mem_decute) db_all_rate = 100 - db_top_decute if db_all_rate >= 60: db_rate_color = 'green' db_rate_level = 'success' elif db_all_rate >= 20 and db_all_rate < 60: db_rate_color = 'yellow' db_rate_level = 'warning' else: db_rate_color = 'red' db_rate_level = 'danger' db_all_decute_reason = db_conn_decute_reason + db_cpu_decute_reason + db_mem_decute_reason # 插入总评分及扣分明细 insert_sql = "insert into mysql_db_rate(host,port,tags,conn_decute,cpu_decute,mem_decute,db_rate,db_rate_level,db_rate_color,db_rate_reason) select host,port,tags,'%s','%s','%s','%s','%s','%s','%s' from tab_mysql_servers where tags ='%s'" % ( db_conn_decute, db_cpu_decute, db_mem_decute, db_all_rate, db_rate_level, db_rate_color, db_all_decute_reason, tags) tools.mysql_exec(insert_sql, '') my_log.logger.info( '%s扣分明细,连接数扣分:%s,cpu使用率扣分:%s,内存使用率扣分:%s,总评分:%s,扣分原因:%s' % (tags, db_conn_decute, db_cpu_decute, db_mem_decute, db_all_rate, db_all_decute_reason)) except Exception, e: error_msg = "%s mysql数据库连接失败:%s" % (tags, unicode(str(e), errors='ignore')) db_rate_level = 'red' my_log.logger.error(error_msg) my_log.logger.info('%s:初始化mysql_db表' % tags) insert_sql = "insert into mysql_db_his select * from mysql_db where tags = '%s'" % tags tools.mysql_exec(insert_sql, '') delete_sql = "delete from mysql_db where tags = '%s'" % tags tools.mysql_exec(delete_sql, '') error_sql = "insert into mysql_db(host,port,tags,mon_status,rate_level) values(%s,%s,%s,%s,%s)" value = (host, port, tags, 'connected error', db_rate_level) tools.mysql_exec(error_sql, value) # 更新数据库打分信息 my_log.logger.info('%s :开始更新数据库评分信息' % tags) delete_sql = "delete from mysql_db_rate where tags= '%s'" % tags tools.mysql_exec(delete_sql, '') insert_sql = "insert into mysql_db_rate(host,port,tags,db_rate,db_rate_level,db_rate_color,db_rate_reason) select host,port,tags,'0','danger','red','connected error' from tab_mysql_servers where tags ='%s'" % tags tools.mysql_exec(insert_sql, '') my_log.logger.info('%s扣分明细,总评分:%s,扣分原因:%s' % (tags, '0', 'conected error'))
def go_start(): log_type = '程序启停' tools.mysql_exec("delete from many_logs where log_type = '程序启停'",'') starts = tools.mysql_query( "select oper_type,app_name,host,user,password,name,do_cmd,process_check,check_log,id from easy_start_conf order by id") for start in starts: oper_type = start[0] app_name = start[1] host = start[2] user = start[3] password = base64.decodestring(start[4]) name = start[5] do = start[6] process_check = start[7] check_log = start[8] id = start[9] # 关闭程序时如执行命令为空,则选择杀进程方式 if not do and oper_type == 'shutdown': cmd_do = 'ps -ef | grep %s| grep -v "grep" | cut -c 9-15 | xargs kill -s 9' % process_check exec_log = '' # 启动或关闭命令 if oper_type == 'startup': exec_log = '/tmp/%s.log' % name cmd_do = "%s > %s 2>&1 &" % (do, exec_log) # 检查命令 cmd_check = 'ps -ef|grep %s |grep -v "grep"' % process_check # 异常日志 if not check_log: check_log = exec_log error_check = 'tail -100 %s' % check_log tools.my_log(log_type,'跳转至%s,对%s执行%s' %(host,name,oper_type),'') # 执行关闭或启动命令 exec_command(host,user,password,cmd_do) if oper_type == 'shutdown': # 进程数为0,关闭成功 if len(exec_command(host,user,password,cmd_check)) == 0: tools.my_log(log_type, '%s:%s成功!' % (name,oper_type),'') upd_sql = "update easy_start_conf set process_check_result='green' where id = %s" %id tools.mysql_exec(upd_sql,'') else: tools.my_log(log_type, '%s:%s失败!' % (name, oper_type),'') upd_sql = "update easy_start_conf set process_check_result='red' where id = %s" %id tools.mysql_exec(upd_sql,'') break elif oper_type == 'startup': # 进程数为0,启动失败 if len(exec_command(host,user,password,cmd_check)) == 0: # 更新启停状态 upd_sql = "update easy_start_conf set process_check_result='red' where id = %s" % id tools.mysql_exec(upd_sql, '') # 异常日志 err_info = exec_command(host,user,password,error_check) err_info_txt = '' for i in err_info: err_info_txt = err_info_txt + i tools.my_log(log_type, '%s:%s失败!' % (name, oper_type),err_info_txt) break else: upd_sql = "update easy_start_conf set process_check_result='green' where id = %s" % id tools.mysql_exec(upd_sql, '') err_info = exec_command(host,user,password,error_check) err_info_txt = '' for i in err_info: err_info_txt = err_info_txt + i if 'err' in err_info_txt: upd_sql = "update easy_start_conf set check_log_result='red' where id = %s" % id tools.mysql_exec(upd_sql, '') tools.my_log(log_type, '%s:%s失败!' % (name, oper_type), err_info_txt) else: tools.my_log(log_type, '%s:%s成功!' % (name, oper_type),'') upd_sql = "update easy_start_conf set check_log_result='green' where id = %s" % id tools.mysql_exec(upd_sql, '')