Cacher is the code snippet organizer for pro developers

USE [SEPM]
GO

/****** Object:  StoredProcedure [dbo].[usp_import_alerts_readable]    Script Date: 05/13/2013 15:44:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[usp_import_alerts_readable]
AS
  SET NOCOUNT ON;

BEGIN

	DECLARE @MinRow INT
	DECLARE @MaxRow INT
	DECLARE @CurrentRow INT
	DECLARE @Event VARCHAR(32)
	DECLARE @RegionalCollector VARCHAR(32)
	DECLARE @DatabaseName VARCHAR(32)
	DECLARE @SchemaName VARCHAR(32)
	DECLARE @TranName varchar(100)
	DECLARE @sepmSQL nvarchar(max)
	
	IF OBJECT_ID('tempdb.dbo.#AlertsReadableTempTable' ) IS NOT NULL
	BEGIN
	  DROP TABLE #AlertsReadableTempTable
	END

	CREATE TABLE #AlertsReadableTempTable
	(
		RowId INT PRIMARY KEY IDENTITY(1,1)
	  , RegionalCollector VARCHAR(32)
	  , DatabaseName VARCHAR(32)
	  , SchemaName VARCHAR(32)
	)
	
	INSERT INTO #AlertsReadableTempTable
	SELECT [REGIONAL_COLLECTOR], [DATABASE], [SCHEMA_NAME]
	FROM [SEPM].[dbo].[EVENT_CONFIGURATION]
	WHERE [EVENT_TYPE] = 'ALERTS_READABLE' AND ENABLED = 1;
      
	SELECT @MinRow = (SELECT MIN(RowId) FROM #AlertsReadableTempTable)
	SELECT @MaxRow = (SELECT MAX(RowId) FROM #AlertsReadableTempTable)

	WHILE @MinRow <= @MaxRow
	
	BEGIN

    SET @CurrentRow = @MinRow
    SET @RegionalCollector = (SELECT RegionalCollector FROM #AlertsReadableTempTable WHERE RowId = @CurrentRow)
    SET @DatabaseName = (SELECT DatabaseName FROM #AlertsReadableTempTable WHERE RowId = @CurrentRow)
    SET @SchemaName = (SELECT SchemaName FROM #AlertsReadableTempTable WHERE RowId = @CurrentRow)
          
    SET @TranName =  @Event +' import from ' + @RegionalCollector

	-- Used to fetch only data from the last timestamp
	DECLARE @LastImportTime bigint
	SET @LastImportTime = (SELECT MAX(TIME_STAMP) FROM [SEPM].[dbo].[ALERTS_READABLE] where Regional_Collector = @RegionalCollector)
	
	-- Creates datetime timestamp
	DECLARE @RetentionPeriod datetime
	SET @RetentionPeriod = GETDATE() - 365
	
	
	IF  @LastImportTime IS NULL OR  @LastImportTime <= 0
	BEGIN
		SET  @LastImportTime = (Select convert(bigint,(datediff(ss,'19700101',@RetentionPeriod)))*1000)
		TRUNCATE TABLE [SEPM].[dbo].[ALERTS_READABLE]
	END
	
	BEGIN TRAN @TranName
	
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	-- DELETES data older than what is specified by the Retention Period
	
	DELETE FROM [SEPM].[dbo].[ALERTS_READABLE] WHERE ALERTDATETIME < @RetentionPeriod

	-- INSERTS "Alerts" data from Symantec database into the ALERTS_READABLE table
	
	
	SET @sepmSQL = 
	N'INSERT INTO [SEPM].[dbo].[ALERTS_READABLE]
		(
				[ALERT],
				[USER_NAME],
				[COMPUTER_NAME],
				[IP_ADDR1_TEXT],
				[VIRUSNAME],
				[FILEPATH],
				[SOURCE],
				[ACTUALACTION],
				[ALERTDATETIME],               
				[ALERTINSERTTIME],
				[TIME_STAMP],
                [REGIONAL_COLLECTOR] 				
		)
		SELECT 
				ALERT, 
				USER_NAME, 
				COMPUTER_NAME, 
				IP_ADDR1_TEXT, 
				VIRUSNAME, 
				FILEPATH, 
				SOURCE,
				ACTUALACTION, 
				ALERTDATETIME,
				ALERTINSERTTIME,
				TIME_STAMP,
				'''+@RegionalCollector+''' AS REGIONAL_COLLECTOR 
		         
		FROM OPENQUERY	      
		(        
		   ['+@RegionalCollector+'],    
			''SELECT 
        
					am.ALERT, 
					a.USER_NAME, 
					i.COMPUTER_NAME, 
					vs. IP_ADDR1_TEXT, 
					v.VIRUSNAME, 
					a.FILEPATH, 
					a.SOURCE,
					aa.ACTUALACTION, 
					a.ALERTDATETIME,
					a.ALERTINSERTTIME,
					a.TIME_STAMP



FROM ['+@DatabaseName+'].['+@SchemaName+'].ALERTS a 
INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].ALERTMSG am ON a.ALERT_IDX = am.ALERT_IDX
INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].SEM_COMPUTER i ON a.COMPUTER_IDX = i.COMPUTER_ID 
INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].VIRUS v ON a.VIRUSNAME_IDX = v.VIRUSNAME_IDX 
INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].ACTUALACTION aa ON a.ACTUALACTION_IDX = aa.ACTUALACTION_IDX
INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].V_SEM_COMPUTER vs ON i.COMPUTER_ID = vs.COMPUTER_ID


WHERE a.TIME_STAMP > CONVERT(bigint,'+CONVERT(varchar,@LastImportTime)+')'' 
) as alerts_readable

ORDER BY alerts_readable.TIME_STAMP;';
	
	
	
	
	EXEC SP_EXECUTESQL @sepmSQL;
	 
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
      
-- Error handling
      
      IF @@ERROR <> 0
      BEGIN
            PRINT 'Rolling back ' + @TranName
            ROLLBACK TRAN @TranName
      END
      ELSE
      BEGIN
            PRINT 'Committing ' + @TranName
            COMMIT TRAN @TranName
      END
      
      -- Advance to next row in our loop
      SET @MinRow = @MinRow + 1
	
	END
	
	
	IF OBJECT_ID('tempdb.dbo.#AlertsReadableTempTable' ) IS NOT NULL
	BEGIN
      DROP TABLE #AlertsReadableTempTable
	END
END



GO