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

Advertisements