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 -- 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 )
-- Find computers within a target group that need updates
-- which have been approved for install for at least N days
USE
DECLARE
SELECT
-- Find all computers in the given @TargetGroup
FROM
INNER
WHERE
vComputerTargetGroup
-- 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
(
select
PUBLIC_VIEWS
vUpdateEffectiveApprovalPerComputer
)
Hooray!!! finally some useful compliance reporting from WUS.
Why anyone thought that reporting on 'Needed' updates for machines that an update hadn't been approved was more useful than.....
Yet another set of foreign language updates synched with my WSUS server today (6/24) - with it set to only download English updates!
Why??? If I wanted Bulgarian or Estonian updates, I would have selected them!
I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.
Microsoft's Travis Plunk posted a modified version of Marc Shepard's WSUS SQL script for producing
The list is a little longer today because of not posting last week. Enjoy! Microsoft Advanced Windows
Hi all, We have released a fix for the synchronization issues that Cecilia described in http://blogs.technet.com/wsus/archive/2008/06/20/baseline-compliance-report-using-public-wsus-views.aspx
Need help with auto up dates on home computer
please contact me or e-mail with tel #for support
thanks
jim macpherson
jmacphe103@aol.com
What are the chances that these SQL queries appearing on this blog will be incorporated into the reports module of the next WSUS version?
Please remember that WSUS admins hail from all walsk of life: the gamut from full-bore IT administrators to power desktop users who get nominated as the IT person for their site/company. Using SQL to find this information out is really not the best way of doing it. Please provide more canned reports or more ad hoc report functionality in WSUS itself!
gtq Een plaatje zegt alles, toch ? uiz Het volledige rapport is hier te vinden. Lees natuurlijk j de blogposting. j n
Thanks for interesting post! xoz
[url=http://skuper.ru]паркет[/url] 9i
132 Microsoft Team blogs searched, 105 blogs have new articles in the past 30 days. 641 new articles
Do you get the occasional OutOfMemoryException error when running reports in WSUS?  Are you looking
This type of query really should be in a report in the GUI
If a patch or update is installed by some other means than WSUS then WSUS should not return a ‘Not Applicable’ Status. It can say already installed or something else but to me Not applicable on a machine I know should have something like SP 3 makes it look like an error
Using SQL to find this information out is really not the best way of doing it. Please provide more canned reports or more ad hoc report functionality in WSUS itself!
thanks!
Not Applicable’ Status. It can say already installed or something else but to me Not applicable on a machine I know should have something like SP 3 makes it look like an error