?
Solved

SCCM 2012 query

Posted on 2014-02-20
1
Medium Priority
?
1,151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
Leon Taljaard earned 1500 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 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