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, '')
