Obtaining binary locations for your patches with the Unique IDs in SCCM

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.Description
FROM
dbo.v_UpdateInfo ui
 CROSS APPLY ui.SDMPackageDigest.nodes(N'//dcm:SoftwareUpdateReference') AS d(ref)
WHERE BulletinID = 'MS11-001'
ORDER BY
 ArticleID, BulletinID, LogicalName


Note!!

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