Back in January I released a set of software update reports I called Yet Another Software Update Dashboard. This was a set of reports that I had built to monitor software update compliance for my organization. The name was a bit of a lie as there really wasn’t much of a dashboard in there. Sure, I broke down the compliance figures into sub-categories but that is not a real dashboard. When the MMSMOA team was foolish enough to give me a microphone and let me talk about update reporting I took that as an opportunity to remedy this.
I’m going to focus just on the changes I’ve made since the initial release. Refer to the original post for everything else: Yet Another Software Update Dashboard.
Dougnuts. Hmmmmmm doughnuts.
While I’m an exile to the US I am at heart a Canadian. Canadians have a love affair with doughnuts that most people don’t really understand. So I changed the pie charts to doughnuts. Honestly, I have zero preference between the two but for better or worse doughnuts give the impression of being more ‘modern’.
I’ve also gone through the reports and added alternating background colors to hopefully make things a bit more readable.
Workstation/Server Split
I stole this from some other example that I now unfortunately forget but I’m using v_GS_OPERATING_SYSTEM.ProductType0 to split the reports between the two. The overall compliance report still has graphs to break those down based on collections but I think it’s a worthwhile split at the highest levels.
New Report: Latest SUG for an ADR
This was one of the first reports I wrote. It allows you to specify an Automatic Deployment Rule (ADR) and it will list the updates in the latest Software Update Group the ADR created. I’m not sure the report has a ton of value, mostly because the titles for updates in WSUS/ConfigMgr aren’t all that descriptive. It does work however so I figured I might as well include it. If your leadership or team wants to know what patches you are releasing this month this is about as good as it gets. I specifically configured the parameters so that you can create a report subscription using just the name of the ADR.
New Report: Scan Report
The built in Scan Error report is plenty fine. However, I wanted something that fit into the set of reports that make up my dashboard so I’ve added it here. This also allows for drill down into the other reports that make up the whole solution. Here you can see that I have 18 devices (out of ~1300) in our lab that have failed to scan. Remember: if you’re not scanning then your not patching.
New Report: Installation Failures
There were already two reports in the original release that organize update installation failures by machine and by update. This report organizes it by the failure code itself so that you can easily see what kind of failures are most prevalent in your organization. Note that the figure in the header refers to the number of distinct systems. Since a system can have multiple failures that doesn’t match the total number of individual errors. However, it does give you some insight into what kinds of things might be going wrong. You can see in the example below that we are still facing timeout issues despite using my maintenance script to increase all cumulative updates to 60 minutes. You also see the result of my team deciding that our virtual machines only needed 30 Gb for their system drive leading to an unending parade of full disks.
Finally, A Honest to Goodness Dashboard
Ok, now we get to the heart of the thing. When I first envisioned my session at MMS talking about patching I had wanted to create a dashboard both in SSRS and in PowerBI and use them to discuss the merits of each. Time prevented that; both in terms of preparation and the session itself but it did push me to create a dashboard worth replicating in PowerBI. There’s not much here that needs explained. Everything is a link to the other reports that are part of this solution. The one thing I will mention is that clicking on the top level chart titles (ex. All Workstations) will change the dashboard to report on just those kinds of devices.
So there you have it, a real, honest-to-goodness dashboard this time. I have some other ideas for subreports but for now this ticks off all the major things I want to be able to see at a glance: overall compliance figures, installation errors, scan errors, and devices that don’t have the latest cumulative update.
I’m unable to edit these reports after uploading them. I get this error with Microsoft Report Builder:
System.Web.Services.Protocols.SoapException: The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition’ which cannot be upgraded.
at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, ItemType ItemType, CatalogItem& ItemInfo, Warning[]& Warnings)
at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)
at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)
Hi Byran,
Great reports…I’m getting some strange results. I don’t see all the updates that are “in” the ADR, and the ADR name doesn’t reflect what it’s been renamed too (renamed months ago, reports just installed). I’ve verified they are in the SUG the ADR creates, and the preview of the ADR shows the missing updates. The other issue is (which might be related) is that I just seem get Cumulative Update not found on all machines in the Latest Cumulative Update report, any ideas?
Ahh, renaming the ADR will break the ADR to SUG logic. There’s nothing that actually ties them together apart from the SUG’s ‘created by’ field holding the ADR’s name. So if you changed the ADR’s name after the fact then any existing SUGs won’t be tied to it.
The LCU report likely needs some work at this point as the Update and OS names changed a while back.
Bryan, great work. Where in SQL can we find the Error Description for Software Update deployments? Shown in column 1 in your dashboard under ‘Top Installation Errors’. Thank you!
It’s not SQL, it a function that makes a call to get the description. Open up the report in the designer and look at that field to get the function name and follow that down the rabbit hole.
Thank you for the quick reply! I do have your .rdl open in designer but I guess I am unsure where to look. I have been looking at SMSProv.log to see the query ran when Monitoring a particular deployment. I am unfamiliar with how functions would work for SQL views. Are you saying this Error Description is not held in a table in the MECM database, but a call (web?) has to be made to convert the error code, or? Any help very much appreciated!
Hi Bryan. Where does he Error Description come from? Thank you!
Great work on the dashboard, is very useful, thank you! Wondering if there’s a way of showing compliance for machines that have been lagging (not updating for months) rather than just focusing on the not having the latest CU? A machine that is a few weeks behind not so bad, compared to one that is months behind or not updating at all.
It’s not as dynamic as I would like but each report has a ‘Days’ parameter that you can set to define how old an update needs to be in order to be included. So you could push those up to higher values to kind of achieve that.
But I hear what you’re saying, could use a “Haven’t patched in X days/weeks/months(?)” visualization.
Holy crap! Your report is exactly what I was looking for. Even shows GPO conflict errors that I have on some remaining workstations that haven’t updated the WSUS server GPO change. I really liked your Pully DP post too.
Kudos Bryan, Really appreciate.
What license (if any) is Yet Another Software Update Dashboard released under?
I’m really sorry to sat this, but your reports do not work!!!
The Latest Cumulative Update report needs some love for sure but all the others should still work since there’s been no breaking changes to the SQL Views used. Be sure to follow the instructions in the original post for importing these.
Thank you.. this report was a help for me!
Great Reports… Can you help me add 1 parameter to the for choosing a specific software update group. Example July Software update group as we are compliant up to current month and wanting to report on current month. I would really appreciate this help.
Hi Bryan,
Thanks for the great reports.
For Overall Compliance report, do do I get hide or delete of the parameters? I’m our environment there are Pilot / Prod Workstations and Dev / Prod servers.
I have tried to manage the parameter through SSRS and Report Builders.
When I hide or delete the parameters in SSRS Manage I get an error. The same thing with the report builder.
great reports Bryan, I do have a query: How can i resolve the ‘detection state unknown’ for an update on a server that is not required? this throws out the overall compliance
On which report? On the overall compliance report is based on the compliance of the SUG deployments. So as long as all of the deployments report compliant then the device is compliant. If one or more report failure then it’s failed. If the compliance field is null for any deployment then it’s unknown.
Great work, Bryan. This is highly useful for me. One question, in ‘Software Update Dashboard – All Devices’, all the donut charts show 100% unknown, but the data in the odometers and tables is accurate. Also, in ‘Compliance Overview – Latest Cumulative Update’ the donut charts show the accurate percentages. Is there something I may have missed when adapting the reports to my environment?
Thanks
Joe, that suggests that every client has one or more assignments (deployments) that have not reported status at all. Drill down into the details and somewhere along the line the report will show assignment status per machine.
Hi There Guru,
This design is awesome.
Is there anyway you can design a specific Software Updates Group Status (Compliant, Error, Unknown etc.) with One or Multiple Collection?
Ivan, glad you liked it. While I’m certain it’s possible to write the kind of thing you’re looking for it’s not the kind of report I would want to write. I simply do not see the value on focussing on SUGs; in my organization that has nothing to do with device compliance. What might make sense though is to put an upper bound on the deployment deadline. That way you could limit it to updates that have released in the last week, month, or any date range you prefer.
Greetings!
Is the “Compliance – Latest Cumulative Update” report working on your end for Windows 10 machines? I’m not sure when it broke on my end but it seems to not show anything even if your Compliance for Specific Computer and SCCM’s in built report shows the latest cumulative
Yea, someone else has mentioned that the Win 10 stuff doesn’t line up anymore. I’ll take a look when I get a chance and see if I can fix it.
awesome, I wasn’t sure if I should be poking around in my environment or your report
appreciate your work!
on the workstation piece… if you change your left join where you are tying in the update_prefix to have a case statement on CategoryInstanceName, looking for and replacing ‘Windows 10 , version 1903 and later’ with ‘Windows 10’ it works… am i underthinking this?
new section like
(select CI_ID, Title, case CategoryInstanceName when ‘Windows 10, version 1903 and later’ then ‘Windows 10’ else CategoryInstanceName end as CategoryInstanceName
one more thing to add… toward the bottom…
Left Join fn_ListCategoryInstances(@lcid) As Categories
On UpdateInfo.CategoryInstance_UniqueID = Categories.CategoryInstance_UniqueID) UpdateList
On CollectionMembers.OperatingSystem = UpdateList.CategoryInstanceName
Left Join fn_ListCategoryInstances(@lcid) As Categories
On UpdateInfo.CategoryInstance_UniqueID = Categories.CategoryInstance_UniqueID) UpdateList
On CollectionMembers.OperatingSystem = substring(UpdateList.CategoryInstanceName,1,10)
it didnt like my comments… the first left join is the old way. To get workstations reporting compliance, you have to be like the last one
This is superb – I particularly like the 100% more!
I have one issue, which hopefully is just down to my poor SQL report-fu:
I’ve renamed the reports, giving them prefix to help our IT support find them easily. Now, when running the Software Updates Installation failure by Update I can’t then click on the link for a failed computer, receiving “The item ‘/ConfigMgr_[mysite]/Compliance for Specific Computer’ cannot be found. (rsItemNotFound).”. Something simple I’ve missed no doubt but any guidance appreciated!
Oops. Found it. Text Box Properties for the [Name] cell, then Actions.
I think this means I am now 101% ‘more’
Hi,
Again, thank you for the good work.
I had to change the Period parameter to:
Format(DateAdd(“M”,-1,Now()),”yyyy-MM”)