Wednesday 6 April 2011

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









6 comments:

  1. Hi Blair,

    I tried to test your report below and I have a little issue on it. It didn't work for me and it seems I have a problem on this line :
    (dbo.v_R_System.Netbios_Name0 = @ComputerName)
    Don't understand why.

    ReplyDelete
  2. You have created the variable @ComputerName in your report?

    ReplyDelete
  3. You have created the variable @ComputerName in your report?

    How do you do this

    ReplyDelete
  4. Hi John,

    I have just created a blog on how to do this:
    http://blair-muller.blogspot.com/2011/07/how-to-link-two-reports-together.html

    ReplyDelete
  5. Hi Blair,
    Is it possible to build this report using Update Category?
    For instance, I would like only 'Security Updates' to be counted.

    Thank you,
    Vas

    ReplyDelete