TJT

The following will grab the KB articles: select distinct v_UpdateInfo.ArticleID from v_AuthListInfo,v_CIRelation,v_UpdateInfo where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and v_AuthListInfo.Title = @UPDATELIST order by v_UpdateInfo.ArticleID @UPDATELIST PROMPT: begin if (@__filterwildcard = '') select distinct Title from v_AuthListInfo order by Title else select distinct Title from v_AuthListInfo where ((CI_UniqueID like @__filterwildcard) or (Title like @__filterwildcard)) order by Title end

I was also trying to figure out to get KB articles from an Update List by joining v_AuthListInfo (The Update List) and v_UpdateInfo (The list of KB articles). This way if you had an update list with September patches, for example, and you grabbed the KB article list and used this list to determine your patch compliance for a given month of MS Updates.

What is the select statement for the @Status prompt?

I can't seem to get this report working in SCCM 2007. It returns nothing. I have changed MS10-006 to MS11-054 etc and still no luck. Next I tried this "where (summ.qnumbers='KB2539639' or summ.qnumbers='KB2507938') " and this "where (summ.qnumbers='2539639' or summ.qnumbers='2507938') " Still no luck ... no matching records could be found "select summ.Product,summ.Language,summ.ID,summ.qnumbers as'Q Number', COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end) as 'Distribution Successful', COUNT(distinct case when ps.LastState=102 then ps.ResourceID else NULL end) as 'Reboot Pending', COUNT(distinct case when ps.LastState=101 then ps.ResourceID else NULL end) as 'Distribution Failed', COUNT(distinct case when ps.LastState not in (107,105,101,102) then ps.ResourceID else NULL end) as 'Distribution Incomplete', COUNT(distinct ps.ResourceID) as 'Requested', ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'Success %', ROUND(100.0*COUNT(distinct case when ps.laststate=101 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'failed %', ROUND(100.0*COUNT(distinct case when ps.LastState not in (107,105,101,102) then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as 'Incomplete %' from v_GS_PatchStatusEx ps join v_ApplicableUpdatesSummaryEx summ on ps.UpdateID=summ.UpdateID where (summ.ID='MS10-006' or summ.ID='MS10-007' or summ.ID='MS10-008' or summ.ID='MS10-013') and (summ.Type='Microsoft Update') and (summ.product not like 'Windows Server%') group by summ.Product, summ.ID, summ.Language, summ.qnumbers Order By summ.Language,summ.ID"