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
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
Shane, I was looking for such a report but when i try to run a report in SQL Studio, i am getting the belwo error. I am not that well with SQL queries, cna you please help me.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword ‘as’.
The query is perfectly fine. Thanks for sharing.
For everyone discussing the syntax error message you have to replace the apostrophes with actual ‘ marks. The apostrophes at the very end of the cast line:
+ ‘%’ AS ‘% Compliant’, <- make sure those are apostrophes and not weird quotes
Hi,
I am looking for SCCM2012 query for patch status from specific deployment and specific collection. I need the following details in my patch report with against each machine name
Total patch count in deployment
Total patch count installed
Total patch count missing
Total patch count not required
I’m looking for this as well. It would be nice to query which computer failed to install a patch from a specific deployment.
Code works perfectly…Thankzzz keep rockinggggg… 🙂
There are many fine stores to choose from, but consider the following:1.
This year, experts are predicting that dolls based on the boy band,
Onne Direction will sell incredibly well. In standard, the smaller a great RC car is normally, the more
expensive it will be, as well.
This is a great report. I am currently using this at my organization. Thank you for this Kent!
This is exactly what I am needing but not sure where/how to run it properly?
How would i run this query? I really would love it as a query in CM, but also tried it in SQL on the CM database and get errors:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 156, Level 15, State 1, Line 47
Incorrect syntax near the keyword ‘as’.
Thanks
Great report ..Can this be modified in such a way that it can be extracted collection wise ? I used prompt but it opens default to the above report
i have been using this report for a while. My question is can someone let me know how to add the field “Non-Compliant” under the Last state?
Thanks for all your help
Has anyone modified the report to break out by OU’s?
Or any idea where to join v_RA_System_SystemOUName and v_R_system?
Thanks
-Paul
man this would be even more awesome if you could take this info and create a collection based on it for a more aggressive maintenance window or different reporting against this group of computers.I haven’t seen all of the views there to create something like this in a wql query
How do you include other assignmentnames in this query besides software updates (such as application deployments)
How to resolve this error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword ‘as’.