Baseline compliance report, using public WSUS views

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: https://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

-- which have been approved for install for at least N days

USE

SUSDB

DECLARE

@TargetGroup nvarchar(30)

DECLARE

@Days int

SELECT

@TargetGroup = 'Test Machines'

SELECT

@Days = 7

-- Find all computers in the given @TargetGroup

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

-- 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

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

)