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