EventLog_Capture - Blacklist removal - removing cruft records before insertion

Eventlog_Blacklist_Removal.sql

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Eventlog_Blacklist_Removal
@ComputerName sysname, @EventLog VARCHAR (255)
AS 
SET NOCOUNT on
/*
--Uses log parser to scan the event logs 
1.00 Michael Bourgon 2010/02/05 still need to be fixed: TRDB issues, "Error reading event log: The remote procedure call failed."

2.00 mdb 2015/04/29	The all-new, powershell-driven approach.  Used GWMI and get-WinEvent to pull logs, using remoting if possible.

V2, unsure if this applies:
--while it can run in Chicago (run on VI-01 so that it can reach the TRDBs and APs)
-- another problem then occurs: it can't insert into the partitioned view. Cursor error of some sort.

--V2, This still applies:
--There are two jobs: one that runs the log reader, and one that runs monthly that creates the new tables and alters the view.
-- you should only need last month and this month; new servers are only polled for the past two weeks.
-- So provided it even just runs weekly, you should be fine.

2015/04/30 mdb 2.01 moving update logic, trying to figure out the dupes.
2015/05/04 mdb 2.02 dupes were due to stragglers (still unexplained) and the cluster. Added Straggler re-run
						also: adding insert_datetime everywhere.
2015/05/07 mdb 2.03 changing size of fields "strings" to 1000, "message" to 1500"

-----------------------
--RUN ON FTW-SV-DB-03--
-----------------------
---------------
--create tables
---------------

--create table EventLog_EventBlacklist (Event_Name VARCHAR(255), DESCRIPTION VARCHAR(50), is_active BIT)
--INSERT INTO eventlog_eventblacklist (name, is_active) VALUES ('The McAfee McShield service entered',1)
--INSERT INTO eventlog_servers (SERVER, is_active) VALUES ('FTW-SV-DB-03', 1)
--DECLARE @blacklist TABLE (NAME VARCHAR(255)
--SELECT NAME FROM EventLog_EventBlacklist WHERE is_active IS NULL OR is_active = 1
--you'll need to create a primary key on the EventLog_Staging table named PK__EventLog_Staged__RecordNumber
-- that's needed for the fulltext below

-- note that the ID is now the PK. Needed so we can run in parallel.
drop table [dbo].[EventLog_Staged_PoSH]  
CREATE TABLE [dbo].[EventLog_Staged_PoSH]  
(
ID INT IDENTITY,
[EventLog] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RecordNumber] [bigint] NOT NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--View so that we can insert into here and have it auto-pick-up the IDENTITY
CREATE VIEW [EventLog_Staged_PoSH_Insert]
AS 
SELECT EventLog,
		RecordNumber,
		TimeGenerated,
		TimeWritten,
		EventID,
		EventType,
		EventTypeName,
		EventCategory,
		EventCategoryName,
		SourceName,
		Strings,
		ComputerName,
		SID,
		Message FROM EventLog_Staged_PoSH

ALTER TABLE [dbo].[EventLog_Staged_PoSH] ADD CONSTRAINT [PK__EventLog_Staged_PoSH__RecordNumber] PRIMARY KEY CLUSTERED  (ID) ON [PRIMARY]
GO
CREATE FULLTEXT INDEX ON [EventLog_Staged_PoSH] KEY INDEX [PK__EventLog_Staged_PoSH__RecordNumber] ON [Eventlog_Blacklist] 
with change_tracking AUTO --change_tracking AUTO is the default.
GO
ALTER FULLTEXT INDEX ON [dbo].[EventLog_Staged_PoSH] ADD (Message LANGUAGE 1033)
GO
create index ncidx__EventLog_Staged_PoSH__computername__eventlog on EventLog_Staged_PoSH (Computername, EventLog)
--needed since we'll have multiple computers in there at once.

create table EventLog_Staged_Errors (ID bigint identity primary key, ErrorState int, ErrorSeverity int, errormessage nvarchar(4000))
--trying to figure out leftovers
create table EventLog_Blacklist_Calls (ID bigint identity primary key, computername varchar(255), eventlog varchar(255), insert_datetime datetime default(getdate()))
------------
--fulltext--
------------
--mdb 2015/04/29 - below are original comments from V1.

--sp_fulltext_database 'enable'
--CREATE FULLTEXT CATALOG Eventlog_Blacklist AS default
--We create the index on Staged since that's the table we're filtering (in order to populate the Daily table).  
--	Plus it keeps things small(er).
--mdb 2015/04/29 the above block for Eventlog_Staged_PoSH seems to handle all of the below.  Saving here until i'm sure.
--CREATE FULLTEXT INDEX ON dbo.EventLog_Staged ([Message])
--KEY INDEX PK__EventLog_Staged__RecordNumber WITH Change_Tracking AUTO 

*/
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
	
DECLARE @blacklist_message varchar(255), @min int, @max int
DECLARE @blacklist TABLE (id INT IDENTITY, event_name VARCHAR(255)) 
IF object_id('tempdb..#Errors') IS NOT NULL
   DROP TABLE #Errors
CREATE TABLE #Errors (resultant VARCHAR(255))
IF object_id('tempdb..#Full_Errors') IS NOT NULL
   DROP TABLE #Full_Errors
CREATE TABLE #Full_Errors (id INT IDENTITY, server_name sysname, resultant VARCHAR(255))

DECLARE @max_recordnumber BIGINT, @insert_datetime DATETIME
DECLARE @waitcounter TINYINT = 1
BEGIN TRY 

