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 to 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. 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.
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
- Creating custom report models for Configuration Manager in SQL Server Reporting Services