Report Updates Compliance 5 - Specific System across similar machines

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>%'
November 11th, 2013 6:01pm

Hi,

Have you compared two similar computers logs when you deploy same updates? (UpdatesHandler.log, UpdatesStore.log, UpdatesDeployment.log)

Best Regards,

Joyce Li

Free Windows Admin Tool Kit Click here and download it now
November 13th, 2013 12:45pm

Yeah, the more I look into it on one out of about 8 similar machines a bunch of patches just evaluate 'Not Required'.  Which is odd, they look like they should be required to me, all machines are at the same SP level etc.  I'll post back as I get more into it.
November 14th, 2013 8:00pm

Yes, I know this is an old post, but Im trying to clean them up. Did you solve this problem, if so what was the solution?

Free Windows Admin Tool Kit Click here and download it now
February 1st, 2015 12:52pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics