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
)