----had issues where it would skip result sets... I think possibly because it runs before it's committed
----doing the above part so that it doesn't have to wait 1 second if it doesn't need to.
--WHILE (@waitcounter <= 5 AND @max_recordnumber IS NULL)
--begin
--	SELECT @max_recordnumber = MAX(RecordNumber) , @insert_datetime = MAX(insert_datetime)
--	FROM EventLog_Staged_PoSH 
--	WHERE eventlog = @EventLog 
--	AND ComputerName = @ComputerName
--	INSERT INTO EventLog_Blacklist_Calls (computername, eventlog, max_recordnumber, inserted_dt_staging) VALUES (@ComputerName, @EventLog, @max_recordnumber, @insert_datetime)

--	WAITFOR DELAY '00:00:01'
--	SET @waitcounter = @waitcounter + 1
--END 	

SELECT @max_recordnumber = MAX(RecordNumber) 
	FROM EventLog_Staged_PoSH 
	WHERE eventlog = @EventLog 
	AND ComputerName = @ComputerName


--updating the table is at the end, in case it crashes. 

-------------------------------------------------------------------------------------------
--Blacklist removal code.  Uses the EventLog_Blacklist table to determine what to delete.--
-------------------------------------------------------------------------------------------
--Doing this to allow inclusion/exclusion of "all servers" from blacklist.
-- If the servername is null, it assumes it's for all servers, but you can explicitly forbid a server from using it.
DELETE FROM @blacklist
INSERT INTO @blacklist (event_name) 
	--get "all server" events
	SELECT event_name FROM dbo.EventLog_EventBlacklist
	--but exclude when we've set is_active 
		WHERE 
		is_active = 1 
		AND Server_Name IS NULL 
		AND NOT EXISTS 
		(
			SELECT * FROM EventLog_EventBlacklist whitelist 
			WHERE whitelist.Event_Name = EventLog_EventBlacklist.Event_Name
			AND whitelist.Server_Name = @computername 
			AND is_active = 0
		)
	UNION ALL
	--and include all explicit blacklist entries for that server
	SELECT event_name 
		FROM dbo.EventLog_EventBlacklist
		WHERE Server_Name = @computername AND is_active = 1

--dealing with regexp
UPDATE @blacklist SET event_name = REPLACE(event_name, '[','[[]')

SELECT @min = NULL, @max = NULL

SELECT @min = MIN(id), @max = MAX(id) FROM @blacklist

WHILE @min <= @max
	BEGIN
		SELECT @blacklist_message = event_name FROM @blacklist WHERE id = @min

		DELETE FROM dbo.EventLog_Staged_PoSH WHERE message LIKE '%'+@blacklist_message+'%'
		AND computername = @ComputerName AND EventLog = @EventLog

		SET @min = @min + 1
	END 

-------------------------------
--Insert into permanent table--
-------------------------------



	INSERT INTO EventLog_Details 
			(EventLog ,
			RecordNumber ,
			TimeGenerated ,
			TimeWritten ,
			EventID ,
			EventType ,
			EventTypeName ,
			EventCategory ,
			EventCategoryName ,
			SourceName ,
			Strings ,
			ComputerName ,
			[SID] ,
			[Message],
			insert_datetime )
	--changing the field length here because trying "width" via custom table in PoSH caused "illegal key width" error
	SELECT  EventLog ,
			RecordNumber ,
			TimeGenerated ,
			TimeWritten ,
			EventID ,
			EventType ,
			EventTypeName ,
			EventCategory ,
			EventCategoryName ,
			SourceName ,
			LEFT(Strings,1000) ,
			ComputerName ,
			[SID] ,
			LEFT([Message],1500),
			insert_datetime
			FROM dbo.EventLog_Staged_PoSH
			WHERE ComputerName = @ComputerName
					AND EventLog = @EventLog
END TRY
BEGIN CATCH
        PRINT 'Error Occurred on Insert'

        SELECT  @ErrorMessage = ERROR_MESSAGE(),
                    @ErrorSeverity = ERROR_SEVERITY(),
                    @ErrorState = ERROR_STATE();

		INSERT INTO EventLog_Staged_Errors (errorstate, errorseverity, errormessage, computername, eventlog) 
				VALUES (@errorstate, @ErrorSeverity, @ErrorMessage, @computername, @eventlog)

        IF @@TRANCOUNT > 0 
                ROLLBACK TRANSACTION;
        PRINT 'Error Severity: ' + CONVERT(VARCHAR(30), @ErrorSeverity)
        PRINT 'Error State: ' + CONVERT(VARCHAR(30), @ErrorState)
        PRINT 'Error Severity: ' + @ErrorMessage
        --commented this out because I don't want it dy.  
        --RAISERROR  (@ErrorMessage, -- Message text.
        --                                @ErrorSeverity, -- Severity.
        --                                @ErrorState -- State.
        --                                );


END CATCH

--trying to keep the staging table small
IF @@ERROR = 0 AND @ErrorState IS null
	BEGIN
		DELETE FROM EventLog_Staged_PoSH
		WHERE ComputerName = @ComputerName
				AND EventLog = @EventLog

--if a null set is returned (because there are no new events) then we need to make sure it doesn't get set to null

	IF @max_recordnumber IS NOT NULL AND @eventlog = 'Application'
		UPDATE EventLog_Servers 
		SET Application_RecordCount = @max_recordnumber, 
			Application_Last_Inserted = GETDATE() 
		WHERE Server_Name = @computername 
	ELSE
	IF @max_recordnumber IS NOT NULL AND @EventLog = 'System'
		UPDATE EventLog_Servers 
		SET System_RecordCount = @max_recordnumber, 
			System_Last_Inserted = GETDATE() 
		WHERE Server_Name = @computername
	END 



GO