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

1. 

   Mandar Aatave on July 11, 2025 7:29 PM

   Created a powershell script to find out the same data using powershell. Thanks for logic.

   [string]$SourceRoot = "\e75lapwp6148v\DSL$\Applications",
   [string]$LogFile = "C:\Temp\Logs\SCCM_Content_Cleanup_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

   Create log directory if needed

   $logDir = Split-Path $LogFile
   if (-not (Test-Path $logDir)) {
   New-Item -Path $logDir -ItemType Directory -Force | Out-Null
   }

   function Log {
   param([string]$Message)
   $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
   $entry = "$timestamp - $Message"
   Write-Output $entry
   Add-Content -Path $LogFile -Value $entry
   }

   -----------------------------------

   Step 1: Connect to SCCM Site

   -----------------------------------

   try {
   Import-Module ($env:SMS_ADMIN_UI_PATH.Substring(0,$env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager\ConfigurationManager.psd1') -ErrorAction Stop
   $SiteCode = Get-PSDrive -PSProvider CMSite | Select-Object -First 1 -ExpandProperty Name
   Set-Location "$SiteCode`:"
   Log "Connected to SCCM Site: $SiteCode"
   } catch {
   Log "ERROR: Could not load ConfigurationManager module or connect to site."
   exit 1
   }

   -----------------------------------

   Step 2: Parse used content paths from XML

   -----------------------------------

   Log "Parsing content paths from SDMPackageXML..."

   $usedPathsSet = [System.Collections.Generic.HashSet[string]]::new()

   $applications = Get-CMApplication

   foreach ($app in $applications) {
   $deploymentTypes = $app | Get-CMDeploymentType

   foreach ($dt in $deploymentTypes) {
try {
[xml]$xml = $dt.SDMPackageXML

   $nsmgr = New-Object System.Xml.XmlNamespaceManager $xml.NameTable
   $nsmgr.AddNamespace("ns", "http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest")

   $locationNodes = $xml.SelectNodes("//ns:DeploymentType/ns:Installer/ns:Contents/ns:Content/ns:Location", $nsmgr)

   foreach ($node in $locationNodes) {
$path = $node.InnerText.TrimEnd('\')
if ($path -like "$SourceRoot*") {
if ($usedPathsSet.Add($path)) {
Log "Found content path: $path"
}
}
}
} catch {
Log "ERROR parsing XML for deployment type '$($dt.LocalizedDisplayName)': $_"
}
}


   }

   Log "Total used content paths found: $($usedPathsSet.Count)"

   Reply
   • 

     Eswar Koneti on July 13, 2025 9:37 AM

     Hi Mandar,
     Thank you for time and sharing the solution.

     Regards,
     Eswar

     Reply
2. 

   DanZi on February 2, 2022 6:32 AM

   Probably a long abandoned thread, but just wondering if there was a way to standardize all msiexec install and uninstall commands, say add or replace all /L*v switches with the desired log folder for every package... could be a long stretch...?

   Reply
   • 

     Eswar Koneti on February 23, 2022 8:58 AM

     Hi Danzi,
     if you want to replace the command line syntax for all MSI applications, you can use the PowerShell script or community tools.
     Making changes to the database is not supported unless you go through the Microsoft support engineer.

     Thanks,
     Eswar

     Reply
3. 

   Eric van Voorthuizen on July 30, 2020 5:00 PM

   Thx Eswar, you're article was one of the many articles I red to reach my own goal to have an SQL query for creating a temporary table to use with info about apps which are superseeded with the Uninstall selected.
   I need this info for our application archiving process as an extra argument to not archive apps which are superseeded and has the option Uninstall checked in the superseed.

   I know this is an outdated article but just to help others I will contribute my query:

   /*
   We must drop the temporary tables if they already exist.
   */
   IF Object_id('tempdb..#AppsAndThereSupersedes') IS NOT NULL
   DROP TABLE #AppsAndThereSupersedes

   /*
   Get All DT's from superseeded apps which Action Uninstall is TRUE, #SuperseedingDTS.
   DTAppRef = App ID which is superseeded
   DTRef = DT ID from superseeded App
   DTUninstall = Is superseed with Uninstall
   */
   ;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest')
   SELECT DISTINCT DTName,
   DTAppRef,
   DTRef,
   DTUninstall
   INTO #AppsAndThereSupersedes
   FROM (
   SELECT SDMPackageDigest AS objXML,
   SDMPackageDigest.value('(/AppMgmtDigest/*:DeploymentType/*:Title)[1]', 'nvarchar(256)') AS DTName,
   (DTAppRef.X.value('./@LogicalName','nvarchar(256)')) AS DTAppRef,
   (DTRef.X.value('./@LogicalName','nvarchar(256)')) AS DTRef,
   (DTRef.X.value('./@Changeable','nvarchar(256)')) AS DTUninstall
   FROM vCI_ConfigurationItems
   CROSS APPLY SDMPackageDigest.nodes('/AppMgmtDigest/DeploymentType/Supersedes/*:DeploymentTypeRule/*:DeploymentTypeIntentExpression/*:DeploymentTypeReference/.') AS DTRef(X)
   CROSS APPLY SDMPackageDigest.nodes('/AppMgmtDigest/DeploymentType/Supersedes/*:DeploymentTypeRule/*:DeploymentTypeIntentExpression/*:DeploymentTypeApplicationReference/.') AS DTAppRef(X) ) AS TEMP
   SELECT *
   FROM #AppsAndThereSupersedes
   ORDER BY DTName ASC

   Reply
   • 

     Eswar Koneti on August 6, 2020 10:14 AM

     Hi Eric,
     Appreciate your contribution and helping others.

     Thanks,
     Eswar

     Reply

4. Pingback: How to find a Configuration Manager application that uses a specific file for install – System Center Configuration Manager Notes

5. 

   Marc Jolley on April 23, 2018 11:00 PM

   I never leave comments on stuff. But this is some of the coolest and most useful SQL I've ever encountered. And it just works! Thank you so much.

   Reply
   • 

     Eswar Koneti on July 8, 2018 8:47 PM

     Thanks for the feedback and glad you liked it.

     Regards,
     Eswar

     Reply
6. 

   Russ Rimmerman on June 13, 2017 3:17 AM

   Hey Eswar, this is great stuff. Can you elaborate how to grab the Requirements defined on the deployment type? I'm having challenges getting the XML pathing just right. If I add "Memory > 1MB", I get the value "GreaterThan" back but not the 1MB.

   Reply
   • 

     Eswar Koneti on July 11, 2017 4:15 PM

     Hi Russ,
     you can use the following SQL code to get the requirements tab but it is not dynamic means, if you have set multiple requirements ,you need to that many times in the SQL.

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

     Full SQL Code:

     ;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)
     SELECT
     A.[App Name],max(A.[DT Name])[DT Title],A.Type
     ,A.ContentLocation ,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn
     ,A.UserInteractionMode,A.OnFastNetwork,A.OnSlowNetwork,A.DetectAction ,A.Requirement1,A.Requirement2
     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
     ,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[1]', 'nvarchar(max)') AS [Requirement1]
     ,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[2]', 'nvarchar(max)') AS [Requirement2]
     FROM
     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,
     A.OnFastNetwork,A.OnSlowNetwork,A.DetectAction,A.Requirement1,A.Requirement2

     I am not sure how to make the requirements column as dynamic .

     Reply
7. 

   Nicke Källén on August 5, 2015 2:59 AM

   Great article Eswar. Really useful!

   Reply
   • 

     Eswar Koneti on August 12, 2015 2:30 PM

     Thanks Nicke 😉

     Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.