Server Software Updates Compliance (Updated)

This an update to the previous post and corrects cases where systems aren't in any of the expected collections.  My boss actually caught that and made the change...  Embarrassing...

 

SELECT

  Name0 AS Server, 

  SUM(Targeted) AS Targeted,

  SUM(Missing) AS Missing,

  SUM(Installed) AS Installed,

  TopUser,

  Collection

FROM

  (SELECT DISTINCT

    sys.Name0,

    ui.Title,

    (CASE WHEN ctm.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS Targeted,
    (CASE WHEN css.Status = 2 THEN 1 ELSE 0 END) AS Missing,
    (CASE WHEN css.Status = 3 THEN 1 ELSE 0 END) AS Installed,
     sys.Operating_System_Name_and0, v_Collection.Name AS Collection,
     v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 AS TopUser
     FROM v_UpdateComplianceStatus AS css
           JOIN v_UpdateInfo AS ui ON ui.CI_ID = css.CI_ID
           JOIN v_R_System AS sys ON css.ResourceID = sys.ResourceID and sys.Operating_System_Name_and0 LIKE '%server%'
           LEFT JOIN vWorkstationStatus AS ws ON ws.ResourceID = sys.ResourceID                        
           LEFT JOIN v_FullCollectionMembership ON css.ResourceID = v_FullCollectionMembership.ResourceID
           LEFT JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
           LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON sys.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID
           LEFT JOIN v_CITargetedMachines AS ctm ON css.ResourceID = ctm.ResourceID AND ctm.CI_ID = css.CI_ID
      WHERE (v_Collection.Name LIKE CHANGEME or v_Collection.Name =CHANGEME or v_Collection.Name is null)
      GROUP BY sys.Name0, ctm.ResourceID, css.Status, ui.Title, sys.Operating_System_Name_and0, v_Collection.Name,
              v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0) AS derived
GROUP BY Name0, TopUser, Collection
ORDER BY Missing DESC