Cacher is the code snippet organizer for pro developers

/****** Object:  StoredProcedure [dbo].[usp_import_computer_checkin_history]    Script Date: 05/17/2013 14:01:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[usp_import_computer_checkin_history]
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.#ComputerCheckinHistoryTempTable' ) IS NOT NULL
	BEGIN
	  DROP TABLE #ComputerCheckinHistoryTempTable
	END
	
	CREATE TABLE #ComputerCheckinHistoryTempTable
	(
		[USER_NAME] [nvarchar](512) NULL,
		[COMPUTER_NAME] [nvarchar](128) NULL,
		[IP_ADDR1] [varchar](15) NULL,
		[LAST_UPDATE_TIME] [datetime] NULL,
		[LAST_CONNECTED_IP_ADDR] [varchar](15) NULL,
		[AGENT_VERSION] [nvarchar](128) NULL,
		[VERSION] [varchar](255) NULL,
		[REGIONAL_COLLECTOR] [varchar](64) NULL
    )
	
	IF OBJECT_ID('tempdb.dbo.#ComputerCheckinHistoryRegionTempTable' ) IS NOT NULL
	BEGIN
	  DROP TABLE #ComputerCheckinHistoryRegionTempTable
	END

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

	WHILE @MinRow <= @MaxRow
	
	BEGIN

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

	-- Used to fetch only data from the last timestamp([LAST_UPDATE_TIME])
	
	DECLARE @LastImportDateTime datetime
	SET @LastImportDateTime = (SELECT MAX(LAST_UPDATE_TIME) FROM [SEPM].[dbo].[COMPUTER_CHECKIN_HISTORY] where Regional_Collector = @RegionalCollector)
	
	DECLARE @LastImportDateAsTimeStamp bigint
	SET @LastImportDateAsTimeStamp = (Select convert(bigint,(datediff(ss,'19700101',@LastImportDateTime)))*1000) 
	
	-- Create a retention period of one year
	
	DECLARE @RetentionPeriod datetime
	SET @RetentionPeriod = GETDATE() - 365
	
	
	IF  @LastImportDateTime IS NULL OR  @LastImportDateTime <= 0
	BEGIN
		SET  @LastImportDateTime = @RetentionPeriod
	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].[COMPUTER_CHECKIN_HISTORY] WHERE LAST_UPDATE_TIME < @RetentionPeriod

	-- OPENQUERY to pull full table of all computer check in history within the SEPM regional collectors
	
	
	SET @sepmSQL = 
	N'INSERT INTO #ComputerCheckinHistoryTempTable
		(
				[USER_NAME],
				[COMPUTER_NAME],
				[IP_ADDR1],
				[LAST_UPDATE_TIME],
				[AGENT_VERSION],            
				[VERSION],
				[REGIONAL_COLLECTOR]   
		)
		SELECT 
			USER_NAME,
			COMPUTER_NAME,
			sepm.dbo.IntegerToIPAddress(IP_ADDR1),
			LAST_UPDATE_TIME,
			AGENT_VERSION,
			VERSION,
			'''+@RegionalCollector+''' AS REGIONAL_COLLECTOR 
		         
		FROM OPENQUERY	      
		(        
		   ['+@RegionalCollector+'],    
			''SELECT
			    c.USER_NAME,
				sc.COMPUTER_NAME,
				sc.IP_ADDR1,
				dateadd(ss, sa.LAST_UPDATE_TIME/1000, ''''1970-01-01'''') as LAST_UPDATE_TIME,
				sa.AGENT_VERSION,
				p.VERSION	
			FROM ['+@DatabaseName+'].['+@SchemaName+'].[SEM_COMPUTER] sc
			INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].[SEM_CLIENT] c ON sc.COMPUTER_ID = c.COMPUTER_ID 
			INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].[SEM_AGENT] sa ON sc.COMPUTER_ID = sa.COMPUTER_ID 
			INNER JOIN ['+@DatabaseName+'].['+@SchemaName+'].PATTERN p ON sa.PATTERN_IDX = p.PATTERN_IDX
			WHERE 
				p.PATTERN_TYPE =  ''''VIRUS_DEFS''''
				AND p.DELETED=''''0''''
				AND sa.DELETED=''''0''''
				AND sc.DELETED=''''0'''' 
				and sa.LAST_UPDATE_TIME > CONVERT(bigint,'+CONVERT(varchar,@LastImportDateAsTimeStamp)+') ''	
		) as scans_readable 
		ORDER BY scans_readable.LAST_UPDATE_TIME;';
	
	
	
	
	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
	/*Statement to insert a subset of records into the COMPUTER_CHECKIN_HISTORY table from #ComputerCheckinHistoryTempTable (master pull).
	  Description of ROW_NUMBER() clause: Command to create partitions within the data, based on row-sets, grouped by columns declared
	  in the PARTITION BY clause and placed in the order declared by the ORDER BY clause. The partition will include data where only, but all
	  columns declared contain similar values. Row numbers are assigned to each element in a partition, starting at 1. The OVER clause simply 
	  executes the PARTITION and ORDER BY clause before associating the result set with aggregate or ranking function. 

      Needs: The set we return could be grouped by a single value and will return the latest scan for the element we group by. This will not
      capture chnages on the network in regards to different users signed into a single machine during a similar timeframe or a machine having
	  mutiple IP addresses within a similar timeframe. By partitioning on all three values, we are creating sets of these unique values for 
      one-offs or changes in the network that would otherwise be missed if just selecting the top value of one grouping. 	  
	
	*/
	
	INSERT INTO [SEPM].[dbo].[COMPUTER_CHECKIN_HISTORY]
	(
		[USER_NAME],
		[COMPUTER_NAME],
		[IP_ADDR1],
		[LAST_UPDATE_TIME],
		[AGENT_VERSION],            
		[VERSION],
		[REGIONAL_COLLECTOR]
	) 
	
		SELECT  
			USER_NAME,
			COMPUTER_NAME,
			IP_ADDR1,
			LAST_UPDATE_TIME,
			AGENT_VERSION,
			VERSION,
			REGIONAL_COLLECTOR

    FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY USER_NAME,COMPUTER_NAME,IP_ADDR1 ORDER BY LAST_UPDATE_TIME DESC) part
        FROM #ComputerCheckinHistoryTempTable) A
        WHERE part = 1
	
	
	/* Drop the two #temptables created within Proc*/
	
	IF OBJECT_ID('tempdb.dbo.#ComputerCheckinHistoryTempTable' ) IS NOT NULL
	BEGIN
      DROP TABLE #ComputerCheckinHistoryTempTable
	END
	
	IF OBJECT_ID('tempdb.dbo.#ComputerCheckinHistoryRegionTempTable' ) IS NOT NULL
	BEGIN
      DROP TABLE #ComputerCheckinHistoryRegionTempTable
	END
	
	
 END




GO