Configuration Manager 2012: SQL XPATH Query for Deployment Type

Below is an example of how to write a SQL XPATH query to extract details from a deployment type CI contained in an XML data type. In this query we are returning columns for deployment type Title, SDMPackageDigets (XML), Technology (MSI, Script, etc.), Content Location, and Install and Uninstall strings. I basically found an example in this link http://gallery.technet.microsoft.com/scriptcenter/Count-of-EP-Definition-59c06ea2 and added Install and Uninstall columns.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
	CI_ID,
	CI_UniqueID,
	SDMPackageDigest,
    SDMPackageDigest.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"; 
    (p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]', 'nvarchar(max)') AS DTTitle,
    SDMPackageDigest.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"; 
    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/@Technology)[1]', 'nvarchar(max)') AS DTTechnology,
    SDMPackageDigest.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"; 
    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:Location)[1]', 'nvarchar(max)') AS DTContentLocation,       
    SDMPackageDigest.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"; 
    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(MAX)') AS Install,
    SDMPackageDigest.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest"; 
    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:UninstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(MAX)') AS Uninstall
FROM 
	v_ConfigurationItems
WHERE 
	CIType_ID = 21 -- DeploymentType CI's
This entry was posted in Configuration Manager, Microsoft, System Center 2012.

Leave a Reply