Mysql NAgios Log: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
 
(не показано 27 промежуточных версий этого же участника)
Строка 1: Строка 1:
  +
[[Категория:Linux]]
  +
[[Категория:Nagios]]
  +
[[Категория:MySQL]]
  +
[[Категория:GeneralSQL]]
 
=Анализ логов нагиоса=
  
=Анализ логов нагиоса=
 
==Загрузить логи в Mysql==
  
==Загрузить логи в Mysql==
 
<PRE>
  
<PRE>
−
CREATE TABLE nm (
 +
CREATE TABLE `nm` (
−
date int(10),
 +
`date` int(10) DEFAULT NULL,
−
host_name varchar(128),
 +
`host_name` varchar(128) DEFAULT NULL,
−
check_name varchar(128),
 +
`check_name` varchar(128) DEFAULT NULL,
−
check_status varchar(128),
 +
`check_status` varchar(128) DEFAULT NULL,
−
f1 varchar(128),
 +
`f1` varchar(128) DEFAULT NULL,
−
f2 varchar(128),
 +
`f2` varchar(128) DEFAULT NULL,
−
f3 varchar(128),
 +
`f3` varchar(128) DEFAULT NULL,
−
f4 varchar(128),
 +
`f4` varchar(128) DEFAULT NULL,
−
f5 varchar(128)
 +
`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
 
</PRE>
  
</PRE>
   
Строка 24: Строка 32:
 
</PRE>
  
</PRE>
   
  +
==Удалить лишние записи==
−
==1==
  
  +
Очень просто - удалить запись если она "такая же" как предыдущяя
 
<PRE>
  
<PRE>
−
DROP FUNCTION GetAllerts;
 +
DROP PROCEDURE GetAllerts;
 
delimiter //
 
delimiter //
−
CREATE FUNCTION GetAllerts;
 +
CREATE PROCEDURE GetAllerts()
−
BEGIN
 
⚫
DECLARE cursor_alert CURSOR FOR SELECT date host_name check_name check_status FROM nm
  
−  
−
END
 
−
//
 
−
delimiter ;
 
−  
−
</PRE>
  
−  
−
==Это Просто пример==
  
−
<PRE>
  
−
DROP FUNCTION GetAllPoolsForNet;
 
−
delimiter //
 
−
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS MEDIUMTEXT
 
 
BEGIN
 
BEGIN
 
DECLARE done INT DEFAULT 0;
 
DECLARE done INT DEFAULT 0;
−
DECLARE mask1 int UNSIGNED;
+
DECLARE current_host_name VARCHAR(255);
−
DECLARE mac_dhcp1 VARCHAR(255);
+
DECLARE current_check_name VARCHAR(255);
−
DECLARE current_ip int UNSIGNED DEFAULT 0;
+
DECLARE current_check_status VARCHAR(255);
−
DECLARE pools MEDIUMTEXT DEFAULT " ";
+
DECLARE prev_host_name VARCHAR(255);
  +
DECLARE prev_check_name VARCHAR(255);
−
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 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;
 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
  +
−
SELECT mask from network_mask WHERE network=n1 INTO mask1;
 
−
OPEN cursor_ip;
+
SET prev_host_name = '';
  +
SET prev_check_name = '';
−
FETCH cursor_ip INTO current_ip,mac_dhcp1;
 
  +
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
 
WHILE done = 0 DO
  +
-- SELECT current_date1, current_host_name, current_check_name, current_check_status;
⚫
SELECT CONCAT(
 
  +
-- SELECT prev_date, prev_host_name, prev_check_name, prev_check_status;
−
"pool { range ",
 
  +
IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = prev_check_status))
−
INET_NTOA(current_ip),
 
−
"; allow members of \"",
+
THEN
  +
-- SELECT "DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date=current_date1;";
−
INET_NTOA(current_ip),
 
  +
DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date=current_date1;
−
"__",
 
−
mac_dhcp1,
+
END IF;
  +
SET prev_host_name = current_host_name;
−
"\"; default-lease-time 36000; max-lease-time 72000; } "
 
−
," \n",pools) INTO pools;
+
SET prev_check_name = current_check_name;
  +
SET prev_check_status = current_check_status;
−
FETCH cursor_ip INTO current_ip,mac_dhcp1;
 
  ⚫
  +
FETCH cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status;
  +
 
END WHILE;
 
END WHILE;
−
CLOSE cursor_ip;
+
CLOSE cursor_alert;
−
RETURN pools;
 
 
END
 
END
 
//
 
//
 
delimiter ;
 
delimiter ;
  +
 
</PRE>
  
</PRE>

Текущая версия на 15:39, 28 сентября 2012

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

Загрузить логи в 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 ;
Источник — https://noname.com.ua/mediawiki/index.php?title=Mysql_NAgios_Log&oldid=5731