USE [syslog]
GO
/****** Object:  StoredProcedure [dbo].[SyslogSequence2]    Script Date: 04/12/2007 13:26:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alexander Björnhagen
-- Create date: 04/03/2007
-- Description:	SP som loopar igenom alla regex seqvenser och kolla om dess seqvenser återfinns i sequencepattern.
-- =============================================

ALTER PROCEDURE [dbo].[SyslogSequence2]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT ON;
	--Dess variabler tillhör mail formatteringen
	DECLARE @time DATETIME;
	DECLARE @msg VARCHAR(600);
	DECLARE @cleanup_counter INT;
	DECLARE @mail_body VARCHAR(max);
	DECLARE @mail_subject VARCHAR(max);
	--slut på mail variabler
	--Resten av variablerna tillhör själv matchnings looparna
	DECLARE @orig_regex_ID int
	DECLARE @orig_regex VARCHAR(max) --första regexet i seqvensen
	DECLARE @first_positive_regex VARCHAR(max) --första regexet i seqvensen som har en positiv operator
	DECLARE @last_positive_regex_ID VARCHAR(max) --sista regexet i seqvensen som har en positiv operator
	DECLARE @num_positive_reg int	--antal regex som har posititiv operator i seqvensen
	DECLARE @counter int	--räknare, används till lite allt möjligt
	DECLARE @counter2 int	--räknare, används till lite allt möjligt
	DECLARE @operator int	--operator för aktuellt regex
	DECLARE @interval INT	--
	DECLARE @current_regex VARCHAR(max) --aktuellt regex

	DECLARE @last_operator_in_sequence int --den sista operatorn i regexet
	DECLARE @positive_reg_counter int
	DECLARE @error_flag int
	DECLARE @last_time DATETIME
	DECLARE @previous_time DATETIME	--tid innan
	DECLARE @next_time DATETIME	--tid efter
	DECLARE @slask_ID int
	DECLARE @slask varchar(max)
	DECLARE @host VARCHAR(15) --seqvensen gäller bara för en host
	--Nu loopar vi igenom alla regex i sequenceregex som har en SequenceNR=1 eftersom det betyder att dom är först i seqvensen
 	DECLARE Pointer CURSOR FOR
	SELECT sequenceregex.regex,sequenceIndex.interval,sequenceIndex.ID FROM sequenceregex,sequenceIndex WHERE sequenceregex.SequenceIndex_ID=sequenceIndex.ID
	AND sequenceregex.sequenceNR=1
	OPEN Pointer
	FETCH NEXT FROM Pointer
	into @orig_regex,@interval,@orig_regex_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		print '@operator:'+CONVERT(VARCHAR,@operator)
		print '@orig_regex:'+@orig_regex
		SELECT @last_operator_in_sequence = (SELECT top 1 operator FROM sequenceregex
		WHERE SequenceIndex_ID=@orig_regex_ID
		order by sequenceregex.ID desc)
		--om första regexets operator är 0 så måste vi hitta första regexet i seqvensen där operator är 1
		--annars finns det ju ingen tidpunkt eller host att utgå ifrån
		SELECT @first_positive_regex = (SELECT top 1 regex FROM SequenceRegex WHERE SequenceIndex_ID=@orig_regex_ID AND operator=1 ORDER BY SequenceNR desc)
		SELECT @last_positive_regex_ID=(SElECT top 1 ID from sequenceRegex WHERE SequenceIndex_ID=@orig_regex_ID AND operator=1 ORDER BY SequenceNR asc)
		--starttiden
		SELECT @previous_time = (SELECT top 1 Receivedat FROM data,sequencepattern WHERE data.ID=sequencepattern.Data_ID
		AND data.msg LIKE @first_positive_regex order by data.receivedat desc)
		--sluttiden
		SELECT @last_time = (SELECT top 1 Receivedat FROM data,sequencepattern WHERE data.ID=sequencepattern.Data_ID
		AND sequencepattern.Sequenceregex_ID=@last_positive_regex_ID order by data.receivedat desc)
		--vår host
		SELECT @host = (SELECT top 1 senderip FROM data,sequencepattern WHERE data.ID=sequencepattern.Data_ID
		AND data.msg LIKE @first_positive_regex order by data.receivedat
		)
		print 'XXX hosten är '+@host
		--Om vi inte vill ha in något meddelande alls blir det lite speciellt eftersom vi inte har någon startpunkt
		--Alltså om alla operator i hela seqvensen är noll
		--vi tog ju trots allt inte emot ett enda meddelande...
		--EGENTLIGEN BORDE VI ALLTID TA EMOT MINST ETT MEDDELANDE
		IF (@first_positive_regex IS NULL AND @last_positive_regex_ID IS NULL)
		BEGIN
		SET @previous_time=GETDATE()
		SET @host='N/A'
		SET @first_positive_regex=@orig_regex
		SET @next_time=@previous_time
		SET @last_time=@previous_time
		END
		--totalt antal regex i seqvensen som har positiv operator
		SELECT @num_positive_reg = (SELECT Count(*) From sequenceregex WHERE SequenceIndex_ID=@orig_regex_ID AND operator=1)
		--Nu kan vi loopa igenom resten av seqvensen ifråga och kolla dom en för en
		SET @positive_reg_counter=0
		SET @counter2 = 0
		SET @error_flag= 0
	 	DECLARE Inner_Pointer CURSOR FOR
		SELECT sequenceregex.regex,data.receivedat,sequenceregex.operator,
		data.msg,sequenceregex.SequenceNR,data.receivedat
		from data,sequencepattern,sequenceregex where sequencepattern.data_ID=data.ID
		AND sequencepattern.SequenceRegex_ID=sequenceregex.ID 
		AND sequenceRegex.SequenceIndex_ID=@orig_regex_ID
		AND sequenceregex.SequenceNR > 0 order by data.ID
		OPEN Inner_Pointer
		FETCH NEXT FROM Inner_Pointer
		into @current_regex,@next_time,@operator,@msg,@counter,@last_time
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--har vi redan räknat denna sequenceNR?
			if(@counter2!=@Counter)
			BEGIN
			SET @positive_reg_counter=(@positive_reg_counter+1)
			END
			SET @Counter2=@Counter
			print 'regex är: '+@current_regex
			print '@next_time är: '+CONVERT(varchar,@next_time,21)
			print '@counter är '+CONVERT(varchar,@counter)
			print '@operator är '+CONVERT(varchar,@operator)
			print '@msg är:'+@msg
			print '@num_positive_reg är:'+CONVERT(varchar,@num_positive_reg)
			print '@positive_reg_counter är:'+CONVERT(varchar,@positive_reg_counter)

		
			print ' '
			IF ((@msg IS NULL) AND (@operator = 1))
			OR ((@msg IS NOT NULL) AND (@operator = 0))
			BEGIN
		--	print 'vi hittade nåt vi inte ville ha'
		--	print 'eller så hittade vi inte nåt vi ville ha'
			print 'error flag är 1 !'
			SET @error_flag=1
			END
			
			FETCH NEXT FROM Inner_Pointer
			into @current_regex,@next_time,@operator,@msg,@counter,@last_time
		END
	CLOSE Inner_Pointer
	DEALLOCATE Inner_Pointer
	
	--är vi klara ?
	IF (@num_positive_reg=@positive_reg_counter AND @error_flag!=1)
	BEGIN
		print 'vi kanske är klara nu'
		print 'vi måste kolla tiden'
		print '@last_operator_in_sequence är:'+CONVERT(varchar,@last_operator_in_sequence)
		print '@next_time är:     '+CONVERT(varchar,@next_time,21)
		print '@previous_time är: '+CONVERT(varchar,@previous_time,21)
		print 'GETDATE():         '+CONVERT(varchar,GETDATE(),21)
		print '@interval är:'+CONVERT(varchar,@interval)
		print 'DATEDIFF(minute,@previous_time,GETDATE()) är: '+CONVERT(varchar,DATEDIFF(minute,@previous_time,GETDATE()))
		print CONVERT(varchar,DATEDIFF(second,@previous_time,GETDATE()))
			IF (( DATEDIFF(minute,@previous_time,GETDATE()) >= @interval ) AND (@last_operator_in_sequence=0))
			OR ((  DATEDIFF(minute,@previous_time,GETDATE()) <= @interval ) AND (@last_operator_in_sequence=1))
			BEGIN
				print 'tiderna matchar, på hela seqvensen'
				--Nu ska vi mejla...
				SELECT @mail_subject=(SELECT title from SequenceIndex WHERE ID=@orig_regex_ID)
				SET @mail_body='Hej, regexseqvensen med titeln '+@mail_subject+' har triggats'
				+CHAR(13)+CHAR(10)++CHAR(13)+CHAR(10)
				+'Här är loggarna:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
				SET @mail_subject='Seqvensen av regex med titeln:"'+@mail_subject+'" har triggats'
				--Vi loopar i stort sett igenom hela sekvensen igen
				--Vi återanvänder alla variabler från tidigare loop
				SET @counter2=1
				DECLARE Mail_Pointer CURSOR FOR
				SELECT sequenceregex.regex,data.receivedat,sequenceregex.operator,
				data.msg,sequenceregex.SequenceNR,data.receivedat
				from data,sequencepattern,sequenceregex where sequencepattern.data_ID=data.ID
				AND sequencepattern.SequenceRegex_ID=sequenceregex.ID 
				AND sequenceRegex.SequenceIndex_ID=@orig_regex_ID
				AND sequenceregex.SequenceNR > 0 order by data.ID
				OPEN Mail_Pointer
				FETCH NEXT FROM Mail_Pointer
				into @current_regex,@next_time,@operator,@msg,@counter,@last_time
				WHILE @@FETCH_STATUS = 0
				BEGIN
				--fattas det nått ?
				print '@counter:'+CONVERT(varchar,@counter)
				print '@counter2:'+CONVERT(varchar,@counter2)
				WHILE ((@counter>@counter2) OR (@counter2=1 AND @Counter!=1))
				BEGIN
				print 'hej'
				SELECT @slask=(select regex from sequenceregex WHERE SequenceIndex_ID=@orig_regex_ID AND SequenceNR=@Counter2)
				SET @mail_body=CHAR(13)+CHAR(10)+@mail_body+CHAR(13)+CHAR(10)+
				+'Log '+CONVERT(varchar,@Counter2)+ ' matchades inte av regexet "'+@slask+'"'
				+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
				SET @counter2=(@Counter2+1)
				END
				SET @mail_body=@mail_body+
				+'Log '+CONVERT(varchar,@Counter)+':"'+@msg+'"'
				+CHAR(13)+CHAR(10)
				+'Togs emot:'+CONVERT(varchar,@next_time)
				+CHAR(13)+CHAR(10)
				+'matchade regexet:"'+@current_regex+'"'
				+CHAR(13)+CHAR(10)
				SET @Counter2=@Counter+1
				FETCH NEXT FROM Mail_Pointer
				into @current_regex,@next_time,@operator,@msg,@counter,@last_time
				END
				CLOSE mail_pointer
				DEALLOCATE mail_pointer
				
				SET @counter=(SELECT Count(*) FROM sequenceregex WHERE SequenceIndex_ID=@orig_regex_ID)
				WHILE (@counter>@counter2)
				BEGIN
				print 'hej'
				SELECT @slask=(select regex from sequenceregex WHERE SequenceIndex_ID=@orig_regex_ID AND SequenceNR=@Counter)
				SET @mail_body=CHAR(13)+CHAR(10)+@mail_body+CHAR(13)+CHAR(10)+
				+'Log '+CONVERT(varchar,@Counter2+1)+ ' matchades inte av regexet "'+@slask+'"'
				+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
				SET @counter2=(@Counter2+1)
				END

				--Nu ska vi lägga till seqvensen så man enkelt kan se vad som matchades
				SET @mail_body=@mail_body
				+CHAR(13)+CHAR(10)
				+CHAR(13)+CHAR(10)
				+'så här såg seqvensen ut:'
				+CHAR(13)+CHAR(10)
				DECLARE Mail_Pointer2 CURSOR FOR
				SELECT sequenceregex.sequenceNR,sequenceregex.regex,sequenceregex.operator 
				FROM sequenceregex,sequenceIndex 
				WHERE sequenceregex.SequenceIndex_ID=sequenceIndex.ID
				AND sequenceIndex.ID=1 order by SequenceNR asc
				OPEN Mail_Pointer2
				FETCH NEXT FROM Mail_Pointer2
				into @counter,@current_regex,@operator
				WHILE @@FETCH_STATUS = 0
				BEGIN
				SET @mail_body=@mail_body+CHAR(13)+CHAR(10)
				+'Regex nummer--->'+CONVERT(varchar,@counter)+CHAR(13)+CHAR(10)
				+'Regex---------->'+@current_regex+CHAR(13)+CHAR(10)
				+'Operator*------>'+CONVERT(varchar,@operator)+CHAR(13)+CHAR(10)
				FETCH NEXT FROM Mail_Pointer2
				into @counter,@current_regex,@operator
				END
				CLOSE Mail_pointer2
				DEALLOCATE Mail_pointer2
				
				SET @mail_body=@mail_body+CHAR(13)+CHAR(10)
				+'* Operator är en flagga, om vi ville matcha ett meddelande eller inte'
				INSERT INTO mail VALUES (@mail_body,@mail_subject)	
				print 'mejlat'
				--Nu ska vi rensa upp i sequencepattern...
				DECLARE Del_Pointer CURSOR FOR
				SELECT sequencepattern.ID,
				sequenceregex.SequenceNR
				from data,sequencepattern,sequenceregex where sequencepattern.data_ID=data.ID
				AND sequencepattern.SequenceRegex_ID=sequenceregex.ID 
				AND sequenceRegex.SequenceIndex_ID=@orig_regex_ID
				AND sequenceregex.SequenceNR > 0 order by data.ID
				OPEN Del_Pointer
				FETCH NEXT FROM Del_Pointer
				into @slask_ID,@Counter
				WHILE @@FETCH_STATUS = 0
				BEGIN
					--har vi redan behandlat denna?
					if (@Counter2!=@Counter)
					BEGIN
						print 'deleting id:'+CONVERT(varchar,@slask_ID)
						DELETE from sequencepattern WHERE ID=@slask_ID
					END
					SET @Counter2=@Counter
					FETCH NEXT FROM Del_Pointer
					into @slask_ID,@Counter
				END
				CLOSE Del_Pointer
				DEALLOCATE Del_Pointer
			
				
			END
			ELSE
			BEGIN
				print 'nä, tiderna stämmer ej'
				BREAK --RÄTT???
			END
		END
		ELSE
		BEGIN
		print 'nä'
	END
	
	FETCH NEXT FROM Pointer
	into @orig_regex,@interval,@orig_regex_ID
	END
CLOSE Pointer
DEALLOCATE Pointer
END