4 Replies Latest reply on Dec 22, 2014 8:16 AM by Landon Winburn

    Moving fully deployed machines to a different Deployment Group

    gregf SupportEmployee
      A recent request:

      Can anyone help me produce a script which enumerates the machines in a deployment group and if all packages are installed then move it to another group (and the icing on the cake would be then to do a poll now once it had moved)?

        • 1. Re: Moving fully deployed machines to a different Deployment Group
          gregf SupportEmployee
          Not sure about the Poll Now but here's the SQL to do the move

          USE [ManagementServer]
          
          DECLARE @OldGroup nvarchar(256)
          DECLARE @NewGroup nvarchar(256)
          SET @OldGroup = 'Your Source Group'
          SET @NewGroup = 'Your Target Group'
          
          CREATE TABLE #MachinesToMove (MachinePK uniqueidentifier)
          
          INSERT INTO #MachinesToMove
          SELECT [MachineKey]
            FROM [MachinesSummaryViewConsole] msvc
            WHERE msvc.GroupName = @OldGroup
            AND msvc.Deployed = 100
          
          DECLARE @NewGroupFK uniqueidentifier
          SELECT @NewGroupFK = GroupPK FROM Groups WHERE Name = @NewGroup
          
          
          UPDATE Mac
           SET ModifiedTime = GETUTCDATE(),
            GroupFK = @NewGroupFK
           FROM [Machines] AS Mac
           INNER JOIN #MachinesToMove ON Mac.MachinePK = #MachinesToMove.MachinePK
          
          DROP TABLE #MachinesToMove
          
          
          • 2. Re: Moving fully deployed machines to a different Deployment Group
            Chrisb1 Employee
            Don't see why you'd need a temp table in this one.  No need to throw any indexes or anything and just doing a direct join in the update seems to work fine in my testing.

            DECLARE @OldGroup nvarchar(256)
            DECLARE @NewGroup nvarchar(256)
            SET @OldGroup = 'Your Source Group'
            SET @NewGroup = 'Your Target Group'
            
            DECLARE @NewGroupFK uniqueidentifier
            SELECT @NewGroupFK = GroupPK FROM Groups WHERE Name = @NewGroup
            
            
            UPDATE Mac
             SET ModifiedTime = GETUTCDATE(),
              GroupFK = @NewGroupFK
             FROM [Machines] Mac
             INNER JOIN [MachinesSummaryViewConsole] msvc ON Mac.MachinePK = msvc.MachineKey
             WHERE
              msvc.GroupName = @OldGroup
              AND msvc.Deployed = 100
            
            
            • 3. Re: Moving fully deployed machines to a different Deployment Group
              gregf SupportEmployee

              chris.burbank wrote:

               

              Don't see why you'd need a temp table in this one.



              You don't - it was just copy-and-paste laziness when combining my query with someone else's code to do the move.
              • 4. Re: Moving fully deployed machines to a different Deployment Group
                Landon Winburn Expert
                Here is one I just did for a customer that only moves machines that fall into the (Default) group to the proper groups as defined by the membership rules. If a user moves a computer from "Prod" to "Test" for example and that machine doesn't match the membership rules for test then they stick. Again, only moves machines that fall through the cracks and wind up in the default group.

                -- start of T-SQL script --
                
                /* THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
                ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
                THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
                PARTICULAR PURPOSE.     
                
                IMPORTANT: Please take care when executing this script on a live database. 
                It is recommended that a full database backup is first performed.*/
                
                -- Script for moving all machines in groups with discovery enabled to the group they 'should' be discovered in 
                
                IF OBJECT_ID('tempdb..#MoveMachines') IS NOT NULL
                DROP TABLE #MoveMachines
                GO
                
                CREATE TABLE #MoveMachines
                (MachinePK uniqueidentifier, ExpectedGroupFK uniqueidentifier)
                
                INSERT INTO #MoveMachines
                SELECT m.MachinePK, d.ExpectedGroupFK
                       FROM [Machines] m
                       JOIN [DiscoveredMachines] d
                       ON m.ObjectGuid = d.ADObjectGuid
                       CROSS APPLY (SELECT GroupPK from Groups where Name = '(Default)') dg
                       WHERE d.ExpectedGroupFK != m.GroupFK
                       AND d.UserSpecified = 0
                       AND dg.GroupPK = m.GroupFK
                
                
                -- Select statement to view the machines that will be updated (need to uncomment the UPDATE statement below)
                SELECT NetBiosName, DNS, DistinguishedName, gm.Name AS 'Current Group', gd.Name AS 'Discovered Group', LastPollTime, ObjectGuid
                FROM Machines m
                INNER JOIN #MoveMachines #m ON m.MachinePK = #m.MachinePK
                INNER JOIN [Groups] gm ON m.GroupFK = gm.GroupPK
                INNER JOIN [Groups] gd ON #m.ExpectedGroupFK = gd.GroupPK
                
                --Uncomment the following line to move the computers
                --UPDATE [Machines] SET ModifiedTime = GETUTCDATE(), GroupFK = ExpectedGroupFK FROM Machines m INNER JOIN #MoveMachines #m ON m.MachinePK = #m.MachinePK
                
                DROP TABLE #MoveMachines 
                
                -- End of T-SQL script --