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 NonCompliantPatches_OLD;
		DROP TABLE IF EXISTS NonCompliantPatches_NEW;
		DROP TABLE IF EXISTS ComputerPatchComplianceStats_NEW;
		DROP TABLE IF EXISTS ComputerPatchComplianceStats_OLD;
		DROP TABLE IF EXISTS _tempComputers;	
		DROP TABLE IF EXISTS _tempHotfix;
		DROP TABLE IF EXISTS _tempHotfixData;
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED; #Set isolation level back to default
		RESIGNAL; #Rethrow exception
	END;
	INSERT INTO PatchManagerSettings (`Name`, `UserId`, `Value`) VALUES('LastPatchReportRunStart', 0, NOW()) ON DUPLICATE KEY UPDATE `Value` = NOW(), UserId = 0;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #Allows selects in a nonlocking fashion.
	START TRANSACTION;
		#Create temporary copy of computers to reduce time spent locking computers table. Add column LastPatched for the future
		CREATE TEMPORARY TABLE IF NOT EXISTS _tempComputers 
		(INDEX CombinedId (ComputerId, ClientId))
			SELECT 
				ComputerId, 
				ClientId, 
				LastContact,
				IF(WindowsUpdate = '', NULL, IF(WindowsUpdate LIKE '%/%', STR_TO_DATE(WindowsUpdate, '%m/%d/%Y %H:%i:%s'), STR_TO_DATE(WindowsUpdate, '%Y-%m-%d %H:%i:%s'))) AS LastScanned,
				STR_TO_DATE('0000-00-00 00:00:00', '%Y-%m-%d %H:%i:%s') AS LastPatched 
			FROM Computers;
		#Now that LastScanned is a DateTime we can easily check for computers that have never had a Windows Update scan
		UPDATE _tempComputers SET LastScanned = NULL WHERE LastScanned = '1901-01-01 01:01:01';
		#Update temp table to have the real LastPatched now that we are not using the computers table.
		#We do not use _tempHotfix because we need all approval types, not just type 2
		UPDATE _tempComputers AS tc
		JOIN (SELECT ComputerId, MAX(Hotfix.FinishedDate) AS LastPatched FROM Hotfix GROUP BY ComputerId) hf ON hf.ComputerId = tc.ComputerId
		SET tc.LastPatched = hf.LastPatched;
		#Create temporary copy of hotfix to reduce time spent locking the hotfix table NOTE: Approved = 2 is used here so we do not need it on anything else
		CREATE TEMPORARY TABLE IF NOT EXISTS _tempHotfix SELECT ComputerId, HotfixId, Approved, OS, Installed, Failed FROM hotfix WHERE Approved = 2;
		#Add the index afterwards to also decrease the time the hotfix table is locked during its initial creation
		ALTER TABLE `_tempHotfix` ADD INDEX (`ComputerId`, `HotfixId`);
		#Create temporary copy of hotfix data. We use IsThirdParty=0 here so we do not need to do it on the other queries
		CREATE TEMPORARY TABLE IF NOT EXISTS _tempHotfixData
		(
			PRIMARY KEY (`HotFixID`,`OS`),
			INDEX `Severity` (`Severity`),
			INDEX `Title` (`Title`),
			INDEX `CVSS` (`CVSS`)
		)
		SELECT HotfixId, OS, CVSS, Date_Added, KBID, Title, CategoryName, Severity FROM HotfixData WHERE IsThirdParty = 0;
		################Patch Compliance Stats#########################################
		#Create table to hold collected ComputerPatchComplianceStats	
		CREATE TABLE IF NOT EXISTS ComputerPatchComplianceStats_NEW LIKE ComputerPatchComplianceStats;
		TRUNCATE TABLE ComputerPatchComplianceStats_NEW;
		#Do the insert
		INSERT INTO ComputerPatchComplianceStats_NEW 
		(
		   `ComputerId`, `OS`, `TotalApproved`, `Compliance`, `LastScanned`, `LastPatched`, `LastContact`, `NoPatchInventory`, `PendingReboot`, `CriticalInstallCount`, `CriticalFailedCount`, 
		   `CriticalNotAttemptedCount`, `ImportantInstallCount`, `ImportantFailedCount`, `ImportantNotAttemptedCount`, `ModerateInstallCount`, `ModerateFailedCount`, `ModerateNotAttemptedCount`, `LowInstallCount`, 
		   `LowFailedCount`, `LowNotAttemptedCount`, `UnspecifiedInstallCount`, `UnspecifiedFailedCount`, `UnspecifiedNotAttemptedCount`, `CvssHighInstallCount`, `CvssHighFailedCount`, `CvssHighNotAttemptedCount`, 
		   `CvssMediumInstallCount`, `CvssMediumFailedCount`, `CvssMediumNotAttemptedCount`, `CvssLowInstallCount`, `CvssLowFailedCount`, `CvssLowNotAttemptedCount` 
		)
		(
		  SELECT Computers.ComputerID, Hotfix.OS, 
			 COUNT(Hotfix.Hotfixid) AS TotalApproved, 
			 SUM(Installed) / SUM(IF(Approved = 2, 1, 0)) AS Compliance, 
			 Computers.LastScanned AS LastScanned, 
			 Computers.LastPatched AS LastPatched, 
			 Computers.LastContact, 
			 ComputerPatchingStats.NoPatchInventory, 
			 ComputerPatchingStats.PendingReboot, 
			 SUM(IF(Severity = 'Critical', Hotfix.Installed, 0)) AS CriticalInstallCount,  
			 SUM(IF(Severity = 'Critical', Abs(Hotfix.Failed), 0)) AS CriticalFailedCount, 
			 SUM(IF(Severity = 'Critical', IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS CriticalNotAttemptedCount,  
			 SUM(IF(Severity = 'Important', Hotfix.Installed, 0)) AS ImportantInstallCount,  
			 SUM(IF(Severity = 'Important', ABS(Hotfix.Failed), 0)) AS ImportantFailedCount, 
			 SUM(IF(Severity = 'Important', IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS ImportantNotAttemptedCount,  
			 SUM(IF(Severity = 'Moderate', Hotfix.Installed, 0)) AS ModerateInstallCount,  
			 SUM(IF(Severity = 'Moderate', ABS(Hotfix.Failed), 0)) AS ModerateFailedCount,  
			 SUM(IF(Severity = 'Moderate', IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS ModerateNotAttemptedCount, 
			 SUM(IF(Severity = 'Low', Hotfix.Installed, 0)) AS LowInstallCount,  
			 SUM(IF(Severity = 'Low', ABS(Hotfix.Failed), 0)) AS LowFailedCount,  
			 SUM(IF(Severity = 'Low', IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS LowNotAttemptedCount, 
			 SUM(IF(Severity = '', Hotfix.Installed, 0)) AS UnspecifiedInstallCount,  
			 SUM(IF(Severity = '', ABS(Hotfix.Failed), 0)) AS UnspecifiedFailedCount,  
			 SUM(IF(Severity = '', IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS UnspecifiedNotAttemptedCount, 
			 SUM(IF(CVSS >= 7.0, Hotfix.Installed, 0)) AS CvssHighInstallCount,  
			 SUM(IF(CVSS >= 7.0, ABS(Hotfix.Failed), 0)) AS CvssHighFailedCount,  
			 SUM(IF(CVSS >= 7.0, IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS CvssHighNotAttemptedCount, 
			 SUM(IF(CVSS >= 4.0 AND CVSS < 7.0, Hotfix.Installed, 0)) AS CvssMediumInstallCount,  
			 SUM(IF(CVSS >= 4.0 AND CVSS < 7.0, ABS(Hotfix.Failed), 0)) AS CvssMediumFailedCount,  
			 SUM(IF(CVSS >= 4.0 AND CVSS < 7.0, IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS CvssMediumNotAttemptedCount, 
			 SUM(IF(CVSS < 4.0, Hotfix.Installed, 0)) AS CvssLowInstallCount,  
			 SUM(IF(CVSS < 4.0, ABS(Hotfix.Failed), 0)) AS CvssLowFailedCount,  
			 SUM(IF(CVSS < 4.0, IF(Hotfix.Failed = 0 AND Hotfix.Installed = 0, 1, 0), 0)) AS CvssLowNotAttemptedCount 
		  FROM _tempComputers AS Computers
		  JOIN ComputerPatchingStats ON Computers.ComputerID = ComputerPatchingStats.ComputerID  
		  JOIN _tempHotfix AS Hotfix ON Hotfix.ComputerID = Computers.ComputerID  
		  JOIN _tempHotfixData AS HotfixData ON HotfixData.HotfixId = Hotfix.HotfixId AND HotfixData.OS = Hotfix.OS
		  GROUP BY ComputerID
		);
		#If data was successfully collected by query, go ahead and rename the table
		RENAME TABLE ComputerPatchComplianceStats TO ComputerPatchComplianceStats_OLD;
		#Rename the new table to the proper name
		RENAME TABLE ComputerPatchComplianceStats_NEW TO ComputerPatchComplianceStats;
		################Non Compliant Patches#########################################
		#Create table to hold collected NonCompliantPatches info	
		CREATE TABLE IF NOT EXISTS NonCompliantPatches_NEW LIKE NonCompliantPatches;
		TRUNCATE TABLE NonCompliantPatches_NEW;
		#Gather non compliant patch info into temp table
		INSERT INTO NonCompliantPatches_NEW 
		(
		   `ClientID`, `HotfixID`, `OS`, `KBID`, `Title`, `CategoryName`, `Severity`, `CvssScore`, `ReleaseDate`, `TotalInstalled`, `TotalFailed`, `TotalNotAttempted` 
		)
		(
		  SELECT 
			 Computers.ClientID, Hotfix.HotfixID, Hotfix.OS, HotfixData.KBID, HotfixData.Title, HotfixData.CategoryName,
			 IF(HotfixData.Severity = '', 'Unspecified', HotfixData.Severity) AS Severity,
			 HotfixData.CVSS AS CvssScore, 
			 HotfixData.Date_Added AS ReleaseDate, 
			 SUM(Hotfix.Installed) AS TotalInstalled, 
			 SUM(ABS(Hotfix.Failed)) AS TotalFailed, 
			 SUM(IF(NOT Hotfix.Failed AND NOT Hotfix.Installed, 1, 0)) AS TotalNotAttempted 
		  FROM 
		  _tempComputers AS Computers
		  JOIN _tempHotfix AS Hotfix USING (ComputerId) 
		  JOIN _tempHotfixData AS HotfixData ON hotfix.HotfixID = HotfixData.HotfixID AND hotfix.OS = HotfixData.OS
		  GROUP BY Computers.ClientID, Hotfix.HotfixID, Hotfix.OS 
		  HAVING SUM(IF(NOT installed, 1, 0)) > 0
		);
		#If data was successfully collected by query, go ahead and rename the table
		RENAME TABLE NonCompliantPatches TO NonCompliantPatches_OLD;
		#Rename the new table to the proper name
		RENAME TABLE NonCompliantPatches_NEW TO NonCompliantPatches;
	COMMIT;
	#Clean up temp and unneaded tables
	DROP TABLE IF EXISTS NonCompliantPatches_OLD;
	DROP TABLE IF EXISTS NonCompliantPatches_NEW;
	DROP TABLE IF EXISTS ComputerPatchComplianceStats_NEW;
	DROP TABLE IF EXISTS ComputerPatchComplianceStats_OLD;
	DROP TABLE IF EXISTS _tempComputers;	
	DROP TABLE IF EXISTS _tempHotfix;
	DROP TABLE IF EXISTS _tempHotfixData;
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; #Set isolation level back to default
	END

sp_RunPatchReportCalculations

Parameters

Definition