Wednesday, 6 April 2011

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]
            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
            (dbo.v_UpdateComplianceStatus.Status = 2) AND (dbo.v_StateNames.TopicType = 402)
            [Updates Missing]
            ,[Computer Name]


*** 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.


  1. What's the technet source article?

  2. Are you using this in the query designer in SQL management studio or as a query from within SCCM?

  3. here's another good one Blair

  4. Hey Ted, That is the prefect report from a management perspective, thanks for sharing it with me.

    For the Anonymous question. I used the Creating Custom Reports by Using Configuration Manager 2007 SQL Views document.

  5. Great, thanks. Shame MS can't give us what we want in the first place.

  6. Good stuff. how do i target this to a specific collection?