Share via

How to query Configuration Manager 2012 XML Data with SQL - XQuery

  • Article

Sorry, but I had to divert from my Application Model review to discuss XML data in the CM 2012 database. At some point you will start to look at the data and want to generate some custom reports. Perhaps it’s through Report Builder or some killer application / utility that you are working on.  I thought I share some of my field experiences to save you time and frustration.

Some views/tables in CM2012 will store data as XML. You can find out the data type by using the sp_help function. For example:

sp_help v_configurationitems

image

You can see that SDMPackageDigest is a data type of XML. I wonder what’s in there? HMM.. let’s go look.

Looks familiar? My XML Notepad test package.

image

From there you can generate any type of report necessary using Report Builder. This sample reports (shout out thanks to Lou Smathers for the report) shows all Application Deployment Type versions along with the Application Version. This was pulled with the following SQL Statement.

 ;WITH XMLNAMESPACES ( DEFAULT 'https://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest') 

 select  App.DisplayName, App.SDMPackageDigest.value('(/AppMgmtDigest/Application/DeploymentTypes/DeploymentType/@Version)[1]', 'nvarchar(10)') [Version]   

 

 ,sdmpackageversion 

 from fn_ListApplicationCIs(1033) App 

 where sdmpackageversion =  (SELECT MAX (sdmpackageversion) 

                             FROM fn_ListApplicationCIs(1033)App2 

                            WHERE app.modelid = App2.modelid) 

 order by DisplayName

DepType

Now for the IMPORTANT PAY ATTENTION information.

1. All Syntax is CASE and Whitespace Sensitive.  Review Here.

Example:

image

This will not work and return NULL

image

Case must Match. Title

Here is the correct SQL

 

 ;WITH XMLNAMESPACES ( DEFAULT 'https://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' )

 select SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Title)[1]', 'nvarchar(MAX)') [Title] 

  from v_ConfigurationItems

 where CIType_ID = 21

image

With xQuery and following the above syntax you can retrieve the data stored in the xml. For attributes you need to change like as follows:

 select SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Title/@ResourceId)[1]', 'nvarchar(MAX)') [ResourceID]

 

image

Here is an example where I pulled information for Applications from the v_configurationItems, XML Column, SDMPackageDigest. Note that you can use the Where and AND with the xQuery as well.

image

xQuery is so much fun! I hope this post has been useful. Now back to the Application Model in Configuration Manager 2012.

For those of you are curious, this is the first sql query I used to extract data.

image

Yes, my SQL Server did call me up and cry just a little bit then asked me to look at xQuery.

Enjoy xQuery!

Comments

  • Anonymous
    October 24, 2012
    Is it possible to query the SCCM Application Detection Method specified in an application(Registry Detection Type), the data is stored under RegistryDiscoverySource which is stored under p1:EnhancedDetectionMethod/Settings/SimpleSetting/RegistryDiscoverySource.  The examples I have seen obtain the ProductCode stored under the EnhancedDetectionMethodSettings, but the Application detection information is stored at additional levels lower than Settings and changing the examples from  EnhancedDetectionMethod/Settings to EnhancedDetectionMethod/Rule/Expression/Operands/ConstantValue does not yield the registry key data.