Customers at TechEd asked how to generate a compliance report that shows computers that are out of compliance against updates that have been approved for install to them for N days. This can't be done in the public UI because it has no ability to specify the length of time an update has been approved, or to scope to just updates approved-for-install to that computer. However it can be done in WSUS 3 and later via our public SQL views.

Information on how to use our public DB views can be found here: http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx. As described in the article, to do this with the Windows Internal Database with WSUS, one first downloads SQL Studio Express Edition, and then connects to the DB using Windows Auth and the connection string "\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query".

Anyways, here's a query that accomplishes this. This query has not been tried on large DBs yet and may have  performance challenges on such DBs. We will probably update this blog next week with a tweaked version of this query that performs better on large systems, and that lists the particular updates that are needed by the computer (and also says if they are needed just because a reboot is needed). But so many folks asked about how to use the public views to do this type of query last week that we wanted to show how it is done.

-Marc Shepard

Lead Program Manager, WSUS

-- Find computers within a target group that need updates

USE SUSDB

DECLARE @TargetGroup nvarchar(30)

DECLARE @Days int

SELECT @TargetGroup = 'Test Machines'

SELECT @Days = 7

SELECT vComputerTarget.Name

FROM PUBLIC_VIEWS.vComputerGroupMembership

INNER JOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId

INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId

WHERE

vComputerTargetGroup.Name = @TargetGroup

-- And only select those for which an update is approved for install, the

AND EXISTS

(

select * from

PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer

INNER JOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateApprovalId = vUpdateEffectiveApprovalPerComputer.UpdateApprovalId

INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId

WHERE

vUpdateEffectiveApprovalPerComputer.ComputerTargetId = vComputerTarget.ComputerTargetId

AND vUpdateApproval.Action = 'Install'

AND vUpdateInstallationInfoBasic.UpdateId = vUpdateApproval.UpdateId

AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)

AND DATEDIFF (day, vUpdateApproval.CreationDate, CURRENT_TIMESTAMP) > @Days

)

Good morning folks,

A couple of weeks ago, I posted about Windows Vista Service Pack 1’s availability to WSUS. In that post, we mentioned that you should install the WinVerifyTrust update (KB 938759) if you are running your WSUS server on a Windows 2003 server to prevent Windows Vista SP1 from being continually re-downloaded to the server once the service pack is released to WSUS. 

Today, we’d like to let you know that, in order to make sure all customers are well protected, we will be marking this particular update as mandatory, which means it will appear in the Updates/WSUS Updates node of the Admin console, and will be auto-approved by default (since the “Advanced/WSUS Updates” checkbox is checked by default in Options/Automatic Approvals).

After this update has been marked as mandatory to WSUS for a short period of time, we will release Windows Vista Service Pack 1 to WSUS. We believe that this change will help make sure all customers have a good experience once SP1 is released to WSUS, and will also ensure that WSUS can be used to deploy other large updates we may release in the future.

Thank you for your support.

Cecilia Cole

WSUS Program Manager

Good morning!

Now that you are all back from TechEd, I wanted to address an issue that a couple of folks contacted us about regarding some client/server synchronization issues they were experiencing in their WSUS environment.

Issue:

Computers that have Office 2003 or components of Office 2003 installed fail to run a detection against a WSUS server that has the latest Office updates. This prevents the computers from receiving any updates from the WSUS server.

Affected products:

·         WSUS 3.0 Server (RTM or SP1)

·         Computers with Office 2003 or components of Office 2003 installed.

Symptoms:

Automatic updates fails detection against the WSUS server.

1.       On the client side, the automatic updates log file (%windir%\WindowsUpdate.log) has an entry similar to below during the detection phase:

2008-06-13    19:59:53:383  788   ee4    PT     +++++++++++  PT: Synchronizing server updates  +++++++++++

2008-06-13    19:59:53:383  788   ee4    PT       + ServiceId = {3DA21691-E39D-4DA6-8A4B-B43877BCB1B7}, Server URL = http://<WSUS Server>/ClientWebService/client.asmx

2008-06-13    19:59:56:617  788   ee4    PT     WARNING: SyncUpdates failure, error = 0x8024400E, soap client error = 7, soap error code = 400, HTTP status code = 200

