SCCM - Security and Critical Updates | Datazen Dashboard

Recently i needed to provide customer a dashboard on the missing patches for every machines in the park - and because managers like the fancy datazen dashboards, and i also like the easy way we can build some dashboards - why not ?

First of all, created this datasource in Datazen CP :

 SELECT    dbo.v_R_System.Name0 AS 'Computername', dbo.v_UpdateInfo.Title AS 'Updatename', dbo.v_StateNames.StateName, dbo.v_UpdateInfo.InfoURL,  
         dbo.v_Update_ComplianceStatusAll.LastStatusCheckTime, dbo.v_UpdateInfo.DateLastModified, dbo.v_UpdateInfo.IsDeployed, dbo.v_UpdateInfo.IsSuperseded,   
         dbo.v_UpdateInfo.IsExpired, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.DateRevised,   
         catinfo.CategoryInstanceName as 'Vendor',  
     catinfo2.CategoryInstanceName as 'UpdateClassification',  
         COUNT(case when catinfo2.CategoryInstanceName like 'Security%' then '1' else NULL end ) IsSecurity,  
         COUNT(case when catinfo2.CategoryInstanceName like 'Critical%' then '1' else NULL end ) IsCritical  
 FROM    dbo.v_StateNames  
         INNER JOIN dbo.v_Update_ComplianceStatusAll  
         INNER JOIN dbo.v_R_System ON dbo.v_R_System.ResourceID = dbo.v_Update_ComplianceStatusAll.ResourceID  
         INNER JOIN dbo.v_UpdateInfo ON dbo.v_UpdateInfo.CI_ID = dbo.v_Update_ComplianceStatusAll.CI_ID ON dbo.v_StateNames.StateID = dbo.v_Update_ComplianceStatusAll.Status  
         INNER JOIN v_CICategories_All catall on catall.CI_ID = dbo.v_UpdateInfo.CI_ID  
         INNER JOIN v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company'  
         INNER JOIN v_CICategories_All catall2 on catall2.CI_ID=dbo.v_UpdateInfo.CI_ID  
         INNER JOIN v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'  
 WHERE    (dbo.v_StateNames.TopicType = 500)  
 AND        (dbo.v_StateNames.StateName = 'Update is required')  
 AND        (dbo.v_R_System.Name0 IN   
           (SELECT TOP (100) PERCENT SD.Name0 AS 'Machine Name'  
             FROM    dbo.v_R_System AS SD INNER JOIN  
                     dbo.v_FullCollectionMembership AS FCM ON SD.ResourceID = FCM.ResourceID INNER JOIN  
                     dbo.v_Collection AS COL ON FCM.CollectionID = COL.CollectionID LEFT OUTER JOIN  
                     dbo.v_R_User AS USR ON SD.User_Name0 = USR.User_Name0 INNER JOIN  
                     dbo.v_GS_PC_BIOS AS PCB ON SD.ResourceID = PCB.ResourceID INNER JOIN  
                     dbo.v_GS_COMPUTER_SYSTEM AS CS ON SD.ResourceID = CS.ResourceID INNER JOIN  
                     dbo.v_RA_System_SMSAssignedSites AS SAS ON SD.ResourceID = SAS.ResourceID  
             ))  
 AND        ((catinfo2.CategoryInstanceName like 'Critical%' ) OR (catinfo2.CategoryInstanceName like 'Security%' ))  
 GROUP BY dbo.v_R_System.Name0 , dbo.v_UpdateInfo.Title, dbo.v_StateNames.StateName,   
         dbo.v_Update_ComplianceStatusAll.LastStatusCheckTime, dbo.v_UpdateInfo.DateLastModified, dbo.v_UpdateInfo.IsDeployed, dbo.v_UpdateInfo.IsSuperseded,   
         dbo.v_UpdateInfo.IsExpired, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.DateRevised,   
         catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, dbo.v_UpdateInfo.InfoURL  

Then created this dashboard :



The challange now is to make this possible to any group of collection we might want to.

I made a post about it ... give it a try:

http://itopstuff.blogspot.pt/2015/11/sccm-missing-updates-per-collection.html

:)