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.
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.
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.
Package Advertisement and Collection
select v_Package.Name 'Package Name',
v_Collection.Name 'Collection Name',
inner join v_Package
inner join v_Collection
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
- Result of Application and Collection Query
- Creating custom report models for Configuration Manager in SQL Server Reporting Services