SCCM Configmgr 2012 How to Extract information from XML file stored in SQL DB for application properties


Last week,I was working on request to get the application information which includes Application Name ,Deployment Type ,Content location,Command lines (install and uninstall),what type of deployment is it,detection type,user requirement  and other information. Since there are no default reports to get this kind of information ,you must consider writing new SQL/SSRS report else go manual procedure to write down the information for the applications.

In Configmgr 2007 and Configmgr 2012 ,the information about packages like installation command line,uninstallation,requirements (windows 7,windows 8) and other properties can be retried directly from SQL database without any trouble but if you consider the same requirement for applications,its different story.

In configmgr 2012,the application information is not stored directly as you see for packages ,instead it is stored in .XML file (SDMPackageDigest) .So retrieving this information might require additional SQL skills .It took little while for me to figure this out. Information about applications and its properties is stored in different views or I would call them as functions. Some of them are v_ConfigurationItems, dbo.fn_ListApplicationCIs(1033),dbo.fn_ListDeploymentTypeCIs 1033),dbo.fn_ListApplicationCIs_List(1033) etc.

Couple of reference articles helped me to achieve this report are : and

Lets jump into the subject line to extract the information stored in XML file .

AS I said above,the application properties includes deployment type,install,uninstall and properties that you see within the application+deployment type are stored in table with column called:SDMPackageDigest ,For more ,please read here.


AS I highlighted in above screen in red color,all the fields information is stored in one XML file for each deployment type. For ex: if you have application with 3 deployment types,you will have 3 XML files that store the information.

To see how the XML store the information ,you can simply run the below Query to display top 10 application information and click on the SDMPackageDigest. It will pop-up XML file .

select TOP 10 * from vCI_ConfigurationItems where CIType_ID = '21'

CIType_ID=21 is for applications.

XML File:


To create report, I like to use functions (dbo.fn_ListApplicationCIs(1033) and dbo.fn_ListDeploymentTypeCIs(1033) ) to retrieve the information instead of using views as functions always executes faster compare to views.

I have presented the most used and limited information in this report .You can still add other information which you might be interested from the XML file using the same logic .

SQL Query:

You can take the below SQL syntax to create SSRS Report (I will post the RDL file soon ) or run this query in  SQL server Management studio .

A.[App Name],max(A.[DT Name])[DT Title],A.Type
,A.ContentLocation ,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn
from (
SELECT LPC.DisplayName [App Name]
,(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]', 'nvarchar(max)')) AS [DT Name]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/@Technology)[1]', 'nvarchar(max)') AS [Type]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:Location)[1]', 'nvarchar(max)') AS [ContentLocation]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(max)') AS [InstallCommandLine]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:UninstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(max)') AS [UninstallCommandLine]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[3]', 'nvarchar(max)') AS [ExecutionContext]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[4]', 'nvarchar(max)') AS [RequiresLogOn]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[8]', 'nvarchar(max)') AS [UserInteractionMode]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:OnFastNetwork)[1]', 'nvarchar(max)') AS [OnFastNetwork]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:OnSlowNetwork)[1]', 'nvarchar(max)') AS [OnSlowNetwork]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:DetectAction/p1:Provider)[1]', 'nvarchar(max)') AS DetectAction
dbo.fn_ListApplicationCIs(1033) LPC
RIGHT Join fn_ListDeploymentTypeCIs(1033) LDT ON LDT.AppModelName = LPC.ModelName
where LDT.CIType_ID = 21 AND LDT.IsLatest = 1
) A
GROUP BY A.[App Name],A.Type,A.ContentLocation,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn,A.UserInteractionMode,



when you copy paste ,take care of quotes (‘), sometimes blog convert them to fancy quotes.

Download the SSRS Report (.RDL) file from TechNet Gallary here .

Upload the RDL file to your SSRS Report folder,change the data source,run the report.

2 Responses to "SCCM Configmgr 2012 How to Extract information from XML file stored in SQL DB for application properties"

Leave a Reply