labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- ERROR
  ROLLBACK;
END;
START TRANSACTION;
	-- Note: If Category 2(Workstations) or Category 3(Servers) change it needs updated here as well.
	-- Delete PatchFilterItems no longer in the system for Servers and Workstations Categories
	DELETE `PatchFilterItems` FROM `PatchFilterItems`
	LEFT JOIN hotfixdata hd ON patchfilteritems.name = hd.os
	WHERE hd.os IS NULL 
	AND
	((`PatchFilterItems`.`Category` = 3 AND (`PatchFilterItems`.`Name` LIKE '%server%' OR `PatchFilterItems`.`Name` LIKE '%2011%'))
	OR
	(`PatchFilterItems`.`Category` = 2 AND (`PatchFilterItems`.`Name` NOT LIKE '%server%' AND `PatchFilterItems`.`Name` NOT LIKE '%2011%')));
	-- Add PatchFilterItems for Servers and Workstations from the HotfixData table
	 INSERT IGNORE INTO `PatchFilterItems` (`Name`, `Category`, `Value`)
	(
	  SELECT DISTINCT(`OS`) AS `Name`, IF(`OS` LIKE '%server%' OR `HotfixData`.`OS` LIKE '%2011%', 3, 2) AS `Category`, `OS` AS `Value` FROM `HotfixData`
	  LEFT JOIN `PatchFilterItems` pfi ON `pfi`.`Name` = `HotfixData`.`OS`
	  WHERE
	  `HotfixData`.`OS` != '' AND 
	  (
	     (`HotfixData`.`OS` LIKE '%server%' OR `HotfixData`.`OS` LIKE '%2011%')
	     OR
	     (`HotfixData`.`OS` NOT LIKE '%server%' AND `HotfixData`.`OS` NOT LIKE '%2011%')  
	  ) AND `pfi`.`Name` IS NULL
	);
COMMIT;
END

sp_UpdateOperatingSystemPatchFilterItems

Parameters

Definition