This content has been marked as final. Show 4 replies
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
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
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.
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 --