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