I come from Singapore but find myself now living and working in the Netherlands, specialising in SMS 2003 / Configmgr 2007. I enjoy working and connecting people with computers and I hope I can share as much information as I can here through this blog.
“Computers do not just work like that, at the press of a button. From the moment a computer is switched on in your office, magic happens. That’s why you need an IT department and they do more than crawling under your desk to plug in that wire you kicked out.”
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: