Condividi tramite

Baseline compliance report, using public WSUS views

  • Articolo

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

)

Comments

  • Anonymous
    January 01, 2003
    Do you get the occasional OutOfMemoryException error when running reports in WSUS?  Are you looking

  • Anonymous
    January 01, 2003
    132 Microsoft Team blogs searched, 105 blogs have new articles in the past 30 days. 641 new articles

  • Anonymous
    January 01, 2003
    The list is a little longer today because of not posting last week. Enjoy! Microsoft Advanced Windows

  • Anonymous
    January 01, 2003
    I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.

  • Anonymous
    January 01, 2003
    technet very good. thanks microsoft.. i love you windows

  • Anonymous
    January 01, 2003
    Microsoft's Travis Plunk posted a modified version of Marc Shepard's WSUS SQL script for producing

  • Anonymous
    January 01, 2003
    I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    June 22, 2008
    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.....

  • Anonymous
    June 24, 2008
    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!

  • Anonymous
    July 14, 2008
    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

  • Anonymous
    July 28, 2008
    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!

  • Anonymous
    August 13, 2008
    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

  • Anonymous
    September 16, 2008
    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

  • Anonymous
    October 18, 2008
    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!

  • Anonymous
    October 20, 2008
    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 thanks!

  • Anonymous
    November 12, 2008
    This can't be done in the public UI because it has no ability to specifs the lengths of time an updates thanks

  • Anonymous
    November 12, 2008
    That computer. Howevers it can be done in WSUS 3 and laters via our public SQL view very good

  • Anonymous
    November 12, 2008
    Using SQL to find this informations out is really not the best way of doing it thanks you

  • Anonymous
    November 12, 2008
    But so many folks asked abouts how to use the public view to do this type of query last weeks that we wanteds to shows how it is done thanks

  • Anonymous
    November 12, 2008
    I know should  have somethings like SP 3 makes it looks like an errors wonderful very nice

  • Anonymous
    December 08, 2008
    The comment has been removed

  • Anonymous
    December 11, 2008
    http://www.battery-export.com/hp/hp-pavilion-dv8100-series.html  HP Pavilion dv8100 Series   http://www.battery-export.com/hp/hp-pavilion-dv8200-series.html  HP Pavilion dv8200 Series   http://www.battery-export.com/hp/hp-pavilion-dv8300-series.html  HP Pavilion dv8300 Series

  • Anonymous
    December 26, 2008
    I know should  have somethings like SP 3 makes it looks like an errors wonderful very nice

  • Anonymous
    December 28, 2008
    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. Regards

  • Anonymous
    January 01, 2009
    thansk <a href="http://www.trstar.net" title="chat sohbet, sohbet odalari">sohbet chat</a> sites no spam :D

  • Anonymous
    January 02, 2009
    http://www.ircask.com bolca sohbet

  • Anonymous
    February 15, 2009
    I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.

  • Anonymous
    February 15, 2009
    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. Regards

  • Anonymous
    March 02, 2009
    Yes If a patch or update is installed by some other means than WSUS then  WSUS should not return a ‘Not Applicable’ Status

  • Anonymous
    June 05, 2009
    thanks very good informations.. thanks thanks. nice sites.

  • Anonymous
    June 05, 2009
    technet very good. thanks microsoft.. i love you windows

  • Anonymous
    June 19, 2009
    I need a sql statement that includes the date (or number of days) of the least recent update on non-compliant servers generated from Marc's sql statement?

  • Anonymous
    July 08, 2009
    Come on Microsoft - shouldn't you be checking your blog comments for spam - it's rather worrying that there are so many spam comments on this particular posting (as an example). AS to this post - looks good, it would be nicer if it was easier to create these reports - this looks pretty complicated.

  • Anonymous
    July 23, 2009
    That computer. Howevers it can be done in WSUS 3 and laters via our public SQL view very good

  • Anonymous
    December 22, 2009
    In WSUS reports, Bulletin Number does not exist, would you please help on how to run a query to generate a report for "all computers' patch status with Bulletin number"? thanks.

  • Anonymous
    December 29, 2009
    I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.

  • Anonymous
    May 07, 2010
    This seems like some good code. I'm a rookie when it comes to the WSUS/SUSDB. For some reason, there are no values in the my vComputerTarget and was wondering how does the database tables and views get poplulated.

  • Anonymous
    July 09, 2013
    How about a report on a selected group of patches, say weekly approved of 10-20 and their rollout to a site, showing machines with patches installed and requiring those patches?

  • Anonymous
    September 03, 2015
    Has anyone got this to work on a SQL 2014 server with the SUSDB running as a SQL 2008 db, reporting on a WSUS 3.0 installation? I have it generating an e-mail, but no output at all. No errors either. I am not really a SQL guy or a scripter, but I hold my on at times. It used to work on the SQL 2008 server with a WSUS 2.0 db. I feel that this might just be a case of it looking in the wrong location, or wrong table, for the WSUS 3.0 server.

  • Anonymous
    February 25, 2016
    Hi

    Please excuse my ignorance but I am a newbie at all of this. I am running the above query against our WSUS DB and it is returning results but these results are simply the computer names.

    How would I go about getting more information such as what installation state each computer is in with an update etc?

    Again sorry if this is a really basic question but I am learning.

    Many thanks

  • Anonymous
    February 25, 2016
    Just to extend on my question. Just for example it would be great to be able to show all computers pending a reboot..

    Thanks