You may find yourself in situations where you may need to locate the physical location of a certain software update and or check if that has expired. However, it can get cumbersome to determine the right binary just by searching by its KB Article number in Windows Explorer on the SCCM Server. Fret not though, there is a way you can do both by mapping the Content Unique ID to the Unique Update ID from the v_UpdateInfo table so that you can get the logical binary file name and expiry status (Not Expired = 0 and Expired = 1). The logicalname can then be used to search for the specific patch in the software updates location on the SCCM server. A WHERE clause can also be added so that you can search by Bulletin ID for example or even Article ID. The following query should be queried against your SCCM Database server. Extract the results for the LogicalName and use that to search for your patches. This process will be changed in Configmgr 2012, so enjoy while it lasts. Have fun!
WITH XMLNAMESPACES ('http://schemas.microsoft.com/SystemsCenterConfigurationManager/2006/03/24/DesiredConfiguration' as dcm) SELECT ui.CI_ID, ui.ArticleID, ui.BulletinID, ui.CI_UniqueID, ui.ISExpired,SUBSTRING(d.ref.value(N'@LogicalName','VARCHAR(40)'),5,36) AS LogicalName, ui.Title, ui.DescriptionFROMdbo.v_UpdateInfo ui CROSS APPLY ui.SDMPackageDigest.nodes(N'//dcm:SoftwareUpdateReference') AS d(ref)WHERE BulletinID = 'MS11-001'ORDER BY ArticleID, BulletinID, LogicalName
**The above procedure was passed on courtesy of a certain 'Hammer of Justice' Rob Marshall. It made life easier then and therefore, all attribution and acknowledgements to him, as it would come to naught without him. :)
Ah thanks Lionel, the code snippet does the job perfectly, but I take no credit, as it originaly came from a certain John Nelson (No2), who is a bit awesome with SQL. See more of John's stuff here:
Powered by Zimbra