WSUS maintenance for ConfigMgr

So it was my turn to face problems. I had neglected the obstacle for months excusing myself that everything was still working wonders, until today.

Following screenshot was the reality of my WSUS console when trying to run the server cleanup wizard:

wsuserror

Add so the struggle to solve the problem began and following is the facts and solution:

  • I’m using WSUS running on the internal database in Windows (WID), so I downloaded and installed SQL Server 2014 Management Studio on my server running WSUS
  • Connected to \\.\pipe\microsoft##WID\tsql\query in the Connect to Server window

studioconnecting

  • Ran the following two SQL scripts. My WSUS DB was so bloated that the reindex script from the scripting guys didn’t cut it. When that happens, the deal usually is that you have to delete updates manually directly in the DB.
    Fortunately for me, I found below script to my aid. The script runs the stored procedure EXEC spGetObsoleteUpdatesToCleanup and then deletes the updates. Beware, running these scripts may take several hours depending on the specs of the server and the amount of updates)
  1. DeleteObsoleteUpdates
  2. WSUSreindex

This is a snip of the two scripts showing directly in Management Studio, saved for later use as .sql.

studioscripts

Lesson learned:

Maintaining the SUSDB is important, and is not just something you setup and leave even though running it integrated with ConfigMgr.

**Will update this post on how I’m going to automate this in the future.

Deploying software targeting user AND machine context

So, are you ever in need of deploying software targeting computers , but also in need of pushing config files belonging to the same software targeting the users profile?

You can do that using ConfigMgr, and this is how I do it.

In this example I was messing around with Ad Block for IE. To avoid some annoying first run popups, you have to make sure some config files exists in the users profile. You can push those files directly to the logged on user (or any user logging on the same computer) immediately after installing the targeted software.

  • First, create the ad block (or whatever software you’d like) as a package in configmgr. This is pretty standard, and is not explained in this post.
  • Secondly, create another package consisting of the files going into the users profile. The files for Ad Block is automatically created during the first run of IE after the installation, and consists of following files:

adblockfiles

  • Thirdly, create a batch script running following command and put it next to the files going into the users profile: xcopy “%~dp0Files\*.*” “%userprofile%\AppData\LocalLow\Adblock Plus for IE\” /E /S /Y /Q. Notice I have the files in a sub folder to the actual .cmd:

adblockfiles2

  • Distribute to the distribution point as usual and create a program running the CopyFiles.cmd running in user context:

adblockfiles3 adblockfiles4

  • Edit the program running the software created in the first step and make the following changes:

adblockfiles5

  • Finally, deploy both the program running the actual software, and the program copying the files to the users profile to the same collection of computers. The program running the copy of the files, can be deployed as available:

adblockfiles6

All of above will result in the actual software being installed (system context) AND the files being copied into the logged on users profile (user context) in one go.