Solved

SCCM 2012 query

Posted on 2014-02-20
1
1,086 Views
Last Modified: 2014-03-07
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
Comment
Question by:Risrollout
1 Comment
 
LVL 8

Accepted Solution

by:
Leon Taljaard earned 500 total points
ID: 39876042
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now