Solved

SCCM 2012 query

Posted on 2014-02-20
1
1,078 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

861 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

24 Experts available now in Live!

Get 1:1 Help Now