There was a question raised by someone on MyItform list ,asking for ‘is there a way to get superseded patch list’ for all windows 7”. when I read the question ,I was thinking ,we can get this using the default reports but when I check the default reports,there is no such report that match this requirement .
So ,I sat for sometime in the lab ,started writing the SQL Query ,found something . So thought of writing this blog post that helps others who are in similar needs.
If you want to know particular patch (ex:Cumulative Update for Windows 10 Version 1511 (KB3124200)) superseded by what software updates in SCCM/Configmgr ,you can simply browse Software Library /Software updates nodes,search with this title ,right click on the Software Update Properties ,Go to Supersedence Information ,You can see, this software update is superseded by (To replace ) also ,if this software update supersedes by any other update .
This method is easy if you want to check for specific software update but,what if you want to have a list of software updates that are superseded by what software updates ?
In Configmgr ,Software update information is scattered across multiple tables/views and depends on your requirement ,you must choose right table to query the information.
For our requirement, we will be retrieving the software update information from V_updateinfo and superseded information from v_CIRelation_all.
Based on the above 2 SQL views, I will be retrieving the Software updates that are superseded by what updates.
UI2.Title [S Title],ui2.IsSuperseded,UI2.BulletinID [S BulletinID],UI2.InfoURL [S InfoURL]
from v_CIRelation_all CA
left join v_UpdateInfo UI1 on CA.ReferencedCI_ID=UI1.CI_ID
left join v_UpdateInfo UI2 on ca.CI_ID=ui2.CI_ID
and UI1.title like '%Windows 10%'
If you want to list only windows 7 updates ,replace 10 with 7 .if you want to list all Software updates irrespective of OS ,then simply comment the last line using --
you can use this SQL Code to put in SSRS Reports with your customizations.