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!
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
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