Mysql NAgios Log

Анализ логов нагиоса

Загрузить логи в Mysql

 CREATE TABLE `nm` (
  `date` int(10) DEFAULT NULL,
  `host_name` varchar(128) DEFAULT NULL,
  `check_name` varchar(128) DEFAULT NULL,
  `check_status` varchar(128) DEFAULT NULL,
  `f1` varchar(128) DEFAULT NULL,
  `f2` varchar(128) DEFAULT NULL,
  `f3` varchar(128) DEFAULT NULL,
  `f4` varchar(128) DEFAULT NULL,
  `f5` varchar(128) DEFAULT NULL,
  KEY `host_name` (`host_name`),
  KEY `check_name` (`check_name`),
  KEY `check_status` (`check_status`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

mkfifo mysql.fifo
tail -f ./mysql.fifo | mysql mydatabase -uUSER -pPASSWORD

tail spd_hy-netrac-p1.core.sdn.telia.se.log  | grep -v ^$ | sed s/"\t"/"@@"/g | awk -F"@@" '{print "INSERT INTO nm VALUES (\"" $1"\", \""$2"\",\""$3"\",\""$4"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$9"\");" }'

Удалить лишние записи

Очень просто - удалить запись если она "такая же" как предыдущяя

DROP PROCEDURE GetAllerts;
delimiter // 
CREATE PROCEDURE GetAllerts()
BEGIN 
    DECLARE done INT DEFAULT 0; 
    DECLARE current_host_name VARCHAR(255);
    DECLARE current_check_name VARCHAR(255);
    DECLARE current_check_status VARCHAR(255);
    DECLARE prev_host_name VARCHAR(255);
    DECLARE prev_check_name VARCHAR(255);
    DECLARE prev_check_status VARCHAR(255);
    DECLARE current_date1 INT(10);
    DECLARE prev_date INT(10);
    DECLARE cursor_alert CURSOR FOR  SELECT date,host_name,check_name,check_status FROM nm ORDER BY host_name,check_name,date;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 

    SET prev_host_name = '';
    SET prev_check_name = '';
    SET prev_check_status = '';
--    SELECT "This is my stored procedure";
    OPEN cursor_alert; 
    FETCH  cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status;
    WHILE done = 0 DO 
--          SELECT current_date1, current_host_name, current_check_name, current_check_status;
--          SELECT prev_date,     prev_host_name,    prev_check_name,    prev_check_status;
          IF ( (current_host_name = prev_host_name ) AND  ( current_check_name = prev_check_name ) AND  ( current_check_status = prev_check_status))
          THEN
--              SELECT "DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status  AND date=current_date1;";
              DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status  AND date=current_date1;
          END IF;
          SET prev_host_name = current_host_name;
          SET prev_check_name = current_check_name;
          SET prev_check_status = current_check_status;
          
          FETCH  cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status;
   
    END WHILE; 
    CLOSE cursor_alert; 
END 
// 
delimiter ;