ConfigMgr Patch Compliance With PowerBI – V220.127.116.11
Almost two weeks ago I released a pretty generic report on reporting patch compliance using PowerBI and System Center Configuration Manager and I was shocked at the amount of feedback I got from the community! So first I want to say thank you and then I want to let you all know that I’ve got a new and improved version of the report!
If you just want to go download the version of the report and start playing with it you can find it on TechNet Gallery: Patch Compliance With PowerBI – V18.104.22.168
- Organization Overview Tab – Consolidated view of patch compliance for your organization – Shows Server Patching, Desktop Patching and, ConfigMgr client penetration.
- Workstation Patch Compliance – Previously only had a single tab for Server Patch Compliance (I’m primarily a server admin forgive me).
- Server Client Penetration – A very rough spin into finding clients that don’t have the agent installed filtered by has the device authenticated to AD within X number of days.
- Includes count of client versions in the environment
- Workstation Client Penetration – A very rough spin into finding clients that don’t have the agent installed filtered by if the device has authenticated to AD within X number of Days
- Includes count of client versions in the environment
- Workstation/Server Tab now includes a drop down to filter Articles by membership in a specific Software Update Group.
- Color Theme Change – Based on some conversation with members of the community realized the default colors might be hard to see so the report has been skinned using a custom theme that should hopefully be more color-blind friendly.
- SCConfigMgr Logo – Added the website logo to it, you can of course replace it with your own.
- Additional SQL Queries – In order to support some of the changes in the Major Updates section minor SQL query changes were made, and some new queries were added, more on that later.
- Graphics Changes/improvements
Possible Future Updates:
- Clients Last WSUS Scan time
- Last WSUS Scan Server Location
- Last WSUS Scan Status Code – (And maybe a suggestion what’s broken if something is broken)
- Possibly including devices where path status is ‘unknown’
- Currently, this is not done as it pulls in a very large amount of data as it requires usage of V_Update_ComplianceStatusAll which is a much larger dataset.
- Additional Generic Client Health Maybe?
In order to facilitate a large number of these changes, I had to modify some existing SQL and add an additional query or two. I strongly encourage you to run the queries that power the report on their own in SQL Server Management Studio to assess the impact on your environment especially the query that collects patch compliance information.
SQL Queries Leveraged
Update Compliance Information
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' , CASE v_GS_OPERATING_SYSTEM.ProductType0 WHEN 1 THEN 'WORKSTATION' WHEN 2 THEN 'DOMAIN CONTROLLER' WHEN 3 THEN 'SERVER' END AS 'OSCLASS' , 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'
Client Info Query
select V_R_System.Name0 , CASE V_R_System.Client0 WHEN 1 THEN 'INSTALLED' ELSE 'NOT INSTALLED' END AS CLIENTSTATUS , V_R_System.Client_Version0 , V_R_System.Operating_System_Name_and0 , v_R_System.Last_Logon_Timestamp0 , v_GS_OPERATING_SYSTEM.Caption0 as 'OSIfClientInstalled' from v_R_System Left OUTER Join V_GS_Operating_System on V_R_System.ResourceID = V_GS_Operating_System.REsourceID
Software Update Group Members
with SUGInfo as ( Select V_UpdateInfo.CI_ID , v_UpdateInfo.CIType_ID , V_UpdateInfo.Title from v_UpdateInfo where v_UpdateInfo.CIType_ID = '9' ) Select SUGInfo.Title as 'SoftwareUpdateGroup' , v_CIRelation.FromCIID , v_CIRelation.ToCIID , V_UpdateInfo.ArticleID , V_UpdateInfo.Title from SUGInfo Left Outer Join v_CIRelation on SUGInfo.CI_ID = v_CIRelation.FromCIID Left outer Join v_UpdateInfo on v_CIRelation.ToCIID = V_UpdateInfo.CI_ID
Select V_UpdateInfo.ArticleID , V_UpdateInfo.Title , V_UpdateInfo.InfoURl from V_UpdateInfo Where V_UpdateInfo.ArticleID is NOT Null and V_UpdateInfo.ArticleID !=''
Just as a quick review when you download the template and open it you’ll be greeted by the same prompts as before here is a quick video example of how to change the datasets. I also want to give a big shout out to Maurice Daly for all of his edits and help in making this look more streamlined.
Now, you might be prompted by Native Database Query Security and you’ll be asked for approval to run each query individually. This is a security feature of the product and CAN be disabled. I’ll show you how.
If you want to disable the above annoying query approval from popping up every time you put a new query into PowerBI – I for one do disable it because the only person who puts queries in there is me, and only after I’ve tested them in SSMS – you can do so by doing the following. File->Options and Settings -> Options -> Security
In the security tab of the Options popup un-check the ‘Native Database Queries’ option that requires approval and hit OK. Please, only do this if you know what you’re doing and you are absolutely sure queries that go in PowerBI have been tested.
If you have any questions about what the different rings mean and the criteria I’ve used please feel free to ask questions.
Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan currently works in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.