A sub report that shows you which updates are missing

Hi All,

As promised I have extended the report to show you which updates are missing.

You can link it to the above report by computer name. So when you click on the computer name it displays this report.

SELECT  
        dbo.v_UpdateInfo.BulletinID
        ,dbo.v_UpdateInfo.ArticleID
        ,dbo.v_UpdateInfo.Title
        ,dbo.v_StateNames.StateName AS State
        ,dbo.v_UpdateDeploymentSummary.AssignmentName AS [Update List]
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
WHERE   
        (dbo.v_UpdateComplianceStatus.Status = 2) AND (dbo.v_StateNames.TopicType = 402) AND (dbo.v_R_System.Netbios_Name0 = @ComputerName)
ORDER BY
        dbo.v_R_System.Netbios_Name0, dbo.v_UpdateComplianceStatus.Status

Please feel free to drop me a comment if you have any questions or comments.

Regards,

Blair

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

Hi Everyone,

I have changed the above query to remove the Successfully Installed Update state. Use the below query:

SELECT     TOP (100) PERCENT dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Title, dbo.v_StateNames.StateName AS State, 
                      dbo.v_UpdateDeploymentSummary.AssignmentName AS [Update List]
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
WHERE     (dbo.v_UpdateComplianceStatus.Status = 2) AND (dbo.v_StateNames.TopicType = 402) AND (dbo.v_R_System.Netbios_Name0 = @ComputerName) AND 
                      (NOT (dbo.v_StateNames.StateName LIKE 'Successfully Installed update'))
ORDER BY dbo.v_R_System.Netbios_Name0, dbo.v_UpdateComplianceStatus.Status