A consolidated Report for Patch Deployments

to get the consolidated Report for all your deployments / instead of depending on dashboard you can view this PVT Report

 

Source:- http://blog.coretech.dk/kea/status-report-for-software-update-deployments/

Select Deploymentname, Available, Deadline,

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

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

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.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt