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

0
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 with 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.

  • 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.
SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr
Application Package and Collection Information | ConfigMgr | SQL Query – SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr

Package Advertisement and Collection

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

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 result as below.

  • Result of Package and Collection Query
SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr
SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr
  • Result of Application and Collection Query
SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr
SCCM SQL Query to Find Collections Used for App Deployment | ConfigMgr

References

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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