SQL Script to List SCCM Applications Details

---

📘 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