Collection-SUG.sql

/*
declare @CollID varchar(8) = 'Collection ID for Testing'
DECLARE @UserSIDs VARCHAR(16);
SELECT @UserSIDs = 'disabled';
*/

declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID('English')

--Getting Deployments IDs
select
	cia.AssignmentID as id
into #ASSIGNID
from fn_rbac_CIAssignmentToGroup(@UserSIDs) atg
join fn_rbac_AuthListInfo(@lcid, @UserSIDs) ugi on ugi.CI_ID = atg.AssignedUpdateGroup
join v_CIAssignment cia on atg.AssignmentID = cia.AssignmentID
where 
	CI_ID in (17143786,17152698,17159821) -- Software update Goup Config Item ID for testing
--	CI_ID in (@SUGS) --report usage

--Getting Updates States for updates in selected Update Groups
select uc.ResourceID, uc.StateType, uc.StateID 
into #updatestates_combined 
from  v_UpdateState_Combined  uc with (nolock)
where 
	uc.ResourceID  in ( select ast0.ResourceID from v_CIAssignmentTargetedMachines ast0 where  ast0.AssignmentID in (select id from #ASSIGNID)) 
and
	uc.CI_ID in ( select aci0.CI_ID from v_CIAssignmentToCI aci0 where aci0.AssignmentID in (select id from #ASSIGNID ))  
 
select
	uc.ResourceID, 
	s.name0,
	s.Name0+'.'+s.Full_Domain_Name0 as ComputerName0,
	sn.StateName as Status
	,count(sn.StateName) as countstatus
into #updates_status
from
#updatestates_combined uc
join v_StateNames sn with (nolock) on sn.TopicType = uc.StateType and sn.StateID = uc.StateID
join v_R_System s with (nolock) on s.ResourceID = uc.ResourceID and isnull(s.Obsolete0,0) = 0
join (select fcm1.ResourceID from v_FullCollectionMembership fcm1 with (nolock) where fcm1.CollectionID = @CollID) fcm  on fcm.ResourceID = s.ResourceID 
where 
	sn.StateName not like 'Update is not required'
group by 
	uc.ResourceID, 
	s.Name0,
	s.Full_Domain_Name0,
	sn.StateName

--Pivoting Data and setting overall Compliance State
select 
	*,
	case 
		when 
			[Update is installed] is not null and
			[Detection state unknown] is null and
			[Downloaded update] is null and
			[Installing update] is null and
			[Pending system restart] is null and
			[Successfully installed update] is null and
			[Update is required] is null and
			[Waiting for another installation to complete] is null and
			[Successfully installed update] is null and
			[Failed to download update] is null and
			[Failed to install update] is null and
			[General failure] is null and 
			[Waiting for maintenance window before installing] is null
			then 'Compliant'
		when 
			[Failed to download update] is not null or 
			[Failed to install update] is not null or
			[General failure] is not null
			then 'Failed' 
		when
			[Detection state unknown] is not null
			then 'State Unknown'
	else 'In Progress'
	end as [Compliancy]
from #updates_status
pivot(sum(countstatus)
	for [status] in ([Detection state unknown],[Downloaded update],[Failed to download update],[Failed to install update],[General failure],[Installing update],[Pending system restart],[Successfully installed update],[Update is installed],[Update is not required],[Update is required],[Waiting for another installation to complete],[Waiting for maintenance window before installing])
	) as results;

--Cleanup
drop table #updates_status
drop table #ASSIGNID
drop table #updatestates_combined