Help with report 204

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Help with report 204

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Help with report 204

Page: [1]

Message

<< Older Topic Newer Topic >>

Help with report 204 - 6/5/2008 4:36:18 PM

jferguson982

Posts: 9
Score: 0
Joined: 5/20/2005
Status: offline

I'm trying to customize report 204 (Management 1 - Updates required but not deployed) so that it won't prompt for a specific collection and vendor. I want to automatically populate the collectionID and vendor so that I can put it in a dashboard for 2 separate collections. I've cloned the report and started modifying it, but i can't get it to work. when i run it with my customizations, i get zero results, but when i run the original report, i get results. I was hoping someone could look at it and show me what i'm missing. Here's my code (with no prompts):

set nocount on

declare @VendorID int; select @VendorID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Company' and CategoryInstanceName='Microsoft'

declare @ClassID int; set @ClassID='0'

declare @CollID char; set @CollID='IA10001E'

declare @NumTotal int; select @NumTotal=count(*) from v_ClientCollectionMembers where CollectionID='IA10001E' and IsClient=1

declare @CI table(CI_ID int primary key); with CA as (select AssignmentID from v_CIAssignmentTargetedCollections where CollectionID='IA10001E')
   , CM as (select ResourceID from v_ClientCollectionMembers where CollectionID=@CollID and IsClient=1)

insert @CI

select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0
and (@VendorID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@VendorID))
and (@ClassID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))
and exists(select 1 from CM join v_UpdateComplianceStatus cs on cs.CI_ID=ci.CI_ID and cs.ResourceID=CM.ResourceID and cs.Status=2)
and not exists(select 1 from CA join v_CIAssignmentToCI ac on ac.AssignmentID=CA.AssignmentID and ac.CI_ID=ci.CI_ID)

select
   ui.ArticleID as ArticleID,
   ui.BulletinID as BulletinID,
   ui.Title as Title,
   NumMissing as Missing,
   PComputers=convert(numeric(5,2), (isnull(NumMissing, 0))*100.0 / isnull(nullif(@NumTotal, 0), 1)),
   ui.InfoURL as InformationURL,
   ui.CI_UniqueID as UniqueUpdateID
from @CI ci
left join v_UpdateInfo ui on ci.CI_ID=ui.CI_ID
left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID='IA10001E' and cs.NumMissing>0
order by NumMissing desc

Thanks for the help.
Jerrett