Mysql NAgios Log - История изменений

Sirmax в 13:39, 28 сентября 2012

2012-09-28T13:39:15Z

← Предыдущая		Версия 13:39, 28 сентября 2012
Строка 1:		Строка 1:
		+	[[Категория:Linux]]
		+	[[Категория:Nagios]]
		+	[[Категория:MySQL]]
		+	[[Категория:GeneralSQL]]
	=Анализ логов нагиоса=		=Анализ логов нагиоса=
	==Загрузить логи в Mysql==		==Загрузить логи в Mysql==

Sirmax: /* Это Просто пример */

2011-04-21T07:01:09Z

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

← Предыдущая		Версия 07:01, 21 апреля 2011
Строка 73:		Строка 73:
	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;
−	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 ;
	</PRE>		</PRE>

Sirmax: /* 1 */

2011-04-21T07:01:01Z

← Предыдущая		Версия 07:01, 21 апреля 2011
Строка 28:		Строка 28:
	</PRE>		</PRE>

		+	==Удалить лишние записи==
−	==1==
		+	Очень просто - удалить запись если она "такая же" как предыдущяя
	<PRE>		<PRE>
	DROP PROCEDURE GetAllerts;		DROP PROCEDURE GetAllerts;

Sirmax: /* Загрузить логи в Mysql */

2011-04-21T07:00:24Z

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

← Предыдущая		Версия 07:00, 21 апреля 2011
Строка 2:		Строка 2:
	==Загрузить логи в 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>

Sirmax: /* 1 */

2011-04-20T13:56:13Z

← Предыдущая		Версия 13:56, 20 апреля 2011
Строка 39:		Строка 39:
	DECLARE current_date1 INT(10);		DECLARE current_date1 INT(10);
	DECLARE prev_date 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 ~~LIMIT~~;	+	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;

Sirmax: /* 1 */

2011-04-20T13:55:43Z

← Предыдущая		Версия 13:55, 20 апреля 2011
Строка 39:		Строка 39:
	DECLARE current_date1 INT(10);		DECLARE current_date1 INT(10);
	DECLARE prev_date 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 LIMIT 10;	+	DECLARE cursor_alert CURSOR FOR SELECT date,host_name,check_name,check_status FROM nm ORDER BY host_name,check_name,date LIMIT;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

Строка 45:		Строка 45:
	SET prev_check_name = '';		SET prev_check_name = '';
	SET prev_check_status = '';		SET prev_check_status = '';
−	SELECT "This is my stored procedure";	+	-- SELECT "This is my stored procedure";
	OPEN cursor_alert;		OPEN cursor_alert;
	FETCH cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status;		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 current_date1, current_host_name, current_check_name, current_check_status;
−	SELECT prev_date, prev_host_name, prev_check_name, prev_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))		IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = prev_check_status))
	THEN		THEN
−	SELECT "DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date=current_date1;";	+	-- 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;		DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date=current_date1;
	END IF;		END IF;

Sirmax: /* 1 */

2011-04-20T13:53:52Z

← Предыдущая		Версия 13:53, 20 апреля 2011
Строка 53:		Строка 53:
	IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = 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
		⚫	SELECT "DELETE FROM nm WHERE host_name=current_host_name AND check_status=current_check_status AND date=current_date1;";
−
⚫	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;		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_host_name = current_host_name;

Sirmax: /* 1 */

2011-04-20T13:53:00Z

← Предыдущая		Версия 13:53, 20 апреля 2011
Строка 39:		Строка 39:
	DECLARE current_date1 INT(10);		DECLARE current_date1 INT(10);
	DECLARE prev_date 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 cursor_alert CURSOR FOR SELECT date,host_name,check_name,check_status FROM nm ORDER BY host_name,check_name,date LIMIT 10;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

Строка 45:		Строка 45:
	SET prev_check_name = '';		SET prev_check_name = '';
	SET prev_check_status = '';		SET prev_check_status = '';
		+	SELECT "This is my stored procedure";
	OPEN cursor_alert;		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 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))		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=~~current_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_host_name = current_host_name;

Sirmax: /* 1 */

2011-04-20T13:26:03Z

← Предыдущая		Версия 13:26, 20 апреля 2011
Строка 41:		Строка 41:
	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;
		+
−	FETCH cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status;
	SET prev_host_name = '';		SET prev_host_name = '';
	SET prev_check_name = '';		SET prev_check_name = '';
	SET prev_check_status = '';		SET prev_check_status = '';
		+	OPEN cursor_alert;
−
	WHILE done = 0 DO		WHILE done = 0 DO
	IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = prev_check_status))		IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = prev_check_status))

Sirmax: /* 1 */

2011-04-20T13:24:15Z

← Предыдущая		Версия 13:24, 20 апреля 2011
Строка 41:		Строка 41:
	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;
		+	FETCH cursor_alert INTO current_date1, current_host_name, current_check_name, current_check_status;
		+	SET prev_host_name = '';
		+	SET prev_check_name = '';
		+	SET prev_check_status = '';
		+
	WHILE done = 0 DO		WHILE done = 0 DO
	IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = prev_check_status))		IF ( (current_host_name = prev_host_name ) AND ( current_check_name = prev_check_name ) AND ( current_check_status = prev_check_status))