NOTE: THIS TEMPLATE HAS BEEN UPDATED AND CAN BE FOUND: https://gallery.technet.microsoft.com/Patch-Compliance-Reporting-95fa01f7

Template for reporting on Patch Compliance in Configuration manager using PowerBI to present results. 

A full write up on this will be available at a later date.

See Below for SQL that is used:

SQL
Edit|Remove
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 should return the compliance status of all updates against all servers since the beginning of 2017 that are in a state of deployed for servers. There is an additional filter on the updates in the PowerBI report that only returns patches with the word 'server' in the title to help filter and focus the report around operating system level security. The SQL query excludes patches that are in a state of superseded and patches that are of type '8' (Software Update Group fake patches). This information takes approximately 30 seconds to be collected in an environment of 150K endpoints - 10K Servers - 140K Desktops give or take so performance impact should be minor please run the query using SSMS prior to importing and attempting with PowerBI as performance impact may vary depending on your database grooming practices. 
All information is then aggregated inside of PowerBI using DAX expressions to create a dynamic report.
Current supported Dynamic Options NOTE: OPTIONS DO COMPOUND ON EACH OTHER:
ScreenShot of PowerBI Template in use in a lab: