I recently ran a poll (back over Christmas) asking the community about what they would like to see and by popular demand people asked for an update to the PowerBI patching dashboard and man with all of the little bugs I found am I glad I listened to the community. If you are looking to download the next version of the template you can download it from TechNet here:
In this version I spent a lot of time working on fixing various small visual bugs, and pulling back MORE data. The first thing that’s an important note is that I have REMOVED the date range restriction on how far back we retrieve compliance information. Originally, I limited the number of updates that could be retrieved by only going back two years in time. Now I go back to get compliance information against all patches in the database. This can cause the performance of the report to really struggle however it provides a more holistic overview of the environment for patch compliance. I have only tested the report in an environment of 400 machines and NOT at a large scale of thousands of machines. I strongly recommend running the SQL queries one by one to check the run time for each one. The used SQL queries are featured on the TechNet page.
Now onto bug fixes:
- Fixed Many to One and Many to Many relationships
- Adjusted Date Range of past updates (No longer only retrieve patches from last 90 days)
- Fixed Source Query being broken due to an upgrade
- Removed some dials from Organization Overview Corrected
- Corrected the filters on the front page to all use the same data table
- Corrected the Servers Page text (Said Workstations in some spaces)
- Corrected the filters on the Servers/Workstations page.
- Updated color scheme. Red and Black was unclear on compliance information
- Added SUG selection to front page and made it something you could interact with to view an ‘overview’ dial based on SUGS
- Updated the workstation/server pages to include the last time the patch was evaluated for compliance.
- Added a collection filter – you can now only pull back some specific collections to filter the data against.
This has been really fun to work on and now to show some pretty cool features when you first open the PowerBI Template you will be presented with a prompt for some parameters:
Each of these parameters is relatively self explanatory but just in case:
This is a filter to only retrieve collections based on a filter. This uses SQL wild cards and allows you to look at the patch compliance of machines in specific collections. An example would be if you had some maintenance window collections and wanted to return everything that matched your maintenance window naming standard you would enter something like %MAINT%
This is the name of the server that runs the SQL database for configuration manager. Enter the server name here you can use FQDN you will also need to be running PowerBI as a user that has rights to the ConfigMgr database and depending on your security levels may have to run PowerBI as an admin.
This is the name of the data base for your site. Typically something like CM_PR1 or CM_P01.
Once you enter the correct information you will be prompted for permission to run those queries against the database. Approve them and then you’ll see it run for a period of time before it returns some information back. That information will look like this:
From there you’ll be able to get a good overview image of your environment. You’ll also be able to select a software update group from here to cause the dials to change. By default you’ll notice a target goal over on the side for the radials. I have set the target to be at least 80% client install and 80% patch compliance.
At the bottom of course we have our standard compliance tabs for different operating systems with different ways of filtering the data.
And the final page which shows the client penetration in your environment. I think this will be the next area that gets some love to include some of the client health information that is out there.
Finally a little video of the dashboard in use using all the little dials and buttons.
Let me know what you would like to see next!
Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.