sp_DoApplyManagedServices

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