Report to List Packages with Containing Folder

July 18th, 2014 | Posted by John Marcum in ConfigMgr | John Marcum

In complex ConfigMgr 2007 environments it was often hard to locate packages and collection that had been buried as child objects. John Nelson wrote some pretty handy SQL queries that would parse the exact path to a given object. ConfigMgr 2012 made an attempt to remedy this issue in several ways. First of all we no longer nest object for organizational purposes, instead we use folder. Secondly each node is searchable. This does tend to make life easier however from time to time I still need to know what folder an object resides in and the built-in search doesn’t give me that ability.

As an example let’s assume I have a collection to which I deploy Adobe Flash 13.x. When Adobe 14.x releases I want to create a new collection called Adobe Flash 14.x and I want it in the same folder as the aforementioned Adobe Flash 13.x. Sure, if I want to make an edit to the Flash 13.x collection I can search for it, locate it and make changes without needing to know where in the Assets and Compliance node it truly lives but that’s not my goal here. I need to know where it lives. To find this I have a created a few SQL queries that can be used in SQL Management Studio or added as SSRS reports.

Below you will find 3 queries. One will list all of your collections with their containing folders, the next does the same for applications and lastly one for traditional packages. I wasn’t able to figure out how to get the full path to the folder if the folder is a sub-folder of other folders but that way I have my folders organized just knowing the containing folder is normally good enough.

 

–Find my collection

select
col.Name as ‘Collection Name’,
F.Name as ‘Folder Name’,
COL.CollectionID as ‘Collection ID’
from
v_collection col
LEFT JOIN vFolderMembers FM on fm.InstanceKey = col.CollectionID
LEFT Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where FM.ObjectTypeName = ‘SMS_Collection_Device’

order by col.Name

–Find my application

Select Distinct

Apps.DisplayName as ‘Application Name’,
C.ContentSource as ‘Application Source Path’,
F.Name as ‘FolderName’

From fn_ListApplicationCIs(1033) Apps
Left Join v_CIContentPackage CP on CP.CI_ID = Apps.CI_ID
Left Join v_Content C on C.PkgID = CP.PkgID
Left Join vFolderMembers FM on FM.InstanceKey =Apps.ModelName
Left Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where ContentSource Is not null
Order By ContentSource

 

— Find my package

select
Pkg.Name as ‘Package Name’,
F.Name as ‘Folder Name’,
PKGSourcePAth as ‘Package Source Path’
from
v_Package pkg
LEFT JOIN vFolderMembers FM on fm.InstanceKey = pkg.PackageID
LEFT Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where FM.ObjectType = 2

You can follow any responses to this entry through the RSS 2.0 Responses are currently closed, but you can trackback.