SCCM 2007 Patch Report Per Collection

Here is a slightly modified Patch Compliance report that allows for quick analysis for either a Security Bulletin or a KB Article number.  The default report in SCCM only allows you to focus on 1 OS at a time whereas this allows you to have a nice little report for a particular Security Bulletin or KB number.  (Because of the OR statement, you can also have both parameters filled to view both)

declare @collid varchar(20); set @collid = 'SMS0001'
declare @BulletinID varchar(20); set @BulletinID = 'MS12-048'
declare @ArticleID varchar(20); set @ArticleID = ''

declare @CI table(CI_ID int primary key)
if @ArticleID = '' set @ArticleID = NULL;
if @BulletinID = '' set @BulletinID = NULL;


insert @CI(CI_ID)
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0 
and (ci.ArticleID = @ArticleID or ci.BulletinID = @BulletinID)

select
Vendor=ven.CategoryInstanceName,
UpdateClassification=cls.CategoryInstanceName,
ArticleID,
BulletinID,
Title,
Approved=case when exists(select 1 from v_CITargetedCollections where CI_ID=ci.CI_ID and CollectionID=@CollID) then '*' else '' end,
Present=NumPresent,
Missing=NumMissing,
NotApplicable=NumNotApplicable,
Unknown=NumUnknown,
Total=NumTotal,
PCompliant=convert(numeric(5,2), isnull((NumPresent+NumNotApplicable)*100.0/nullif(NumTotal, 0), 100)),
PNotCompliant=convert(numeric(5,2), isnull((NumMissing)*100.0/nullif(NumTotal, 0), 0)),
PUnknown=convert(numeric(5,2), isnull((NumUnknown)*100.0/nullif(NumTotal, 0), 0)),
CollectionID=@CollID,
UniqueUpdateID=CI_UniqueID,
InformationURL=InfoURL
from @CI ci
left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID=@CollID
left join v_UpdateInfo ui on ui.CI_ID=ci.CI_ID
left join v_CICategoryInfo_All ven on ven.CI_ID=ci.CI_ID and ven.CategoryTypeName='Company'
left join v_CICategoryInfo_All cls on cls.CI_ID=ci.CI_ID and cls.CategoryTypeName='UpdateClassification'
order by 1, 2, 3