Mysql NAgios Log

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

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

CREATE TABLE nm (
  date int(10),
  host_name varchar(128),
  check_name varchar(128),
  check_status varchar(128),
  f1 varchar(128),
  f2 varchar(128),
  f3 varchar(128),
  f4 varchar(128),
  f5 varchar(128)
);

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"\");" }'

1

DROP PROCEDURE GetAllerts;
delimiter // 
CREATE PROCEDURE GetAllerts
BEGIN 
    DECLARE cursor_alert CURSOR FOR  SELECT date,host_name,check_name,check_status FROM nm ORDER BY host_name,check_name,date;
    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_date VARCHAR(255);
    DECLARE prev_date VARCHAR(255);

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 
--
    WHILE done = 0 DO 
          IF ( current_host_name == prev_host AND  current_check_name == prev_check_name AND current_check_status == prev_check_status)
          THEN
             DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status  AND date=current_date;
          END IF
          prev_host = current_host_name;
          prev_check_name = current_check_name;
          prev_check_status = current_check_status;
          
          FETCH  cursor_alert INTO into current_date, current_host_name,
   
    END WHILE; 
    CLOSE cursor_ip; 
END 
// 
delimiter ; 

Это Просто пример

DROP FUNCTION GetAllPoolsForNet; 
delimiter // 
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT 
BEGIN 
    DECLARE done INT DEFAULT 0; 
    DECLARE mask1 int UNSIGNED; 
    DECLARE mac_dhcp1 VARCHAR(255); 
    DECLARE current_ip int UNSIGNED DEFAULT 0; 
    DECLARE pools MEDIUMTEXT DEFAULT " "; 
    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATOn(ip_dhcp),mac_dhcp from mtraf_status  WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n1; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 
    SELECT mask from network_mask WHERE network=n1 INTO mask1; 
    OPEN cursor_ip; 
    FETCH cursor_ip INTO current_ip,mac_dhcp1; 
    WHILE done = 0 DO 
        SELECT  CONCAT( 
            "pool { range ", 
            INET_NTOA(current_ip), 
            "; allow members of \"", 
            INET_NTOA(current_ip), 
            "__", 
            mac_dhcp1, 
            "\"; default-lease-time 36000; max-lease-time 72000; } " 
            ,"  \n",pools) INTO pools; 
        FETCH  cursor_ip INTO current_ip,mac_dhcp1; 
    END WHILE; 
    CLOSE cursor_ip; 
    RETURN pools; 
END 
// 
delimiter ;