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]] On
Behalf Of Gerlak, Matthew
Sent: Friday, September 5, 2014 3:28 PM
To: [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')
