Mysql NAgios Log: различия между версиями
Материал из noname.com.ua
Перейти к навигацииПерейти к поискуSirmax (обсуждение | вклад) (→1) |
Sirmax (обсуждение | вклад) (→1) |
||
Строка 47: | Строка 47: | ||
-- |
-- |
||
WHILE done = 0 DO |
WHILE done = 0 DO |
||
− | IF ( current_host_name = prev_host AND current_check_name = prev_check_name) |
+ | IF ( current_host_name == prev_host 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=current_date; |
DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date=current_date; |
||
END IF |
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, |
FETCH cursor_alert INTO into current_date, current_host_name, |
||
− | + | ||
END WHILE; |
END WHILE; |
||
CLOSE cursor_ip; |
CLOSE cursor_ip; |
Версия 10:54, 20 апреля 2011
Анализ логов нагиоса
Загрузить логи в 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 FUNCTION GetAllerts; delimiter // CREATE FUNCTION 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 ;