In response to a forum post, I've decided to write a quick article on getting folders from CM (and showing subfolder structure) and showing what kinds of objects those folders hold (packages, configuration items, etc.)
BACKGROUND
Folder information in CM (and I believe SMS too) is found in the view vSMS_Folders. That view shows the name of the folder, but also has a ContainerNodeID (an integer that uniquely defines that folder in this view), a ParentContainerNodeID (which identifes the ContainerNodeID of the parent for said folder...0 means it's at the top, no parent.), and it also contains an ObjectType field. Now that ObjectType is an integer that doesn't mean much to me when I look at it. However, I wrote an article HERE that shows you how to find the definition of the ObjectType field. I will make use of some of the code of that article to help us finish this one.
Armed with all that information, we should be able to write some M@d SQL to show the folders, their hierarchy (from the parent/child relationship) and where they are in the console (by inferring from the ObjectType)
THE SQL 2000 METHOD
C'mon...upgrade to SQL 2008 already. Then follow those instructions.
SQL 2005/2008 METHOD
The only difference between the SQL 2005 and the SQL 2008 method is the way in which you insert values into the @ObjectTypes temp variable. 2008 can do multi-line inserts all in one statement. SQL 2005 has to do them one at a time. I'm showing the 2008 version in this article, but by looking at that other article I mentioned, you can see the 2005 inserts.
1) First insert the ObjectType and TypeDescription values into a temp table variable called @ObjectTypes. This will be used for a lookup of object types later
DECLARE @ObjectTypes TABLE (
ObjectType INT PRIMARY KEY,
TypeDescription VARCHAR(46)
);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription)
VALUES
(2,'Package'),
(3,'Advertisement'),
(7,'Query'),
(8,'Report'),
(9,'MeteredProductRule'),
(11,'ConfigurationItem'),
(14,'OperatingSystemInstallPackage'),
(17,'StateMigration'),
(18,'ImagePackage'),
(19,'BootImagePackage'),
(20,'TaskSequencePackage'),
(21,'DeviceSettingPackage'),
(23,'DriverPackage'),
(25,'Driver'),
(1011,'SoftwareUpdate'),
(2011,'ConfigurationItem (Configuration baseline)');
2) Now, find all of the folders that are the top folders in the hierarchy
SELECT
CAST('\'+f.Name AS VARCHAR(512)) AS Folder,
f.ContainerNodeID AS ID,
f.ParentContainerNodeID AS ParentID
FROM
dbo.vSMS_Folders f
WHERE f.ParentContainerNodeID = 0
Note: Because we're not just going to display the folder name, but rather we're going to display the full path to the subfolders as well, we're adding a leading backslash to it to indicate it's at the root. The \ will act as a separator between levels.
3) Join this to the @ObjectTypes table variable above to lookup the description of the ObjectType (for a more human readable format)
SELECT
CAST('\'+f.Name AS VARCHAR(512)) AS Folder,
f.ContainerNodeID AS ID,
f.ParentContainerNodeID AS ParentID,
ot.ObjectType, --
ot.TypeDescription --
FROM
dbo.vSMS_Folders f
JOIN @ObjectTypes ot --
ON f.ObjectType = ot.ObjectType --
WHERE f.ParentContainerNodeID = 0
4) Now let's use common table expressions (CTE) to recursively join those top level folders to the child folders.
WITH fldr AS (
--top level folders (anchor)
SELECT
CAST('\'+f.Name AS VARCHAR(512)) AS Folder,
f.ContainerNodeID AS ID,
f.ParentContainerNodeID AS ParentID,
ot.ObjectType,
ot.TypeDescription
FROM
dbo.vSMS_Folders f
JOIN @ObjectTypes ot
ON f.ObjectType = ot.ObjectType
WHERE f.ParentContainerNodeID = 0
UNION ALL
--child folders (recursive)
SELECT
CAST(Parent.Folder+'\'+Child.Name AS VARCHAR(512)) AS Folder,
Child.ContainerNodeID AS ID,
Child.ParentContainerNodeID AS ParentID,
ot.ObjectType,
ot.TypeDescription
FROM dbo.vSMS_Folders Child
JOIN @ObjectTypes ot
ON Child.ObjectType = ot.ObjectType
JOIN fldr AS Parent
ON Child.ParentContainerNodeID = Parent.ID
AND Child.ObjectType = Parent.ObjectType
)
--AND SELECT FROM THE CTE
SELECT
TypeDescription,
Folder
FROM
fldr
ORDER BY
TypeDescription,
Folder
So now let's put it all together:
DECLARE @ObjectTypes TABLE (
ObjectType INT PRIMARY KEY,
TypeDescription VARCHAR(46)
);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription)
VALUES
(2,'Package'),
(3,'Advertisement'),
(7,'Query'),
(8,'Report'),
(9,'MeteredProductRule'),
(11,'ConfigurationItem'),
(14,'OperatingSystemInstallPackage'),
(17,'StateMigration'),
(18,'ImagePackage'),
(19,'BootImagePackage'),
(20,'TaskSequencePackage'),
(21,'DeviceSettingPackage'),
(23,'DriverPackage'),
(25,'Driver'),
(1011,'SoftwareUpdate'),
(2011,'ConfigurationItem (Configuration baseline)')
WITH fldr AS (
--top level folders (anchor)
SELECT
CAST('\'+f.Name AS VARCHAR(512)) AS Folder,
f.ContainerNodeID AS ID,
f.ParentContainerNodeID AS ParentID,
ot.ObjectType,
ot.TypeDescription
FROM
dbo.vSMS_Folders f
JOIN @ObjectTypes ot
ON f.ObjectType = ot.ObjectType
WHERE f.ParentContainerNodeID = 0
UNION ALL
--child folders (recursive)
SELECT
CAST(Parent.Folder+'\'+Child.Name AS VARCHAR(512)) AS Folder,
Child.ContainerNodeID AS ID,
Child.ParentContainerNodeID AS ParentID,
ot.ObjectType,
ot.TypeDescription
FROM dbo.vSMS_Folders Child
JOIN @ObjectTypes ot
ON Child.ObjectType = ot.ObjectType
JOIN fldr AS Parent
ON Child.ParentContainerNodeID = Parent.ID
AND Child.ObjectType = Parent.ObjectType
)
--AND SELECT FROM THE CTE
SELECT
TypeDescription,
Folder
FROM
fldr
ORDER BY
TypeDescription,
Folder
SUMMARY
So, we've learned that
1) The folder information is in the vSMS_Folders view
2) There is an article HERE that explains the ObjectTypes
3) SQL 2000 is old and sucks and chicks will dig you if you upgrade
4) Using common table expressions, you can recursively build the folder hierarchy to see folders and subfolder relationships.
So...are we liking this stuff?
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
