Mareo Code

When recently almost 1500 new computers reported in our WSUS I thought, damn, must to move them from Unassigned Computers group into suitable groups (notebooks, desktops etc.) for easiest management. Unfortunatelly it is a big pain on ass (we don’t use client targeting option in our environment) because of:

- there is no drag & drop in WSUS console (!)

- if you select a lot of computers manually and choose from menu to move them into another group the WSUS Console MMC often start to freeze :/

So.. I look on this 1500 computers and was really in bad mood because of necessity to spend few hours on manually selection (by Ctrl+click) computers and choosing move command from context menu… H+O+R+R+I+B+L+E ;)

Fortunately we have access to WSUS database, so after short investigation and few tries I wrote SQL code which move all computers (selected by you through computer name pattern)  from Unassigned Computers group into group of your choice. After few minutes and few modifications (computer names pattern) the task is done.

I use it on WSUS 3.0 SP1 database. The code is selfexplained I hope. Enter your data in lines with ++++++ Change it to your needs ++++++ comment and run in WSUS database context.

SQL code below.

-- =============================================
-- Author:        Marek Sliwinski ( http://code.mareoblo.pl )
-- Create date: 2009-03-04
-- Description:    WSUS 3.0 SP1
--                Move all computers with selected name (pattern)
--                from UNASSIGNED COMPUTERS group into selected by you
-- =============================================
DECLARE
     @targetGroupID uniqueidentifier
    ,@computerID    nvarchar(256)
    ,@counter        int

-- to show info on the end how many computers have been added
SET @counter = 0

/* Help view, show all WSUS Groups
SELECT
    *
FROM
    tbTargetGroup
*/
SELECT
    @targetGroupID = TargetGroupID
FROM
    tbTargetGroup
WHERE
    [Name] = 'NAME OF YOUR TARGET GROUP' -- ++++++ Change it to your needs ++++++

DECLARE unassigned_computers_list_cursor CURSOR FOR

SELECT
     ComputerID
    --,FullDomainName
    --,a.TargetID
FROM
    tbComputerTarget as a
    INNER JOIN tbTargetInTargetGroup as b
    ON a.TargetID = b.TargetID
WHERE
    FullDomainName LIKE '%computers pattern%'  -- computers pattern, ++++++ Change it to your needs ++++++
    AND TargetGroupID = 'B73CA6ED-5727-47F3-84DE-015E03F6A88A' -- this is Unassigned Computers group ID, don't change

OPEN unassigned_computers_list_cursor

FETCH NEXT FROM unassigned_computers_list_cursor INTO @computerID

WHILE @@FETCH_STATUS = 0
    BEGIN
        -- add computer to target group
        EXEC spAddComputerToTargetGroup @targetGroupID, @computerID

        SET @counter = @counter + 1

        FETCH NEXT FROM unassigned_computers_list_cursor INTO @computerID
    END

CLOSE unassigned_computers_list_cursor
DEALLOCATE unassigned_computers_list_cursor

print 'Number of computers moved to new group: ' + CAST(@counter as varchar)