Find SCCM Application Deployment Details using SQL Query

Let’s see how to find SCCM application deployment details using SQL query. Using the SQL query you can find application deployment details such as Application CI ID, Application Unique ID, Deployment Type Unique ID and much more.

One of the important feature of SCCM is application deployment. You can deploy applications and packages to users and devices using SCCM.

When you deploy an Application, the basic application details include the name of the application, version, Application CI ID, Application Unique ID etc. These details are very much required to troubleshoot application deployments in SCCM.

Most of these details are entering while packaging the application in SCCM. However, there are other details which you might need to know when you want to troubleshoot application deployments in SCCM.

There is an easy way to find the basic application details, and you can quickly find these details from the SCCM console. Go to Software Library\Overview\Application Management\Applications. To view the application details, right-click any application and select Properties.

SQL queries are useful particularly when you want to list all SCCM packages and find the size of applications packaged in ConfigMgr.

Using a SQL query, you can find all the application details in SCCM. Yes, it is also possible to find the applications without deployments.

Troubleshoot Application Deployments in SCCM

After you deploy an application, the following details are required for troubleshoot application deployments in SCCM:

• Application CI ID
• Application Unique ID
• Deployment Type Unique ID
• Application Deployment Unique ID (also known as Assignment Unique ID)
• Application Deployment Purpose
• Content Unique ID
• Collection ID and Name
• Collection Type

The SQL query that we are going to use here will list all the application deployment details and provides other details that will help troubleshoot application deployments in SCCM.

Find SCCM Application Deployment Details using SQL Query

Using a SQL query, you can find SCCM application deployment details with following steps:

• Launch the SQL Server Management studio (SSMS) and connect to database engine.
• Expand Databases and right click SCCM database and select New Query.
• In the query window, paste the below SQL query to find SCCM application deployment details and click Execute.

Note: When you execute this query, you must use the Application Name listed in the General Information tab of Application Properties, instead of using the Localized application name listed in the Software Center tab of Application properties.

SELECT APP.CI_ID [App CI ID], APP.CI_UniqueID [App Unique ID], APP.DisplayName [App Name],
DT.CI_UniqueID [DT Unique ID], DT.ContentId [DT Content ID],
CIA.Assignment_UniqueID [Assignment ID], CIA.CollectionID, CIA.CollectionName,
CASE CIA.OfferTypeID WHEN 0 THEN 'Required' WHEN 2 THEN 'Available' WHEN 3 THEN 'Simulate' ELSE 'Unknown' END AS [Deployment Purpose],
CASE C.CollectionType WHEN 1 THEN 'User Collection' WHEN 2 THEN 'Device Collection' ELSE 'Unknown' END AS [Collection Type],
DT.Technology, DT.DisplayName [DT Name]
FROM fn_ListApplicationCIs(1033) APP
JOIN fn_ListDeploymentTypeCIs(1033) DT ON DT.AppModelName = APP.ModelName AND DT.IsLatest = 1
LEFT JOIN v_CIAssignmentToCI CIACI ON CIACI.CI_ID = APP.CI_ID
LEFT JOIN v_CIAssignment CIA ON CIACI.AssignmentID = CIA.AssignmentID
LEFT JOIN v_Collection C ON C.CollectionID = CIA.CollectionID
WHERE APP.IsLatest = 1 AND APP.DisplayName = 'Application Name' -- Replace Application Name

When you run the above SQL query to find SCCM application deployment details, the query results include the following:

• App CI ID – Each application has an unique CI ID assigned
• App Unique ID – Each application has an unique ID assigned
• Application Name – The name of the application packaged in SCCM
• DT Unique ID – Unique Deployment ID
• DT Content ID – Unique Content ID
• Assignment ID – Unique Assignment ID
• CollectionID – Each collection has a unique ID
• CollectionName – Name of the collection to which the application has been deployed
• Deployment Purpose – Available or Required
• Collection Type – Device Collection or User Collection
• Technology – MSI, EXE or something else
• DT Name – Deployment Name

I hope the above SQL query helps you find SCCM application deployment details and troubleshoot application deployments in SCCM. If you have any questions, let me know in the comments section.