So I get different results. Here is screen shot of the default query. The first highlight is the Console Count on the collection the second highlight is the report count. Not sure why they are off. Also the new query modifications don't work for me I get zero results now. [cid:[email protected]]
From: [email protected] [mailto:[email protected]] On Behalf Of Garth Jones Sent: Friday, September 05, 2014 5:16 PM To: [email protected] Subject: RE: [mssms] RE: Patch Reporting Help I have also simplified it and formatted it, however I have NOT validated it against the report at all. Why do you think that it is not working? Declare @Col as varchar(15) Declare @UDL as varchar(150) Set @col = 'SMS00001' Set @UDL = 'Updatelist' Select ( Select Count(FCMV.resourceid) from dbo.v_UpdateGroupStatus_Live cs left join v_StateNames sn on sn.TopicType=300 and sn.StateID = isnull(cs.Status, 0) left join v_AuthListInfo ai ON cs.CI_ID = ai.CI_ID join dbo.v_FullCollectionMembership_Valid FCMV ON cs.ResourceID = FCMV.resourceID and FCMV.CollectionID = @col where ai.Title = @UDL and cs.status = '3') as 'Compliant', ( Select Count(FCMV.resourceid) from dbo.v_UpdateGroupStatus_Live cs left join dbo.v_StateNames sn on sn.TopicType=300 and sn.StateID = isnull(cs.Status, 0) left join dbo.v_AuthListInfo ai ON cs.CI_ID = ai.CI_ID join dbo.v_FullCollectionMembership_Valid FCMV ON cs.ResourceID = FCMV.resourceID and FCMV.CollectionID = @col where ai.Title = @UDL and cs.status != '3') as 'NONCompliant' Garth Jones Chief Architect Tel: 613-627-4801 x168 Fax: 613-627-4802 www.Enhansoft.com<http://www.enhansoft.com/> [Description: Description: cid:[email protected]]<http://www.enhansoft.com/> Enhancing Your Business [Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://be.enhansoft.com/>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<https://twitter.com/enhansoft>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://www.facebook.com/EnhansoftInc>[Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Description: Enhansoft's YouTube Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description: Description: Description: Description: Description: Description: cid:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/> [cid:[email protected]]<http://mms.mnscug.org/> Ask Me About Our Free SSRS Reports<http://www.enhansoft.com/pages/Free-SSRS-Reports.aspx> From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Gerlak, Matthew Sent: Friday, September 5, 2014 3:28 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] RE: Patch Reporting Help Hello all wondering if someone can help me figure out why this Query give me different results then the caned report Software Updates - A Compliance Compliance 1 - Overall Compliance pointing to same collection and Update list as below. It's probably something Easy I am just not seeing. Select Compliant= (Select Count(r.resourceid) from v_UpdateGroupStatus_Live cs left join v_StateNames sn on sn.TopicType=300 and sn.StateID=isnull(cs.Status, 0) left join v_AuthListInfo ai ON cs.CI_ID = ai.CI_ID inner join v_R_System r ON cs.ResourceID = r.ResourceID join [v_CM_RES_COLL_XXXXXXXXX] COL on col.ResourceID = r.ResourceID where Active0 = 1 AND ai.Title = 'Updatelist' and cs.status = '3') , NONCompliant=(Select Count(r.resourceid) from v_UpdateGroupStatus_Live cs left join v_StateNames sn on sn.TopicType=300 and sn.StateID=isnull(cs.Status, 0) left join v_AuthListInfo ai ON cs.CI_ID = ai.CI_ID inner join v_R_System r ON cs.ResourceID = r.ResourceID join [v_CM_RES_COLL_XXXXXXXXX] COL on col.ResourceID = r.ResourceID where Active0 = 1 AND ai.Title = 'Udatelist' and cs.status < '3')
