labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
	-- Create exception handler
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN -- On exception
		-- Get any tables back to their original state
		ROLLBACK;
		-- Clean up tables
		DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_NEW;
		DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_OLD;
		DROP TEMPORARY TABLE IF EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts;
		DROP TEMPORARY TABLE IF EXISTS _tempComputers;
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Set isolation level back to default
		RESIGNAL; -- Rethrow exception
	END;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Allows selects in a nonlocking fashion.
	START TRANSACTION;	
		-- Create the table if its not found so we dont blow up.
		-- Since we rename the table there is a veeeeery small chance the stored proc gets interupted before finishing
		-- Which if it happens at the exact right moment, the system could be left without a table!
		CREATE TABLE IF NOT EXISTS `MicrosoftNonCompliantPatchCounts` (
		  `ComputerId` INT(11) NOT NULL,
		  `LowSeverityCount` INT(11) DEFAULT 0,
		  `ModerateSeverityCount` INT(11) DEFAULT 0,
		  `ImportantSeverityCount` INT(11) DEFAULT 0,
		  `CriticalSeverityCount` INT(11) DEFAULT 0,
		  `UnspecifiedSeverityCount` INT(11) DEFAULT 0,
		  `LowCvssCount` INT(11) DEFAULT 0,
		  `MediumCvssCount` INT(11) DEFAULT 0,
		  `HighCvssCount` INT(11) DEFAULT 0,
		  `TotalNonCompliantCount` INT(11) DEFAULT 0,
		  PRIMARY KEY (`ComputerId`)
		) ENGINE=INNODB DEFAULT CHARSET=utf8;
		#Cleanup in case something went wrong previously
		DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_NEW;
		DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_OLD;
		DROP TEMPORARY TABLE IF EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts;
		DROP TEMPORARY TABLE IF EXISTS _tempComputers;
		-- Create temp table of computerIds
		CREATE TEMPORARY TABLE IF NOT EXISTS _tempComputers
		(
			INDEX (`ComputerId`)
		)ENGINE=MEMORY
		SELECT `ComputerId` FROM `ComputerPatchingStats`;	
		-- Create temporary copy of hotfix to reduce time spent locking the hotfix table NOTE: WHERE clause is used here so we do not need it on anything else
		CREATE TEMPORARY TABLE IF NOT EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts
		(	INDEX (`ComputerId`)
		) ENGINE=MEMORY
		SELECT `ComputerId`,
		    SUM(IF(`Severity`='low',1,0)) AS LowSeverityCount,
			SUM(IF(`Severity`='moderate',1,0)) AS ModerateSeverityCount,
			SUM(IF(`Severity`='important',1,0)) AS ImportantSeverityCount,
			SUM(IF(`Severity`='critical',1,0)) AS CriticalSeverityCount,
			SUM(IF(`Severity`='', 1, 0)) AS UnspecifiedSeverityCount,
			SUM(IF(`HotfixData`.`Cvss` > 0 AND `HotfixData`.`Cvss` < 4, 1, 0)) AS LowCvssCount,
			SUM(IF(`HotfixData`.`Cvss` >= 4 AND `HotfixData`.`Cvss` < 7, 1, 0)) AS MediumCvssCount,
			SUM(IF(`HotfixData`.`Cvss` >= 7, 1, 0)) AS HighCvssCount,
			COUNT(*) AS TotalNonCompliantCount
		 FROM Hotfix JOIN HotfixData USING (HotfixId,OS) WHERE Approved = 2 AND Installed = 0 GROUP BY ComputerId; 
		-- Create table to hold collected Non-Compliant Patch Counts
		CREATE TABLE IF NOT EXISTS MicrosoftNonCompliantPatchCounts_NEW LIKE MicrosoftNonCompliantPatchCounts;
		TRUNCATE TABLE MicrosoftNonCompliantPatchCounts_NEW;
		-- Tested against 10k computers ~2 million hotfixes. Runs in 6 seconds.
		INSERT INTO MicrosoftNonCompliantPatchCounts_NEW
		(
		   `ComputerId`, `LowSeverityCount`, `ModerateSeverityCount`, `ImportantSeverityCount`, `CriticalSeverityCount`, `UnspecifiedSeverityCount`,
			`LowCvssCount`, `MediumCvssCount`, `HighCvssCount`, `TotalNonCompliantCount`
		)
		(
		  SELECT _tempComputers.ComputerId,
			IFNULL(LowSeverityCount, 0),
			IFNULL(ModerateSeverityCount, 0),
			IFNULL(ImportantSeverityCount, 0),
			IFNULL(CriticalSeverityCount, 0),
			IFNULL(UnspecifiedSeverityCount, 0),
			IFNULL(LowCvssCount, 0),
			IFNULL(MediumCvssCount, 0),
			IFNULL(HighCvssCount, 0),
			IFNULL(TotalNonCompliantCount, 0)
		  FROM _tempComputers LEFT JOIN _tempHotfixForMicrosoftNonCompliantPatchCounts USING (ComputerId)
		);		
		-- If data was successfully collected by query, go ahead and rename the table
		RENAME TABLE MicrosoftNonCompliantPatchCounts TO MicrosoftNonCompliantPatchCounts_OLD;
		-- Rename the new table to the proper name
		RENAME TABLE MicrosoftNonCompliantPatchCounts_NEW TO MicrosoftNonCompliantPatchCounts;
	COMMIT;
	-- Clean up temp and unneaded tables
	DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_NEW;
	DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_OLD;
	DROP TEMPORARY TABLE IF EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts;
	DROP TEMPORARY TABLE IF EXISTS _tempComputers;
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Set isolation level back to default
	END

sp_RunMicrosoftNonCompliantPatchCounts

Parameters

Definition