labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
	DROP TABLE IF EXISTS `mngd_svc_services_new`;

CREATE TABLE IF NOT EXISTS `mngd_svc_services_new` (
	  `ServiceDefinitionID` int(11) NOT NULL Default '0',
	  `TargetID` int(11) NOT NULL Default '0',
	  `TargetTypeFlags` int(11) NOT NULL Default '0',
	  `GroupID` int(11) NOT NULL Default '0',
	  `AppliedCount` int(11) NOT NULL Default '1',
	  PRIMARY KEY (`ServiceDefinitionID`, `TargetID`, `TargetTypeFlags`)
	);
	CREATE INDEX `GroupID` ON `mngd_svc_services_new` (`GroupID`);

INSERT IGNORE INTO `mngd_svc_services_new`(Select `mngd_svc_servicexrefs`.`ServiceDefinitionID`, `subgroupwchildren`.`ComputerID` as 'TargetID', '2' as 'TargetTypeFlags', `subgroupwchildren`.`GroupID`, `mngd_svc_servicexrefs`.`ApplyCount` as 'AppliedCount' 
	from `subgroupwchildren` 
	LEFT JOIN `mastergroups` on (`subgroupwchildren`.`GroupID` = `MasterGroups`.`GroupID`) 
	JOIN `mngd_svc_servicexrefs` on (`subgroupwchildren`.`GroupID` = `mngd_svc_servicexrefs`.`TargetID` AND `mngd_svc_servicexrefs`.`TargetTypeFlags`=128) 
	LEFT JOIN `mngd_svc_defs` on(`mngd_svc_defs`.`ServiceDefinitionID` = `mngd_svc_servicexrefs`.`ServiceDefinitionID`) 
	LEFT JOIN `searches` on (`mngd_svc_servicexrefs`.`SearchID` = `searches`.`SearchID` AND `subgroupwchildren`.`ComputerID` = `searches`.`ComputerID`) 
	WHERE (`mngd_svc_servicexrefs`.`SearchID` = 0 OR `mngd_svc_servicexrefs`.`SearchID` = `searches`.`SearchID`) AND `mngd_svc_defs`.`ServiceType`=2 
	ORDER BY `mastergroups`.`Priority`, `mastergroups`.`Master`=0, `mastergroups`.`Master`, `mastergroups`.`DEPTH` DESC, `mastergroups`.`GroupID` DESC);

INSERT IGNORE INTO `mngd_svc_services_new`(Select `mngd_svc_servicexrefs`.`ServiceDefinitionID`, `subgroupwchildrennetworkdevices`.`DeviceID` as 'TargetID', '32' as 'TargetTypeFlags', `subgroupwchildrennetworkdevices`.`GroupID`, `mngd_svc_servicexrefs`.`ApplyCount` as 'AppliedCount' 
	from `subgroupwchildrennetworkdevices` 
	LEFT JOIN `mastergroups` on (`subgroupwchildrennetworkdevices`.`GroupID` = `mastergroups`.`GroupID`) 
	JOIN `mngd_svc_servicexrefs` on (`subgroupwchildrennetworkdevices`.`GroupID` = `mngd_svc_servicexrefs`.`TargetID` AND `mngd_svc_servicexrefs`.`TargetTypeFlags`=128) 
	LEFT JOIN `mngd_svc_defs` on(`mngd_svc_defs`.`ServiceDefinitionID` = `mngd_svc_servicexrefs`.`ServiceDefinitionID`) 
	LEFT JOIN `searches` on (`mngd_svc_servicexrefs`.`SearchID` = `searches`.`SearchID` AND `subgroupwchildrennetworkdevices`.`DeviceID` = `searches`.`NetworkDeviceID`) 
	WHERE (`mngd_svc_servicexrefs`.`SearchID` = 0 OR `mngd_svc_servicexrefs`.`SearchID` = `searches`.`SearchID`) AND `mngd_svc_defs`.`ServiceType`=32 
	ORDER BY `mastergroups`.`Priority`, `mastergroups`.`Master`=0, `mastergroups`.`Master`, `mastergroups`.`DEPTH` DESC, `mastergroups`.`GroupID` DESC);

INSERT IGNORE INTO `mngd_svc_services_new`(Select `mngd_svc_servicexrefs`.`ServiceDefinitionID`, `subgroupwchildrencontacts`.`ContactID` as 'TargetID', '8192' as 'TargetTypeFlags', `subgroupwchildrencontacts`.`GroupID`, `mngd_svc_servicexrefs`.`ApplyCount` as 'AppliedCount' 
	from `subgroupwchildrencontacts` 
	LEFT JOIN `mastergroups` on (`subgroupwchildrencontacts`.`GroupID` = `mastergroups`.`GroupID`) 
	JOIN `mngd_svc_servicexrefs` on (`subgroupwchildrencontacts`.`GroupID` = `mngd_svc_servicexrefs`.`TargetID` AND `mngd_svc_servicexrefs`.`TargetTypeFlags`=128) 
	LEFT JOIN `mngd_svc_defs` on(`mngd_svc_defs`.`ServiceDefinitionID` = `mngd_svc_servicexrefs`.`ServiceDefinitionID`) 
	LEFT JOIN `searches` on (`mngd_svc_servicexrefs`.`SearchID` = `searches`.`SearchID` AND `subgroupwchildrencontacts`.`ContactID` = `searches`.`ContactID`) 
	WHERE (`mngd_svc_servicexrefs`.`SearchID` = 0 OR `mngd_svc_servicexrefs`.`SearchID` = `searches`.`SearchID`) AND `mngd_svc_defs`.`ServiceType`=8192 
	ORDER BY `mastergroups`.`Priority`, `mastergroups`.`Master`=0, `mastergroups`.`Master`, `mastergroups`.`DEPTH` DESC, `mastergroups`.`GroupID` DESC);

INSERT IGNORE INTO `mngd_svc_services_new`(Select `mngd_svc_servicexrefs`.`ServiceDefinitionID`, `mngd_svc_servicexrefs`.`TargetID` as 'TargetID', `mngd_svc_servicexrefs`.`TargetTypeFlags`, '0' as 'GroupID', `mngd_svc_servicexrefs`.`ApplyCount` as 'AppliedCount' 
	from `mngd_svc_servicexrefs` 
	WHERE `mngd_svc_servicexrefs`.`TargetTypeFlags`=4 OR `mngd_svc_servicexrefs`.`TargetTypeFlags`=8);

DROP TABLE IF EXISTS `mngd_svc_services`;
	ALTER TABLE `labtech`.`mngd_svc_services_new` RENAME TO  `labtech`.`mngd_svc_services`;
END

sp_DoApplyManagedServices

Parameters

Definition