For a specefic project I was searching to get an overview of all the application supersedence in configMgr. I asked on the ConfigMgr Forum and that got me on the right track. (I was using the wrong relationType number).
I you a searching to get an application supersedence using SQL, you mostly want to focus on the vSMS_AppRelation_Flat. It will contain all the CIID’s of the applications and deployment types that are superseded.
Find below a query that I came up with. It will sum all this information into a single and easy to understand table. It will get the application supersedence of the latest applications that are not Tombstoned and not expired.
With CTESuperSedence AS (
select locpropFromapp.DisplayName as FromApp,locpropFromDT.DisplayName as FromDeploymentType,locpropToapp.DisplayName as ToApplication, locpropToDT.DisplayName as ToDeploymentType from vSMS_AppRelation_Flat as appflat
JOIN v_LocalizedCIProperties as locpropFromapp ON locpropFromapp.CI_ID = appflat.FromApplicationCIID
JOIN v_LocalizedCIProperties as locpropFromDT ON locpropFromDT.CI_ID = appflat.FromDeploymentTypeCIID
JOIN v_LocalizedCIProperties as locpropToapp ON locpropToapp.CI_ID = appflat.ToApplicationCIID
JOIN v_LocalizedCIProperties as locpropToDT ON locpropToDT.CI_ID = appflat.ToDeploymentTypeCIID
JOIN v_ConfigurationItems as ciFrom ON locpropFromapp.CI_ID = ciFrom.CI_ID
JOIN v_ConfigurationItems as ciTo ON locpropToapp.CI_ID = ciTo.CI_ID
AND ciFrom.IsTombstoned = 0
AND ciFrom.IsLatest = 1
AND ciFrom.IsExpired = 0
AND ciTo.IsTombstoned = 0
AND ciTo.IsLatest = 1
AND ciTo.IsExpired = 0
SELECt * FROM CTESuperSedence
Comments are of course welcome 🙂