Share This Post

Where IS That Darned Package (or Collection)??

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…

image

…and the report will show you where your treasure is buried!

image

Now you have a quick and easy way to track down Collections and Packages in your SCCM console.

 

 

Share This Post

2 Comments

  1. Yes, this info would be really helpfull. But what sort fo report are you talking about SQL or SCCM and how do you use the text you have provided to actually make the report?

  2. This is for a standard SCCM 2007 report, so you will need to go through the SCCM Console to create the report.

    1. Open the Configuration Manager console.

    2. Navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reports.

    3. Right-click the Reports node, point to New, click Report, and in the New Report Wizard type Collections for a specific computer in the Name text box.

    4. Select Software Distribution – Collections from the Category drop-down list.

    5. Click Edit SQL Statement to open the Report SQL Statement dialog box. A default SQL query statement populates the SQL statement text box.

    6. Paste the SQL statement from the SQL pane in Query Designer into the SQL statement text box, replacing the default SQL statement.

    You’ll need to specify queries as well for the prompts (Collection or Package). You should be able to replicate that from one of the other reports that utilize those prompts.

Leave a Reply