labtech
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_DoApplyManagedServices
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