2008-06-13    19:59:56:617  788   ee4    PT     WARNING: SOAP Fault: 0x000190

2008-06-13    19:59:56:617  788   ee4    PT     WARNING:     faultstring:Fault occurred

2008-06-13    19:59:56:617  788   ee4    PT     WARNING:     ErrorCode:InternalServerError(5)

2008-06-13    19:59:56:617  788   ee4    PT     WARNING:     Message:(null)

2008-06-13    19:59:56:617  788   ee4    PT     WARNING:     Method:"http://www.microsoft.com/SoftwareDistribution/Server/ClientWebService/SyncUpdates"

2008-06-13    19:59:56:617  788   ee4    PT     WARNING:     ID:c0a7445f-b989-43fa-ac20-11f8ca65fa8c

2.       On the WSUS server, the WSUS log file (%Program Files%\Update Services\Log Files\SoftwareDistribution.log) has an entry similar to below, which one can find by searching on the ID obtained from the client log:

2008-06-14 02:59:57.642 UTC Error  w3wp.12       ClientImplementation.SyncUpdates       System.ArgumentException: Item has already been added. Key in dictionary: '8862'  Key being added: '8862'

   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)

   at System.Collections.Hashtable.Add(Object key, Object value)

   at Microsoft.UpdateServices.Internal.ClientImplementation.GetSyncInfo(DataAccess dataAccess, Hashtable stateTable, Hashtable deploymentTable, Boolean haveGroupsChanged, Boolean doChunking)

   at Microsoft.UpdateServices.Internal.ClientImplementation.SoftwareSync(DataAccess dataAccess, UnencryptedCookieData cookieData, Int32[] installedNonLeafUpdateIds, Int32[] leafUpdateIds, Boolean haveGroupsChanged, Boolean expressQuery)

   at Microsoft.UpdateServices.Internal.ClientImplementation.SyncUpdates(Cookie cookie, SyncUpdateParameters parameters)

   at Microsoft.UpdateServices.Internal.ClientImplementation.SyncUpdates(Cookie cookie, SyncUpdateParameters parameters)

   at Microsoft.UpdateServices.Internal.Client.SyncUpdates(Cookie cookie, SyncUpdateParameters parameters)

       <lines removed>

2008-06-14 02:59:57.642 UTC Warning       w3wp.12       SoapUtilities.CreateException     ThrowException: actor = http://wsusebc/ClientWebService/client.asmx, ID=c0a7445f-b989-43fa-ac20-11f8ca65fa8c, ErrorCode=InternalServerError, Message=, Client=?

Root Cause:

A recent revision to the ‘Office 2003 Service Pack 1’ update has resulted in some WSUS 3.0 servers syncing

the revised update to enter an inconsistent state with respect to the update’s approvals. When computers with products related to Office 2003 communicate with such a server, the Web service is unable to process the approvals resulting in the detection failure.

Workaround:

In order to reset the approvals to a consistent state on the WSUS server, follow these steps from the WSUS Administration Console:

1.       Find the Office 2003 Service Pack 1 update in the updates list, UpdateID: D359F493-0AAD-43FA-AF5C-6763326CD98F. This may involve changing the Status and Approval filters. Set Status to Any and Approval to Declined; if you still do not see the update, set Approval to Any Except Declined.

2.       Perform the following steps:

a.        Ensure the update is already declined.

                                                                                       i.      If the update is not already declined, right-click on the update and then click Decline in the shortcuts menu.

b.       Cause the update not to be declined.

                                                                                       i.            Right-click the update and then click Approve in the shortcuts menu.

                                                                                     ii.            In the Approve Updates dialog that opens, just click OK without making any changes to the approval settings.

c.        Decline the update.

                                                                                       i.       Right-click the update and then click Approve in the shortcuts menu.

                                                                                     ii.       In the Approve Updates dialog that opens, just click OK. Dismiss the Approval Progress dialog that appears.

The computers that were failing detection will now successfully complete detection against the server and receive any applicable updates.

Note: If you have a hierarchy of WSUS servers, these steps must be performed on each server, starting with the top-level server. If one of the servers is a replica child, one must first change it to be autonomous, then perform the steps above, then change it back to being a replica. This can be done from the Options/Update Source and Proxy Server dialog box.

Thank you.

Cecilia Cole

WSUS Program Manager