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: