Mysql NAgios Log: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) (→1) |
Sirmax (обсуждение | вклад) |
||
(не показано 18 промежуточных версий этого же участника) | |||
Строка 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 PROCEDURE GetAllerts; |
DROP PROCEDURE GetAllerts; |
||
Строка 37: | Строка 46: | ||
DECLARE prev_check_name VARCHAR(255); |
DECLARE prev_check_name VARCHAR(255); |
||
DECLARE prev_check_status VARCHAR(255); |
DECLARE prev_check_status VARCHAR(255); |
||
− | DECLARE |
+ | DECLARE current_date1 INT(10); |
− | DECLARE prev_date |
+ | 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 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; |
||
+ | |||
+ | SET prev_host_name = ''; |
||
+ | SET prev_check_name = ''; |
||
+ | SET prev_check_status = ''; |
||
+ | -- SELECT "This is my stored procedure"; |
||
⚫ | |||
+ | 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 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 |
THEN |
||
− | DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date= |
+ | -- 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 |
||
− | + | 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 |
+ | FETCH cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status; |
END WHILE; |
END WHILE; |
||
− | CLOSE |
+ | CLOSE cursor_alert; |
END |
END |
||
// |
// |
||
delimiter ; |
delimiter ; |
||
− | </PRE> |
||
− | |||
− | ==Это Просто пример== |
||
− | <PRE> |
||
− | 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; |
||
⚫ | |||
− | 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 ; |
||
</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 ;