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:
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.
Select v_R_System.name0 as 'Name' , v_RA_System_ResourceNames.Resource_Names0 as 'FQDN' , v_R_System.Resource_Domain_OR_Workgr0 as 'DOMAIN' , v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System' , V_UpdateInfo.ArticleID , CASE WHEN v_Update_ComplianceStatus.Status = '2' THEN 'MISSING' WHEN v_Update_ComplianceStatus.Status = '3' THEN 'INSTALLED' else 'UNKNOWN' END AS 'PatchStatus' , V_UpdateInfo.DateCreated , V_UpdateInfo.Title , V_UpdateInfo.InfoURL , V_R_System.ResourceID from v_R_System left join v_Update_ComplianceStatus on v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID left join V_UpdateInfo on v_Update_ComplianceStatus.CI_ID = V_UpdateInfo.CI_ID left join v_RA_System_ResourceNames on v_R_System.ResourceID = v_RA_System_ResourceNames.ResourceID left join v_UpdateScanStatus on v_R_System.ResourceID = v_UpdateScanStatus.ResourceID left join v_GS_WORKSTATION_STATUS on v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID left Join v_GS_OPERATING_SYSTEM on v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID where V_UpdateInfo.DateCreated BETWEEN '2017-01-1 00:00:00.00' and GetDATE() and v_UpdateInfo.IsDeployed = '1' and v_UpdateInfo.CIType_ID = '8' and v_UpdateInfo.IsSuperseded = '0' and V_r_System.Operating_System_Name_and0 like '%server%'
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.