Patch Compliance Reporting in Configuration Manager with PowerBI

If you’ve done any work with System Center Configuration Manager sooner or later, you’ll get asked about leveraging it for patching. It might even be one of the first questions you get from management. That’s great because after all, patching with ConfigMgr is relatively simple provided you are allowed time and resources to create and ENFORCE a patching plan. However, while patching itself is relatively ‘simple’, reporting on what the heck you did can be… well challenging doubly so when you have to articulate results to a manager. Now, some of you are nodding your heads with a smile and others are probably banging their head in frustration while reminiscing on all of the hours they’ve spent on reporting. I know I’ve spent hours creating unique reports for my organization and trying to make them look pretty and easy to consume. Then this thing happened.

I’ll be honest when PowerBI came out I didn’t think anything of it. I certainly didn’t think I would get questions about it within a week or that I would enjoy using it. Nevermind the fact that after I started playing with it I realized there were a lot of things I could do with it that I simply didn’t have the time to figure out in SSRS and when I made the foolish mistake of showing management how I could make nifty pie charts that could be changed without having to re-run a report every single time, well:

Now I understand that PowerBI and SQL are not everyone’s thing and writing DAX expressions in PowerBI to create meaningful and actionable data is tricky especially with how young the product is. However, once you figure out how it works together, it is capable of creating very pretty dynamic reports that managers can consume and you can turn into actionable data when remediating non-compliant devices.

To help you out so that you don’t have to spend hours trying to figure out how to report on patch compliance I’ve created a PowerBI template you can download from the TechNet Gallery:

https://gallery.technet.microsoft.com/Patch-Compliance-Reporting-a08f0fb6

Here’s a screenshot of it in my lab environment:

Now anytime I present a report to management, I always have to make sure I explain very carefully what the report means because data without context leads to sad times for everyone involved. So let us take a look at the SQL Query that collects the data first.

This query is set to look for the following criteria, and this information can be changed. If there is enough interest, I will include parameters within PowerBI to allow it to be changed without modifying the SQL source code and take other user feedback for improvements. I’ve intentionally written it this way to start with a smaller data set to avoid overwhelming the database.

  • Updates must be in a state of deployed for a device to be graded against the update
    • Devices will NOT be graded if a patch is not deployed but WILL be graded if the update is deployed ANYWHERE in the environment regardless of the target.
  • Compliance information will only return if the Operating System name includes ‘%Server%’
    • This can be changed by modifying ‘V_R_system.Operating_System_Name_and0 like ‘%Server%’ to ‘V_R_system.Operating_System_Name_and0 like ‘%Workstation%’ – Note this will only return workstations and depending on the size of your environment and cleanliness of DB may impact performance. I recommend testing the Query in SQL Server Management Studio before using the report OR changing the query.
  • Updates will only be evaluated if they are NOT superseded.
    • This is important for the cumulative rollups and how they function.
  • Updates must have been released between January 1st, 2017 and the date of the query being run.

So you’ve run the SQL query didn’t have any performance issues, installed PowerBI and now you’re ready to use the shiny template to look at your servers and their patch compliance. Fantastic here is a quick video on how to implement.

 

Remember this video assumes you are logged in and using an account that has view rights to the database. If you are trying to do this while logged in with an account that does not have ConfigMgr rights, you can but will need to edit the credentials to use a different windows account and if you are using a non-domain joined device you CAN but you will need to launch the PowerBI.EXE with the runas /netonly command.