I wrote a roll up summary of Compliance 5 to summarize missing/installed on a group of machines. I never noticed it before, but the number of installed patches varies greatly across similar OS's, including some built on the same day and having just baseline software installed. Has anyone else noticed this and possibly know why this happens?
Here is the main query I'm using:
SELECT CS.Name0 AS System, CS.UserName0 AS 'User', SUM(CASE WHEN UCS.Status = 2 THEN 1 ELSE 0 END) AS Missing, SUM(CASE WHEN UCS.Status = 3 THEN 1 ELSE 0 END) AS Installed, ROUND( (SUM(CONVERT(float, CASE WHEN UCS.Status = 3 THEN 1 ELSE 0 END)) / (SUM(CONVERT(float, CASE WHEN UCS.Status = 2 THEN 1 ELSE 0 END)) + SUM(CONVERT(float, CASE WHEN UCS.Status = 3 THEN 1 ELSE 0 END)) )) * 100, 2) AS Compliance, WS.LastHWScan FROM v_Update_ComplianceStatus AS UCS LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM AS CS ON CS.ResourceID = UCS.ResourceID JOIN v_CICategories_All AS CI ON CI.CI_ID = UCS.CI_ID JOIN v_CategoryInfo AS NFO ON NFO.CategoryInstance_UniqueID = CI.CategoryInstance_UniqueID AND NFO.CategoryTypeName = 'UpdateClassification' LEFT JOIN v_GS_WORKSTATION_STATUS AS WS ON WS.ResourceID = CS.ResourceID LEFT JOIN v_FullCollectionMembership AS FCM ON FCM.ResourceID = CS.ResourceID WHERE (UCS.Status IN (2, 3)) AND (CS.Name0 LIKE '<SomeServer>%') AND FCM.CollectionID = '<SomeCollectionID>' AND NFO.CategoryInstanceName = 'Critical Updates' GROUP BY CS.Name0, CS.UserName0, WS.LastHWScan
There is also a screenshot at sccmbrokeit.blogspot.com/2013/11/improved-sccm-2012-srss-microsoft.html. You can see in the last screenshot (this only show 'critical updates') that then number installed varies from 6 to 27, but there are several that have either 6 or 18 installed that were installed at roughly the same time with the same application loads. I see the same pattern on DC's as well which have strict build proceedures and were last refreshed within a few days of each other. For the life of me I just can't see any differences on the machine that would cause different reporting. Thanks all,
--Dave
- Edited by DaveB22 Monday, November 11, 2013 8:56 PM Typo in CS.Name0 LIKE '<SomeServer>%'