Web Report for Software Updates Compliance

Posted on Updated on

SCCM doesn’t have a precreated report (that I can find) that quickly shows the patch compliance for a group of systems.  So I created one for myself, that I thought I’d share with every one.  To implement this, create a web report as you normally would; use the following SQL statement and remember to create a prompt with the information below that!

SQL Statement

select distinct sys.Name0, ui.BulletinID as BulletinID, ui.ArticleID as ArticleID, ui.Title as Title
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_R_System sys on css.ResourceID=sys.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=sys.ResourceID
where css.Status=2 and ccm.CollectionID=@CollID
order by sys.Name0, ui.ArticleID

Prompts

Name: CollID
Prompt text: Collection ID
Provide a SQL statement:

begin  if (@__filterwildcard = '')
select CollectionID as CollectionID, Name as CollectionName
from v_Collection
order by Name
   else
   select CollectionID as CollectionID, Name as CollectionName
   from v_Collection
   WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
   order by Name end
Advertisements

8 thoughts on “Web Report for Software Updates Compliance

    chris mullins said:
    May 13, 2009 at 8:52 pm

    Nice script. Need to take this out for a test drive.

    Bob Swenson said:
    June 15, 2009 at 12:54 pm

    I plan on taking it out for a test drive too. Thanks Bob

    Michael Schultz said:
    July 1, 2009 at 3:05 pm

    Does this report updates that are installed or ones that are missing?

    Nicolas Moseley responded:
    July 1, 2009 at 3:29 pm

    Thanks for asking Michael. This report will show systems that are missing updates.

    JQ said:
    July 19, 2012 at 8:24 am

    Thanks for the report/query. After running the report against a few systems in our environment, it appears to be displaying updates are INSTALLED on those specific systems, not the updates that are missing.

    Anyone else experiencing this behavior?

      N. Moseley responded:
      July 21, 2012 at 7:36 am

      The key here is that css.Status=2 will mean that computers are missing updates. A prime example of this that when I run the report, all computers show as needing Windows IE 9 (because that has not been deployed). Try using MBSA to remotely scan one of your systems to validate the results in the report. Then also make sure that client (and all clients for that matter) has successfully reported inventories for software updates.

    Justin said:
    November 28, 2012 at 5:51 am

    Is there anything specific that needs to be enabled on the reporting server ?
    When i run this report it just comes up with page cannot be displayed, whereas any other report i customize works fine.

      N. Moseley responded:
      November 28, 2012 at 6:39 am

      It will only work in the old CM07 web reporting style, not for SRS. Other than that, there shouldn’t be anything special. It could be though that IIS is hitting a timeout attempting to generate all of the results.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s