• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1193
  • Last Modified:

SCCM 2012 query

Can someone assist me in writing a query or report in SCCM 2012 that will show how many Task Sequence a certain software/driver package is a member of?
I.e driver package ID A10030A is a member of task sequence A0100311, A0100259 and so forth.
0
Risrollout
Asked:
Risrollout
1 Solution
 
Leon TaljaardCommented:
Hi there

Here is a query that I use

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

Open in new window


You can just create a report from it with the "PackageID" as a parameter and you will be able to get the path for the specific package.

Hope that helps
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now