A SCCM report which shows you how many updates that are assigned to a client which have not been installed

Hi All,

I needed a quick and easy way to know if updates that I have deployed from SCCM have been installed. I needed a report to show the clients that were having trouble with the updates. I couldn’t find anything online and the inbuilt reports did not meant my needs.

I would then provide this report to the Service Desk team to troubleshoot.

I have modified a query from TechNet to give the required results. 

If you don’t see any clients in the report, you can safely ensure all updates have been deployed.

Now you can confidently tell IT management the progress of your updates.

Stayed tuned for a linked report to find out what updates are missing with there state and a report for management showing all updates have been applied.

SELECT        dbo.v_R_System.Netbios_Name0 AS [Computer Name]
            ,COUNT(dbo.v_UpdateComplianceStatus.Status) AS [Updates Missing]
            ,dbo.v_GS_WORKSTATION_STATUS.LastHWScan
FROM       
            dbo.v_R_System
            INNER JOIN
                dbo.v_UpdateComplianceStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateComplianceStatus.ResourceID
            INNER JOIN
                dbo.v_UpdateInfo ON dbo.v_UpdateComplianceStatus.CI_ID = dbo.v_UpdateInfo.CI_ID
            INNER JOIN
                dbo.v_StateNames ON dbo.v_UpdateComplianceStatus.LastEnforcementMessageID = dbo.v_StateNames.StateID
            LEFT OUTER JOIN
                dbo.v_GS_WORKSTATION_STATUS ON dbo.v_UpdateComplianceStatus.ResourceID = dbo.v_GS_WORKSTATION_STATUS.ResourceID
WHERE   
            (dbo.v_UpdateComplianceStatus.Status = 2) AND (dbo.v_StateNames.TopicType = 402)
GROUP BY
            dbo.v_R_System.Netbios_Name0
            ,dbo.v_GS_WORKSTATION_STATUS.LastHWScan
ORDER BY   
            [Updates Missing]
            ,[Computer Name]

Regards,

Blair

*** UPDATE ** 16/08/2011 **


Hi Everyone,

After running the report for our service desk. I noticed the information didn’t look correct.

The report would show systems missing updates. When I checked the sub report it was empty or the message was Successfully Installed update.

Use this query instead of the above:


SELECT     TOP (100) PERCENT dbo.v_R_System.Netbios_Name0 AS [Computer Name], COUNT(dbo.v_R_System.Netbios_Name0) AS [Updates Missing], 
                      dbo.v_UpdateScanStatus.ScanTime, MAX(dbo.v_StateNames.StateName) AS [Last Message]
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_UpdateComplianceStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateComplianceStatus.ResourceID INNER JOIN
                      dbo.v_UpdateInfo ON dbo.v_UpdateComplianceStatus.CI_ID = dbo.v_UpdateInfo.CI_ID INNER JOIN
                      dbo.v_StateNames ON dbo.v_UpdateComplianceStatus.LastEnforcementMessageID = dbo.v_StateNames.StateID INNER JOIN
                      dbo.v_UpdateDeploymentSummary ON dbo.v_UpdateInfo.CI_ID = dbo.v_UpdateDeploymentSummary.CI_ID INNER JOIN
                      dbo.v_UpdateScanStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateScanStatus.ResourceID
WHERE     (dbo.v_StateNames.TopicType = 402) AND (NOT (dbo.v_StateNames.StateName LIKE 'Successfully Installed update'))
GROUP BY dbo.v_R_System.Netbios_Name0, dbo.v_UpdateScanStatus.ScanTime, dbo.v_UpdateComplianceStatus.Status
HAVING      (dbo.v_UpdateComplianceStatus.Status = 2)
ORDER BY [Updates Missing] DESC, dbo.v_UpdateComplianceStatus.Status, dbo.v_R_System.Netbios_Name0


Thee report looks like this. It gives a little more information then the first query.

Our Service Desk team can now order by the last message and only deal with ones that are required.