def create_table_salarm(): ''' #创建新的osa_collect_alarm 表 ''' sql = "CREATE TABLE `osa_collect_alarm`(`id` int(20) NOT NULL AUTO_INCREMENT,`oIpid` int(20) NOT NULL,`oAlarmText` text,`oAlarmTime` timestamp NULL DEFAULT NULL,`oAlarmLevel` tinyint(4) DEFAULT NULL,`oAlarmType` varchar(50) DEFAULT NULL,`oSnapShot` varchar(200) DEFAULT NULL,`oIsNotice` tinyint(4) DEFAULT '0',`oIsRead` tinyint(4) DEFAULT '0',`oFaultTime` int(20) DEFAULT NULL,`oIsNoticeNext` tinyint(4) DEFAULT '0',`oNoticeNextTime` timestamp NULL DEFAULT NULL,`oNoticeNextUsers` varchar(500) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8" cmdtosql.execsql(sql)
def create_table_srecord(): ''' #创建新的osa_collect_data表 ''' sql = "CREATE TABLE `osa_collect_data` (`id` int(20) NOT NULL AUTO_INCREMENT,`oIpid` int(20) NOT NULL,`oCollectTime` timestamp NULL DEFAULT NULL,`oCollectData` varchar(5000) DEFAULT NULL,`oReplayTime` double DEFAULT NULL,`oStatus` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8" cmdtosql.execsql(sql)
def create_table_mrecord(): ''' #创建新的osa_monitor_record表 ''' sql = "CREATE TABLE `osa_monitor_record` (`id` int(20) NOT NULL AUTO_INCREMENT,`oItemid` int(20) NOT NULL,`oMonTime` timestamp NULL DEFAULT NULL,`oMonResult` text,`oReplayTime` double DEFAULT NULL,`oStatus` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8" cmdtosql.execsql(sql)
def insert_collectalarm_exception(isNoticeNow,isNoticeNext,noticeNextUsers,noticeNextTime,ipinfo,alarminfo):
'''
@服务器异常通知根据通知方式来更新osa_collect_alarm表
'''
try:
isNotice = 0
alarmTime = cmdtosql._get_time(1)
##再添加一个服务器故障时间
startTime = str(ipinfo[5])
#计算服务器故障时间，并写入表osa_collect_alarm
#faultTime = time.time()-time.mktime(time.strptime(startTime,"%Y-%m-%d %H:%M:%S"))
if isNoticeNow == 0 and isNoticeNext == 0:
return False
else:
if isNoticeNow > 0:
isNotice =1
if isNoticeNext > 0:
notice_sql = "insert into osa_collect_alarm(oIpid,oAlarmText,oAlarmTime,oAlarmLevel,oAlarmType,oIsNotice,oIsRead,oIsNoticeNext,oNoticeNextTime,oNoticeNextUsers) \
values("+str(ipinfo[0])+",'"+str(alarminfo['text'])+"','"+str(alarmTime)+"','"+str(alarminfo['level'])+"','server',"+str(isNotice)+",0,1,"+str(noticeNextTime)+",'"+str(noticeNextUsers.strip(','))+"')"
else:
notice_sql = "insert into osa_collect_alarm(oIpid,oAlarmText,oAlarmTime,oAlarmLevel,oAlarmType,oIsNotice,oIsRead) \
values("+str(ipinfo[0])+",'"+str(alarminfo['text'])+"','"+str(alarmTime)+"','"+str(alarminfo['level'])+"','server',"+str(isNotice)+",0)"
log_debug("insert_collectalarm_exception()执行sql语句:"+str(notice_sql))
cmdtosql.execsql(notice_sql)
except Exception as e:
log_error("insert_collectalarm_exception():"+str(e))
def is_notice_item(iteminfo,username,maxnum,alarmlevel):
'''
@针对监控项目告警信息，整体判断是否要发送邮件
@需要的信息必须有：已发送告警次数notifiednum,连续告警次数notinum,恢复的时候清零
@return 三种类型，'not-send':不发送，'now-send':现在发送，时间字符串：表示转为下次发送时间
'''
notifiednum = iteminfo[19]
notinum = iteminfo[20]
repeatednum = iteminfo[21]
alarmlevel = str(alarmlevel)
#根据是否为恢复通知判断报警通知次数
if alarmlevel == '4':
if iteminfo[7] == '0' or iteminfo[22] == '0':#iteminfo[7] == '0'表示恢复时不通知
return 'not-send'
else:
if notinum >= iteminfo[5]:
return 'not-send'
#针对已重复次数是否已达到设置的值
if repeatednum < iteminfo[6]:
num = int(repeatednum) + 1
sql = "update osa_monitors set oRepeatedNum = "+str(num)+" where id="+str(iteminfo[0])
cmdtosql.execsql(sql)
return 'not-send'
else:#重置已重复次数
sql = "update osa_monitors set oRepeatedNum = 0 where id="+str(iteminfo[0])
cmdtosql.execsql(sql)
#判断项目发送的告警次数是否大于针对每个项目的报警次数限制
if notifiednum >= maxnum:
return 'not-send'
return notice_email_personality(username,iteminfo[3],alarmlevel,iteminfo[2])
def osa_manage_table(m_alarm,m_record,s_alarm,s_record):
'''
#insert into osa_manage_table
'''
now = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime());
sql = "insert into osa_table_manage values(null,'"+m_alarm+"','"+now+"','malarm'),(null,'"+m_record+"','"+now+"','mdata'),(null,'"+s_alarm+"','"+now+"','salarm'),(null,'"+s_record+"','"+now+"','sdata')"
#print(sql)
cmdtosql.execsql(sql)
def monitor_insert_itemdata(itemid,result='',replayTime=0,status='正常'):
'''
@osaMonitor 插入osa_monitor_record记录
@默认数据：恢复正常数据
'''
try:
montime=cmdtosql._get_time(1)
sql = "insert into osa_monitor_record(oItemid,oMonTime,oMonResult,oReplayTime,oStatus) values("+str(itemid)+",'"+str(montime)+"','"+str(result)+"','"+str(replayTime)+"','"+str(status)+"')"
log_debug("monitor_insert_itemdata()执行sql语句:"+str(sql))
cmdtosql.execsql(sql)
except Exception as e:
log_error("monitor_insert_itemdata():"+str(e))
def insert_collectdata_exception(ipid,result,replayTime=0):
'''
@服务器异常通知，插入osa_collect_data记录
'''
try:
result = result.replace("'","")
collectTime=cmdtosql._get_time(1)
snmp_sql = "insert into osa_collect_data(oIpid,oCollectTime,oCollectData,oReplayTime,oStatus) values("+str(ipid)+",'"+str(collectTime)+"','"+str(result)+"','"+str(replayTime)+"','异常')"
log_debug("insert_collectdata_exception()执行sql语句:"+str(snmp_sql))
cmdtosql.execsql(snmp_sql)
except Exception as e:
log_error("insert_collectdata_exception():"+str(e))
def insert_collectdata_remind(ipid,result,replayTime=0):
'''
@服务器恢复通知,插入osa_collect_data记录
'''
try:
collectTime=cmdtosql._get_time(1)
snmp_sql = "insert into osa_collect_data(oIpid,oCollectTime,oCollectData,oReplayTime,oStatus) values("+str(ipid)+",'"+str(collectTime)+"','"+str(result)+"','"+str(replayTime)+"','正常')"
log_debug("insert_collectdata_remind()执行sql语句:"+str(snmp_sql))
cmdtosql.execsql(snmp_sql)
except Exception as e:
log_debug("insert_collectdata_remind()执行sql语句:"+str(snmp_sql))
log_error("insert_collectdata_remind():"+str(e))
def osa_rename_table(): ''' @对需要分表的表进行重命名 ''' suffix = compute_table_suffix() if suffix == False: return False else: m_alarm = "osa_monitor_alarm_"+str(suffix) m_record = "osa_monitor_record_"+str(suffix) s_alarm = "osa_collect_alarm_"+str(suffix) s_record = "osa_collect_data_"+str(suffix) sql = "rename table osa_monitor_record to "+str(m_record)+",osa_monitor_alarm to "+str(m_alarm)+",osa_collect_alarm to "+str(s_alarm)+",osa_collect_data to "+str(s_record) cmdtosql.execsql(sql) osa_manage_table(m_alarm,m_record,s_alarm,s_record) osa_create_table()
def insert_itemalarm_except(isNoticeNow,isNoticeNext,noticeNextUsers,noticeNextTime,iteminfo,alarminfo):
'''
@osaMonitor 发生异常时更新osa_monitor_alarm表
'''
try:
isNotice = 0
alarmTime = cmdtosql._get_time(1)
if isNoticeNow == 0 and isNoticeNext == 0:
return False
else:
if isNoticeNow > 0:
isNotice =1
if isNoticeNext > 0:
notice_sql = "insert into osa_monitor_alarm(oItemid,oMonName,oAlarmText,oAlarmTime,oAlarmLevel,oAlarmType,oIsNotice,oIsRead,oIsNoticeNext,oNoticeNextTime,oNoticeNextUsers) \
values("+str(iteminfo[0])+",'"+str(iteminfo[1])+"','"+str(alarminfo['text'])+"','"+str(alarmTime)+"','"+str(alarminfo['level'])+"','"+str(alarminfo['type'])+"',"+str(isNotice)+",0,1,"+str(noticeNextTime)+",'"+str(noticeNextUsers.strip(','))+"')"
else:
notice_sql = "insert into osa_monitor_alarm(oItemid,oMonName,oAlarmText,oAlarmTime,oAlarmLevel,oAlarmType,oIsNotice,oIsRead) \
values("+str(iteminfo[0])+",'"+str(iteminfo[1])+"','"+str(alarminfo['text'])+"','"+str(alarmTime)+"','"+str(alarminfo['level'])+"','"+str(alarminfo['type'])+"',"+str(isNotice)+",0)"
log_debug("insert_itemalarm_except()执行sql语句:"+str(notice_sql))
cmdtosql.execsql(notice_sql)
except Exception as e:
log_error("insert_itemalarm_except():"+str(e))
def insert_itemalarm_remind(isNoticeNow,isNoticeNext,noticeNextUsers,noticeNextTime,iteminfo,type):
'''
@osaMonitor 恢复通知根据通知方式来更新osa_monitor_alarm表
'''
try:
isNotice = 0
if isNoticeNow == 0 and isNoticeNext == 0 and iteminfo[22] == str(0):
return False
else:
alarmTime = cmdtosql._get_time(1)
itemid = iteminfo[0]
reason = monitor_get_lastReason(itemid)
ntext = '项目恢复正常'
if reason == '' or reason == '项目恢复正常':
ntext = '不稳定'
##再添加一个服务器故障时间
startTime = str(iteminfo[18])
#计算服务器故障时间，并写入表osa_monitor_alarm
if startTime == 'None' or startTime == '':
startTime = cmdtosql._get_time(1)
faultTime = time.time()-time.mktime(time.strptime(startTime,"%Y-%m-%d %H:%M:%S"))
if isNoticeNow > 0:
isNotice =1
if isNoticeNext > 0:
notice_sql = "insert into osa_monitor_alarm(oItemid,oMonName,oAlarmText,oAlarmTime,oAlarmLevel,oAlarmType,oIsNotice,oIsRead,oFaultTime,oIsNoticeNext,oNoticeNextTime,oNoticeNextUsers) \
values("+str(iteminfo[0])+",'"+str(iteminfo[1])+"','"+str(ntext)+"','"+str(alarmTime)+"','4','"+str(type)+"',"+str(isNotice)+",0,"+str(faultTime)+",1,"+str(noticeNextTime)+",'"+str(noticeNextUsers.strip(','))+"')"
else:
notice_sql = "insert into osa_monitor_alarm(oItemid,oMonName,oAlarmText,oAlarmTime,oAlarmLevel,oAlarmType,oIsNotice,oIsRead,oFaultTime) \
values("+str(iteminfo[0])+",'"+str(iteminfo[1])+"','"+str(ntext)+"','"+str(alarmTime)+"','4','"+str(type)+"',"+str(isNotice)+",0,"+str(faultTime)+")"
log_debug("insert_itemalarm_remind()执行sql语句:"+str(notice_sql))
cmdtosql.execsql(notice_sql)
except Exception as e:
log_error("insert_itemalarm_remind():"+str(e))
