Tuesday, April 26, 2011

SQL query for Patches required systems as per collection with the SIZE of each update

Below is sql query for  Patches required systems as per collection with the SIZE of each update

 

SELECT DISTINCT
                      TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code], UI.BulletinID, UI.ArticleID, UI.Title,
                      dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionID, UI.CI_ID, dbo.CI_Contents.SourceSize /(1024.0*1024) AS SizeinMB
FROM         dbo.v_UpdateContents INNER JOIN
                      dbo.v_FullCollectionMembership INNER JOIN
                      dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
                      dbo.v_R_System AS SYS LEFT OUTER JOIN
                      dbo.v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID INNER JOIN
                      dbo.v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID ON dbo.v_FullCollectionMembership.ResourceID = UCS.ResourceID ON
                      dbo.v_UpdateContents.CI_ID = UI.CI_ID INNER JOIN
                      dbo.CI_Contents ON dbo.v_UpdateContents.Content_ID = dbo.CI_Contents.Content_ID
WHERE     (UCS.Status IN ('2')) AND (dbo.v_Collection.CollectionID = 'HCC000FB')
ORDER BY UI.ArticleID

No comments:

Post a Comment