labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
     ROLLBACK;
     DROP TABLE IF EXISTS HotfixPatchSummaryDataTemp, HotfixPatchSummaryDataOld;
     RESIGNAL;
END; 
START TRANSACTION; 
DROP TABLE IF EXISTS HotfixPatchSummaryDataTemp, HotfixPatchSummaryDataOld; 
CREATE TABLE IF NOT EXISTS HotfixPatchSummaryDataTemp LIKE HotfixPatchSummaryData; 
INSERT INTO HotfixPatchSummaryDataTemp 
(HotfixId,OS,PatchesStatCurrentSuccess,PatchesStatCurrentFailed,PatchesStatCurrentUnattempted,PatchesCurrentCompliance,PatchApprovalCount,Last_Updated) 
( SELECT 
     Hotfix.HotfixId, Hotfix.OS, 
     SUM(IF(HotfixData.IsThirdParty = 0, Installed, IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion >= ProductVersionControl.LatestVersion, 1, 0), IF(Hotfix.InstalledVersion = Hotfix.Version, 1, 0)))) AS PatchesStatCurrentSuccess, 
     SUM(IF(HotfixData.IsThirdParty = 0, IF(Installed = 1, 0, ABS(Failed)), IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion >= ProductVersionControl.LatestVersion, 0, ABS(Failed)), IF(Hotfix.InstalledVersion = Hotfix.Version, 0, ABS(Failed))))) AS PatchesStatCurrentFailed, 
     SUM(IF(HotfixData.IsThirdParty = 0, IF(Installed = 0 AND Failed = 0, 1, 0), IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion < ProductVersionControl.LatestVersion AND Failed = 0, 1, 0), IF(Hotfix.InstalledVersion != Hotfix.Version AND Failed = 0, 1, 0)))) AS PatchesStatCurrentUnattempted, 
     IFNULL(ROUND(SUM(IF(HotfixData.isThirdParty = 0, Installed, IF(Hotfix.Version = '', IF(Hotfix.InstalledVersion >= ProductVersionControl.LatestVersion, 1, 0), IF(Hotfix.InstalledVersion = Hotfix.Version, 1, 0)))) / COUNT(Approved) * 100, 2), 0) AS PatchesCurrentCompliance, 
     (SELECT COUNT(ApprovalPolicyId) FROM PatchApprovalSettings pas WHERE Approval > 0 AND pas.HotfixID = Hotfix.HotfixID AND IF(HotfixData.IsThirdParty = 0, pas.OS = Hotfix.OS, pas.OS = '')) AS PatchApprovalCount, 
     Hotfix.Last_Date AS Last_Updated 
  FROM Hotfix 
  LEFT JOIN ProductVersionControl ON HotFix.HotfixId = ProductVersionControl.LTProductKey 
  JOIN HotfixData ON Hotfix.HotfixId = HotfixData.LTProductKey AND 
     (Hotfix.OS = HotfixData.OS OR (HotfixData.IsThirdParty = 1 AND 
     IF(Hotfix.Version = '', HotfixData.Version = ProductVersionControl.LatestVersion, Hotfix.Version = HotfixData.Version))) 
  WHERE Last_Date > (SELECT IFNULL(MAX(Last_Updated), STR_TO_DATE('0000-00-00 00:00:00', '%Y-%m-%d %T')) FROM HotfixPatchSummaryData) AND 
     Approved = 2 AND (HotfixData.IsThirdParty = 0 OR Hotfix.Action = 1 OR Hotfix.InstalledVersion > '') 
  GROUP BY HotfixData.LTProductKey, IF(HotfixData.IsThirdParty = 0, HotfixData.OS, '') 
) 
ON DUPLICATE KEY UPDATE 
   PatchesStatCurrentSuccess = VALUES(PatchesStatCurrentSuccess), 
   PatchesStatCurrentFailed = VALUES(PatchesStatCurrentFailed), 
   PatchesStatCurrentUnattempted = VALUES(PatchesStatCurrentUnattempted), 
   PatchesCurrentCompliance = VALUES(PatchesCurrentCompliance), 
   PatchApprovalCount = VALUES(PatchApprovalCount), 
   Last_Updated = VALUES(Last_Updated); 
RENAME TABLE HotfixPatchSummaryData TO HotfixPatchSummaryDataOld, HotfixPatchSummaryDataTemp TO HotfixPatchSummaryData; 
DROP TABLE HotfixPatchSummaryDataOld; 
COMMIT; 
END

sp_CalculateHotfixPatchSummaryData

Parameters

Definition