Web Report for Software Updates Compliance
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
May 13, 2009 at 8:52 pm
Nice script. Need to take this out for a test drive.
June 15, 2009 at 12:54 pm
I plan on taking it out for a test drive too. Thanks Bob
July 1, 2009 at 3:05 pm
Does this report updates that are installed or ones that are missing?
July 1, 2009 at 3:29 pm
Thanks for asking Michael. This report will show systems that are missing updates.
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?
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.
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.
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.