SQL Query Functions for Configuration Manager 2007 Folder Trees

When you look in the System Center Configuration Manager 2007 admin console, you should be familiar with "folder" structures with respect
to Packages and Advertisements.  However, when you want to determine the folder tree/path for a given package or advertisement programmatically, it takes a little patience and scratching.
 
There are two key SQL tables in the site database: Folders, and FolderMembers.  To start with, you can query the "FolderMembers" table to get the "ContainerNodeID" identifier value for a given Package or Advertisement by passing in the "InstanceKey" value.  This is actually either the AdvertisementID or PackageID value, which should begin with the 3-char site prefix (i.e. "ABC1234B", where "ABC" is the site code).  Once you get the ContainerNodeID, you can query for the Folder "Name" and “ParentContainerNodeID” values and begin walking up the logical folder hierarchy.  The functions below will help get you there.  The Function "CMFolderID" returns the ContainerNodeID for a given Package or Advertisement by name.  The result of that function call is then passed into the Function "CMFolderTree" to return a concatenated folder path using a fairly basic recursive SQL routine.
 
The only additional ingredients you will need to use these two functions are (a) the standard ADO enumeration constants definitions, and (b) a data source name connection (aka “dsn”) of some kind.  These were coded for use in ASP, but you can easily convert these to VBScript, KiXtart or PowerShell if you prefer. – Cheers!

Examples:

Package for application "My Stupid Application 2011" has an ID of "ABC1234X"
The console shows it under:

---\Packages
    +---\Engineering
          +---\Contoso 
                +---\Stupid Applications

nodeID = CMFolderID ("ABC1234X")
folderTree = CMFolderTree (nodeID )

returns: "Packages\Engineering\Contoso\Stupid Applications"

'----------------------------------------------------------------
' function: returns Folder NodeID from object name (package, advertisement, etc.)
'----------------------------------------------------------------

Function CMFolderID (instanceID)
  Dim conn, cmd, rs, query, retval : retval = ""
               
  query = "SELECT DISTINCT dbo.FolderMembers.InstanceKey, dbo.FolderMembers.ContainerNodeID, " & _
    "dbo.Folders.Name FROM dbo.FolderMembers INNER JOIN " & _
    "dbo.Folders ON dbo.FolderMembers.ContainerNodeID = dbo.Folders.ContainerNodeID " & _
    "WHERE (dbo.FolderMembers.InstanceKey = '" & instanceID & "')"
               
  Set conn = Server.CreateObject("ADODB.Connection")
  Set cmd  = Server.CreateObject("ADODB.Command")
  Set rs   = Server.CreateObject("ADODB.Recordset")
 
  conn.Open dsn
 
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockReadOnly
 
  Set cmd.ActiveConnection = conn
 
  cmd.CommandType = adCmdText
  cmd.CommandText = query
  rs.Open cmd
 
  If Not(rs.BOF And rs.EOF) Then
    retval = rs.Fields("ContainerNodeID").value
  End If
  rs.Close
  conn.Close
  Set rs = Nothing
  Set cmd = Nothing
  Set conn = Nothing
  CMFolderID = retval
End Function


'----------------------------------------------------------------
' function: returns concatenated chained folder path string for object
'----------------------------------------------------------------

Function CMFolderTree(NodeID)
  Dim conn, cmd, rs, query, retval : retval = ""
               
  query = "WITH ParentChildRels (ParentContainerNodeID, ContainerNodeID, " & _
    "Name, HierarchyLevel) AS " & _
    "(SELECT ParentContainerNodeID, ContainerNodeID, Name, 1 as HierarchyLevel " & _
    "   FROM Folders " & _
    "   WHERE ContainerNodeID='" & NodeID & "' " & _
    "   UNION ALL " & _
    "   SELECT " & _
    "      r.ParentContainerNodeID, r.ContainerNodeID, r.Name, " & _
    "      pr.HierarchyLevel + 1 AS HierarchyLevel " & _
    "   FROM Folders r " & _
    "      INNER JOIN ParentChildRels pr ON " & _
    "         r.ContainerNodeID = pr.ParentContainerNodeID " & _
    ") " & _
    "SELECT * FROM ParentChildRels " & _
    "ORDER BY HierarchyLevel DESC, ParentContainerNodeID, ContainerNodeID, Name"
 
  Set conn = Server.CreateObject("ADODB.Connection")
  Set cmd  = Server.CreateObject("ADODB.Command")
  Set rs   = Server.CreateObject("ADODB.Recordset")
 
  conn.Open dsn
 
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockReadOnly
 
  Set cmd.ActiveConnection = conn
 
  cmd.CommandType = adCmdText
  cmd.CommandText = query
  rs.Open cmd
 
  If Not(rs.BOF And rs.EOF) Then
    cols = rs.Fields.Count
    rows = rs.RecordCount
    Do Until rs.EOF
      If retval = "" Then
        retval = rs.Fields("Name").value
      Else
        retval = retval & "\" & rs.Fields("Name").value
      End If
      rs.MoveNext
    Loop
  End If
  rs.Close
  conn.Close
  Set rs = Nothing
  Set cmd = Nothing
  Set conn = Nothing
  CMFolderTree = retval
End Function