Status report for Software Update deployments
Spending some time in other communities often gives me a lot of good ideas to reports and queries. A little while ago in the System Center Technet community I stumbled over a very need report created by a user called Qu4rk. His report shows a status of each update deployment, with a count of the each possible software update states. The only thing I have changed (thanks to Claus) is a new column showing the compliant computers in %. I am not going to take any credit for the report as my work was very limited. But this report is way to cool to be forgotten, and I am sure that it will help most of you.
The 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
Subscribe to Kent's RSS Feed
Hi,
Can you add add one more thing to this report? I haven’t learned how to “work” software updates reports…
Would be nice to have a link from this report to “Compliance 4 – Deployment (per update)” report. This way, you can from here to a detailed report of a specific deployment.
Thanks!
Great report! I second that from Vitalie… how do you link this to that other report?
Just an FYI, make sure to change the ticks to single quotes for your SQL query.
Very useful report !! It is saving lot time for me. Thank you very much.
I am looking for a report which tells me how many updates are failing on how many systems in a deployment.
Any help would be highly appreciated.
Regards,
Umesh
Umesh,
How about this?
select
CS.Name0,
CS.UserName0,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (‘Needs ‘+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ‘ Patches’))
else ‘Good Client’
end as ‘Status’,
ws.lasthwscan as ‘Last HW scan’,
FCM.collectionID–,
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.ResourceID
join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’
left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
Where
UCS.Status = ’2′
and FCM.collectionid = ‘SMS00001′
Group by
CS.Name0,
CS.UserName0,
ws.lasthwscan,
FCM.collectionID
Order by
CS.Name0,
CS.UserName0,
ws.lasthwscan,
FCM.collectionID
By the way the script above did you get an Incorrect syntax near the keyword ‘as’?
I’ve improved the report, by including ALL Enforcement States, from http://technet.microsoft.com/en-us/library/bb932172.aspx . And changed the order of the Enforcement States, reading from “good” to “bad”, left to right.
And… added drill down option to “States 1 – Enforcement states for a deployment” (which can then be drilled down to “States 4 – Computers in a specific state for a deployment”)… so you can see more details.
SQL code below (Note : not sure how ‘ will go with copy and paste)
Select DeploymentID, 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
ORDER BY Deploymentname
Then when at “Links”, choose “Link to another report”, then choose “States 1 – Enforcement states for a deployment”.
You can also link above report to “States 2 – Evaluation states for a deployment”.
Enjoy !!!
Shane