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.
Lead Program Manager, WSUS
— Find computers within a target group that need updates
— which have been approved for install for at least N days
SELECT@TargetGroup = ‘Test Machines’
SELECT@Days = 7
— Find all computers in the given @TargetGroup
INNERJOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId
INNERJOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId
vComputerTargetGroup.Name = @TargetGroup
— And only select those for which an update is approved for install, the
— computer status for that update is either 2 (not installed), 3 (downloaded),
— 5 (failed), or 6 (installed pending reboot), and
— the update has been approved for install for at least @Days
INNERJOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateApprovalId = vUpdateEffectiveApprovalPerComputer.UpdateApprovalId
INNERJOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId
vUpdateEffectiveApprovalPerComputer.ComputerTargetId = vComputerTarget.ComputerTargetId
ANDvUpdateApproval.Action = ‘Install’
ANDvUpdateInstallationInfoBasic.UpdateId = vUpdateApproval.UpdateId
ANDvUpdateInstallationInfoBasic.State in (2, 3, 5, 6)
ANDDATEDIFF (day, vUpdateApproval.CreationDate, CURRENT_TIMESTAMP) > @Days