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 Writing the SQL Queries /SSRS Reports,always have these 2 as reference 1)Configmgr SQL views and 2)SSRS expressions
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.
Is there any way to pull the report for particular month
you can use month ,date ,year that is available in v_updateinfo SQL view.