SCCM Report Current Patch Deployment State

This report returns the enforcement states for a specific software update deployment, which is typically the second phase of a deployment assessment. For the overall progress of software update installation, use this report in conjunction with 'Evaluation state for a specific software update deployment':




declare @DeploymentLocalID as int
declare @COLLCOUNT as int
select @DeploymentLocalID = AssignmentID from v_CIAssignment where Assignment_UniqueID = @DEPLOYMENTID
select @COLLCOUNT=count(*) from v_CIAssignmentTargetedMachines where AssignmentID=@DeploymentLocalID

select 
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers,
PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif(@COLLCOUNT, 0), 1))),
sn.TopicType*10000 + sn.StateID as DeploymentStateID
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where (@DEPLOYMENTID is null or a.Assignment_UniqueID = @DEPLOYMENTID)
and assc.StateType in (300,301)
group by a.Assignment_UniqueID,a.AssignmentName, a.StartTime, a.EnforcementDeadline, sn.StateName, sn.TopicType, sn.StateID

order by sn.StateName

*************************************************************************

PROMPT:

declare @DeploymentLocalID as int
declare @COLLCOUNT as int
select @DeploymentLocalID = AssignmentID from v_CIAssignment where Assignment_UniqueID = @DEPLOYMENTID
select @COLLCOUNT=count(*) from v_CIAssignmentTargetedMachines where AssignmentID=@DeploymentLocalID

select 
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers,
PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif(@COLLCOUNT, 0), 1))),
sn.TopicType*10000 + sn.StateID as DeploymentStateID
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where (@DEPLOYMENTID is null or a.Assignment_UniqueID = @DEPLOYMENTID)
and assc.StateType in (300,301)
group by a.Assignment_UniqueID,a.AssignmentName, a.StartTime, a.EnforcementDeadline, sn.StateName, sn.TopicType, sn.StateID

order by sn.StateName