Monday, June 20, 2011

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
Post a Comment