It never fails. You’re diligently working your way through troubleshooting a Task Sequence failure. You scroll through the task sequence report or smsts.log file, locate the error, and grab the PackageID. You open the SCCM 2007 Console, expand Software Distribution…and begin expanding and expanding node after node looking for the package. Sure, you cheated and looked at the System Status > Package Status page to get the package name, but the folder structure is broken down by facility and manufacturer and app suite and version and a dozen other criteria you never had a hand in defining.
Of course, pretty much everything in SCCM is in SQL, so with just a little work we can come up with a better way to find the path to those packages. As it turns out, Stephen Carter already came up with a SQL query to show location of package objects in admin console. If you’re lazy like me though…or if you want to provide this capability to others in your group that you’d rather not have meddling around in SQL Management Studio…a report would be even better!
For Collections, the report is pretty straightforward:
declare @parent varchar(8), @sub varchar(8), @ord int, @name varchar(255)
set @sub = @id
set @parent = (Select parentCollectionID from v_CollectToSubCollect
Where subCollectionID = @sub)
set @name = (Select Name from dbo.v_Collection
where CollectionID = @sub)
set @ord = 0
while @parent <> ‘COLLROOT’
begin
set @parent = (Select parentCollectionID from v_CollectToSubCollect
Where subCollectionID = @parent)
set @sub = (Select parentCollectionID from v_CollectToSubCollect
Where subCollectionID = @sub)
set @name = (Select Name from dbo.v_Collection
where CollectionID = @sub) + ‘ –> ‘ + @name
set @ord = @ord + 1
end
Select @name
The report for the Package location is a little more complicated…
DECLARE @Path AS varchar(2000)
DECLARE @ObjectType AS int
;
WITH folCTE (row, ContainerNodeID, Name, parentContainerNodeID,ObjectType)
AS
(
SELECT
1
,ContainerNodeID
,Name
,parentContainerNodeID
,ObjectType
FROM
dbo.Folders
WHERE
(ContainerNodeID IN (SELECT ContainerNodeID FROM dbo.FolderMembers WHERE InstanceKey = @PackageID))
AND
(ObjectType NOT IN (7,8))
UNION ALL
SELECT
cte.row + 1
,f.ContainerNodeID
,f.Name
,f.parentContainerNodeID
,f.ObjectType
FROM
dbo.Folders f
INNER JOIN
folCTE cte ON cte.parentContainerNodeID = f.ContainerNodeID
)
SELECT @Path = COALESCE(@Path + ‘ -> ‘, ”) + Name from folCTE ORDER BY row DESC
SELECT @ObjectType = ObjectType FROM dbo.Folders WHERE ContainerNodeID IN (SELECT ContainerNodeID FROM dbo.FolderMembers WHERE InstanceKey = @PackageID) AND (ObjectType NOT IN (7,8))
SELECT
@PackageID AS ‘PackageID’
,pkg.Manufacturer + ‘ ‘ + pkg.Name + ‘ ‘ + pkg.Version AS ‘Package’
,CASE
WHEN @ObjectType = 2 THEN ‘Software Distribution\Packages\’ + @Path
WHEN @ObjectType = 14 THEN ‘Operating System Deployment\Operating System Install Packages\’ + @Path
WHEN @ObjectType = 18 THEN ‘Operating System Deployment\Operating System Images\’ + @Path
WHEN @ObjectType = 19 THEN ‘Operating System Deployment\Boot Images\’ + @Path
WHEN @ObjectType = 20 THEN ‘Operating System Deployment\Task Sequences\’ + @Path
WHEN @ObjectType = 23 THEN ‘Operating System Deployment\Driver Packages\’ + @Path
WHEN @ObjectType = 25 THEN ‘Operating System Deployment\Drivers\’ + @Path
ELSE @Path
END AS ‘Path’
FROM
v_Package pkg
WHERE
PackageID = @PackageID
Note that we’re not just limiting to standard application deployments…we’re also including Driver Packages, Task Sequences, etc. Also, by default the webreport role does not have rights to read the Folders and FolderMembers tables. The simplest remedy is to simple grant the required permissions*:
GRANT SELECT
ON Folders
TO webreport_approle
GO
GRANT SELECT
ON FolderMembers
TO webreport_approle
GO
*Microsoft does not support changing permissions on SCCM tables, and there’s a possibility that any future SCCM updates may reset those permissions. You may want to consider creating your own custom SQL views instead; while adding views to the SCCM database is also not technically supported by Microsoft, it would at least give a little more visibility and compartmentalization to your changes. Use these methods at your own risk, test before you put into production, and be sure to have current backups before making any changes.
For the above reports, the only prompt required is the Collection/Package (there are numerous standard reports that provide the prompt queries allowing for the use of wildcards). If you already have the CollectionID/PackageID from your earlier troubleshooting, you can simply enter that. Otherwise just use part of the name of the Package/Collection you need to find, let SCCM do the work of tracking down the PackageID/CollectionID…
…and the report will show you where your treasure is buried!
Now you have a quick and easy way to track down Collections and Packages in your SCCM console.
