Selected Update Groups States listing required updates deployed to collection600Nonec4d8f81b-a374-4ee9-b298-41cba5214a3eCentralSite=Parameters!UserTokenSIDs.Valueselect dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) as UserSIDsUserSIDsSystem.StringCentralSite=Parameters!UserSIDs.ValueSelect Distinct CollectionID, Name FROM dbo.fn_rbac_collection(@UserSIDs) order by NametrueCollectionIDSystem.StringNameSystem.StringCentralSite=Parameters!SUGS.Value=Parameters!UserSIDs.Value=Parameters!CollID.Value/*
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
where
CI_ID in (@SUGS) --report usage
--Getting Updates States for updates in selected Update Groups
select uc.ResourceID, uc.StateType, uc.StateID into #uct 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,
m.name0,
m.Name0+'.'+m.Full_Domain_Name0 as ComputerName0,
sn.StateName as Status
,count(sn.StateName) as countstatus
into #updates_status
from
#uct uc
join v_StateNames sn with (nolock) on sn.TopicType = uc.StateType and sn.StateID = uc.StateID
join v_R_System m with (nolock) on m.ResourceID=uc.ResourceID and isnull(m.Obsolete0,0)=0
join (select vc1.ResourceID from v_FullCollectionMembership vc1 with (nolock) where vc1.CollectionID=@CollID) vc on vc.ResourceID=m.ResourceID
where
sn.StateName not like 'Update is not required'
group by
uc.ResourceID,
m.Name0,
m.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 ts
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 #ucttruename0System.StringComputerName0System.StringResourceIDSystem.Int32Detection state unknownSystem.Int32CompliancySystem.StringDownloaded updateSystem.Int32Failed to download updateSystem.Int32Failed to install updateSystem.Int32General failureSystem.Int32Installing updateSystem.Int32Pending system restartSystem.Int32Successfully installed updateSystem.Int32Update is installedSystem.Int32Update is not requiredSystem.Int32Update is requiredSystem.Int32Waiting for another installation to completeSystem.Int32Waiting for maintenance window before installingSystem.Int32CentralSiteselect CI_ID,title
FROM
v_AuthListInfo
--where
--title like '%required%'
--and title not like '%office%'
--and title not like '%pilot%'trueCI_IDSystem.Int32titleSystem.String=Fields!Compliancy.Value=Fields!Compliancy.Value=Sum(Fields!Detection_state_unknown.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Downloaded_update.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Failed_to_download_update.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Failed_to_install_update.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!General_failure.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Installing_update.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Pending_system_restart.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Successfully_installed_update.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Update_is_installed.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Update_is_not_required.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Update_is_required.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Waiting_for_another_installation_to_complete.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt=Sum(Fields!Waiting_for_maintenance_window_before_installing.Value)#VALYOutputPercentStackedPrimaryPrimaryBlack0pt