SCCM - SQL Query to get Software Update Deployment Status - Pivot View

SCCM - SQL Query to get Software Update Deployment Status - Pivot View

Select  Deploymentname, Available, Deadline,

cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Pending system restart], 0) + ISNULL([Waiting for restart], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Downloaded update(s)], 0) + ISNULL([Downloading update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Waiting for maintenance window before installing], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Failed to download update(s)], 0) + ISNULL([Failed to install update(s)], 0) ))*100) as Numeric(10,2)) as varchar(256)) + '%' AS '% Compliant',

[Compliant],
[Successfully installed update(s)],
[Pending system restart],
[Waiting for restart],
[Installing update(s)],
[Downloaded update(s)],
[Downloading update(s)],
[Waiting for another installation to complete],
[Waiting for maintenance window before installing],
[Enforcement state unknown],
[Failed to download update(s)],
[Failed to install update(s)]

From

(select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers
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)

group by a.Assignment_UniqueID, a.AssignmentName, a.StartTime, a.EnforcementDeadline,
sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)
FOR LastEnforcementState IN

( [Compliant],
[Successfully installed update(s)],
[Pending system restart],
[Waiting for restart],
[Installing update(s)],
[Downloaded update(s)],
[Downloading update(s)],
[Waiting for another installation to complete],
[Waiting for maintenance window before installing],
[Enforcement state unknown],
[Failed to download update(s)],
[Failed to install update(s)])
) AS pvt

where DeploymentName in ('Deployment Name')

ORDER BY Deploymentname