SCCM Queries

Application DeploymentTypes; with REquirements, (up to 19; add more if you have more); like OS types as a requirement:

If(OBJECT_ID('tempdb..#TempAppRequirements') Is Not Null)

Begin

Drop Table #TempAppRequirements

End





create table #TempAppRequirements(

CI_UniqueID  nvarchar (Max),

AppName nvarchar(max),

DTTitle nvarchar(max),

Requirements1  nvarchar(max),

Requirements2  nvarchar(max),

Requirements3  nvarchar(max),

Requirements4  nvarchar(max),

Requirements5  nvarchar(max),

Requirements6  nvarchar(max),

Requirements7  nvarchar(max),

Requirements8  nvarchar(max),

Requirements9  nvarchar(max),

Requirements10  nvarchar(max),

Requirements11  nvarchar(max),

Requirements12  nvarchar(max),

Requirements13  nvarchar(max),

Requirements14  nvarchar(max),

Requirements15  nvarchar(max),

Requirements16  nvarchar(max),

Requirements17  nvarchar(max),

Requirements18  nvarchar(max),

Requirements19  nvarchar(max)

)



;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)

INSERT INTO #TempAppRequirements(CI_UniqueID,AppName,DTTitle,Requirements1,Requirements2,Requirements3,Requirements4,Requirements5,Requirements6,Requirements7,Requirements8,Requirements9,Requirements10

,Requirements11,Requirements12,Requirements13,Requirements14,Requirements15,Requirements16,Requirements17,Requirements18,Requirements19)

Select dt.CI_UniqueID,

app.DisplayName,

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]', 'nvarchar(max)')) as [DTTitle],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[1]', 'nvarchar(max)')) as [Requirements1],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[2]', 'nvarchar(max)')) as [Requirements2],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[3]', 'nvarchar(max)')) as [Requirements3],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[4]', 'nvarchar(max)')) as [Requirements4],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[5]', 'nvarchar(max)')) as [Requirements5],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[6]', 'nvarchar(max)')) as [Requirements6],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[7]', 'nvarchar(max)')) as [Requirements7],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[8]', 'nvarchar(max)')) as [Requirements8],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[9]', 'nvarchar(max)')) as [Requirements9],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[10]', 'nvarchar(max)')) as [Requirements10],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[11]', 'nvarchar(max)')) as [Requirements11],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[12]', 'nvarchar(max)')) as [Requirements12],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[13]', 'nvarchar(max)')) as [Requirements13],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[14]', 'nvarchar(max)')) as [Requirements14],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[15]', 'nvarchar(max)')) as [Requirements15],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[16]', 'nvarchar(max)')) as [Requirements16],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[17]', 'nvarchar(max)')) as [Requirements17],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[18]', 'nvarchar(max)')) as [Requirements18],

(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[19]', 'nvarchar(max)')) as [Requirements19]

from dbo.fn_listdeploymenttypeCIs(1033) as dt

    join dbo.fn_ListLatestApplicationCIs(1033) as app on app.modelname=dt.appmodelname

       join fn_ListDeploymentTypeCIs(1033) LDT  ON LDT.AppModelName = app.ModelName

       where dt.islatest=1 and app.islatest=1 and ldt.IsLatest=1



       Select * from #TempAppRequirements



If(OBJECT_ID('tempdb..#TempAppRequirements') Is Not Null)

Begin

Drop Table #TempAppRequirements

End

>Applications and their dependencies set?

Please see whether the links can help us:

Show Dependent Applications via (a) PowerShell (Form)
https://gallery.technet.microsoft.com/Show-Dependent-Applications-714cc3e6

SCCM Configmgr 2012 SSRS Report Applications and its Dependencies
https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-SSRS-6db68c4c