Pending Windows Update deployments

This SQL query shows the pending Software Updates including deadlines

[SQL code]
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
order by deadline desc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s