SCCM SQL Query To Find Collections Used For App Deployment

Let’s find out the ConfigMgr Application, Packages, and collections used to deploy using the SQL query. We have many other SQL queries and other custom reports we shared with the HTMD Forum community. More details about SCCM SQL Query to Find Collections Used for App Deployment in the following sections.

In this post, we will see two different SQL queries that will provide information on Applications and Packages in Your environment with their deployed collection and Content source path.

Introduction

The first query in this post will give you Information related to Packages and their relation to Collection and Advertisement. The second query will give you information on the applications and to which collection it is deployed.

SQL Query

This SQL Query will help you in using ‘When’ and ‘then‘ syntax. Follow the steps as explained below to get the SCCM package or application collection details.

Open the SQL Management Studio.
Connect your Database Engine.
Right Click on your database CM_XXX and click on ‘New Query’
Copy the following SQL query to find the report of application deployment with collection details.
Click on the Execute button.

Application Package and Collection Information | ConfigMgr | SQL Query – SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr

Package Advertisement and Collection

Let’s check the package advertisement and collection details from the below SQL query. You can use SQL Server Management Studio to get the reports.

select v_Package.Name 'Package Name',
v_Advertisement.PackageID,
v_advertisement.AdvertisementName,
v_Advertisement.AdvertisementID,
v_Collection.Name 'Collection Name',
v_Advertisement.CollectionID,
v_Package.PkgSourcePath
from v_Advertisement
inner join v_Package
on v_Package.PackageID=v_Advertisement.PackageID
inner join v_Collection
on v_Advertisement.CollectionID=v_Collection.CollectionID

Application and its Collection

Here is the query to get the reports of applications and its collection.

SELECT DISTINCT app.DisplayName AS 'Application Name',
aa.CollectionName AS 'Deployed to Collection',
aa.CollectionID,v_ContentInfo.ContentSource
FROM dbo.fn_ListDeploymentTypeCIs(1033) AS dt
inner join dbo.fn_ListLatestApplicationCIs(1033) AS app ON dt.AppModelName = app.ModelName
left outer join v_AppDeploymentSummary ads on app.CI_ID = ads.CI_ID
left outer join v_ApplicationAssignment aa on ads.AssignmentID = aa.AssignmentID
left outer join v_ContentInfo on DT.CONTENTID = V_CONTENTINFO.CONTENT_UNIQUEID
where aa.CollectionName not like '%NULL%'
Order by [Application Name];

Result of SQL Query

The above two different SQL queries will give you the result below.

Result of Package and Collection Query

Result of Application and Collection Query

SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr

Introduction

SQL Query

Package Advertisement and Collection

Application and its Collection

Result of SQL Query

References

Leave a Comment Cancel reply