📘 Overview
In SCCM (Microsoft Endpoint Configuration Manager), gaining a
clear view of your applications, their deployment types, source content, and
install/uninstall behaviors is critical for maintaining a healthy environment.
Whether you are auditing your environment, troubleshooting deployment issues,
or preparing for a migration, having a detailed application inventory is a
must.
In this blog post, we’ll walk through a powerful SQL query
that pulls application metadata, deployment information, content size,
install/uninstall commands, and more — directly from your SCCM database.
🔍 What
This Query Does
This SQL query retrieves:
- Application
name and description
- Deployment
type details
- Source
path and size
- Status
(Active/Retired)
- Install
and uninstall command lines
- Detection
method
- Admin
comments and metadata
All tied together using SCCM’s AppModel and CI
relationships.
📋 The SQL
Query
DECLARE @LocaleID INT = (SELECT LocaleID FROM vSMSData);
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest')
SELECT
app.DisplayName AS
ApplicationName,
pkg.PackageID,
CASE
WHEN
dtt.IsExpired LIKE '1' THEN 'Retired'
ELSE 'Active'
END AS [Application
Status],
cp.SourceSize AS
[Source Size in KB],
app.Description,
ad.AdminComments,
Dtt.DisplayName AS
[DeploymentTypeName],
Dtt.Technology,
dt.CI_ID,
dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Title)[1]', 'nvarchar(max)')
AS [DeploymentTypeName],
ab.NumberOfDeploymentTypes AS [No of Deployments],
dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Installer/Contents/Content/Location)[1]',
'nvarchar(max)') AS [SourcePath],
dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Installer/InstallAction/Args/Arg)[1]',
'nvarchar(max)') AS [InstallCommandLine],
dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Installer/UninstallAction/Args/Arg)[1]',
'nvarchar(max)') AS [UninstallCommandLine],
dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/DetectionMethod/Setting)[1]',
'nvarchar(max)') AS DetectionSetting
FROM v_ConfigurationItems dt
INNER JOIN vSMS_CIRelation rel ON dt.CI_ID = rel.ToCIID
INNER JOIN fn_ListLatestApplicationCIs_List(@LocaleID) app ON
app.CI_ID = rel.FromCIID
INNER JOIN v_Package p ON p.SecurityKey = app.ModelName
INNER JOIN vSMS_ContentPackage cp ON cp.PkgID = p.PackageID
INNER JOIN dbo.Fn_Listdeploymenttypecis(1033) AS Dtt ON
Dtt.AppModelName = app.ModelName
INNER JOIN fn_ListLatestApplicationCIs(1033) AS ab ON
ab.ModelName = app.ModelName
INNER JOIN v_Package pkg ON pkg.SecurityKey = ab.ModelName
INNER JOIN v_Applications ad ON ad.ModelID = app.ModelID
WHERE dt.CIType_ID = 21 -- Deployment Type
AND dt.IsLatest = 1; -- Latest version only
