Server software updates compliance report

The server team asked me to come up with a report showing the number of software updates missing and applied.  This will list all servers with a count of targeted, missing, and installed software updates.  In addition it'll list the collections that they are in, I added that to troubleshoot why some systems weren't getting updates.

This was my first trip into the compliance tables in SCCM 2012. They are mostly the same as 2007, though there are a couple new views that are worth looking at.  I listed the other sites I used as a reference, but the queries directly on those sites weren't really working for me.  So I ended up using a SUM( ) instead of COUNT( ) and tweaking things a bit to get it to run somewhat quicker. 

I'm sure there is some more optimization that could be done, but this works for the time being:

SELECT     Name0 AS Server, SUM(Targeted) AS Targeted, SUM(Missing) AS Missing, SUM(Installed) AS Installed, 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
                       FROM          v_UpdateComplianceStatus AS css INNER JOIN
                                              v_UpdateInfo AS ui ON ui.CI_ID = css.CI_ID INNER JOIN
                                              v_R_System AS sys ON css.ResourceID = sys.ResourceID INNER JOIN
                                              v_FullCollectionMembership ON css.ResourceID = v_FullCollectionMembership.ResourceID INNER JOIN
                                              v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID LEFT OUTER JOIN
                                              v_CITargetedMachines AS ctm ON css.ResourceID = ctm.ResourceID AND ctm.CI_ID = css.CI_ID
                       WHERE      (ui.CIType_ID IN (1, 8)) AND (sys.Operating_System_Name_and0 LIKE '%server%') AND (v_Collection.Name LIKE 'All %')
                       GROUP BY sys.Name0, ctm.ResourceID, css.Status, ui.Title, sys.Operating_System_Name_and0, v_Collection.Name) AS derived
GROUP BY Name0, Collection
ORDER BY Missing DESC

v_UpdateInfo.CIType 1 and CIType 8 indicate a software update and software update bundle respectively.  Don't for get the bundles, or you'll be missing a bunch of stuff.

The v_Collection.Name LIKE 'All %' will have to be changed to your environment.  My original query used a prefix that we you to indicate maintenance windows not 'All', but it would have probably not shown any results if you copied and pasted it...

The rest should be pretty self-explanatory. 

A couple sites that helped me along the way:
http://technet.microsoft.com/en-us/library/dd334594.aspx
http://www.myitforum.com/forums/SCCM-Compliance-Report-Help-m230864.aspx
http://anotherblabla.wordpress.com/2012/03/06/sccm-usefull-software-update-reports/
http://anotherblabla.wordpress.com/2012/12/09/sccm-2012-compliance-state-for-a-specific-computer-custom-report/