Resources For IT Professionals
Very often SCCM Admins wants to find the location of path of a package or collection that they need to work on or to modify etc, but doesn't find an easy way to find the full path of these. Either they need to know the path and remember it or rely on some sort of SQL queries which helps them to find the full path, but then these queries are two separate one, meant for each collection and package. Post observing couple of requests on similar singe query experience, My tiny efforts to club these queries using if, goto etc functions of T-SQL, so to offer single query experience to get full path info
declare
@Selection varchar
(30), @PKG_R_COL_Name
varchar (30)
set @Selection
=
'Collection'
-- Make your selection of package or collection
set @PKG_R_COL_Name
=
'Vasu_TestCollection'
-- Type name of collection or package based on above selection
if @Selection='Package'
goto PackageFn
else
goto collectionFn
PackageFn:
WITH folderHierarchy
(ContainerNodeID,Name,ObjectType,ParentContainerNodeID,[Path])
AS(SELECT ContainerNodeID,
Name, ObjectType, ParentContainerNodeID,
CAST('/'+[Name]+'/'
AS
VARCHAR(MAX))
AS [Path]
FROM dbo.Folders
WHERE ParentContainerNodeID
= 0
UNION
ALL
SELECT child.ContainerNodeID,child.Name,child.ObjectType,child.ParentContainerNodeID,parent.[Path]+child.[Name]+'/'
AS [Path]
FROM dbo.,child.ParentContainerNodeID,parent.[Path]+child.Folders
AS child
INNER
JOIN folderHierarchy
AS parent
ON parent.ContainerNodeID
= child.ParentContainerNodeID
)
SELECT
distinct fldr.[Path],mbr.InstanceKey
AS PackageID,pkg.Name
FROM folderHierarchy
AS fldr
INNER
JOIN dbo.FolderMembers
AS mbr
ON fldr.ContainerNodeID
= mbr.ContainerNodeID
INNER
JOIN dbo.v_package
AS pkg
ON pkg.PackageID
= mbr.InstanceKey
WHERE pkg.Name=@PKG_R_COL_Name
-- and fldr.ObjectType = 2
goto quit
CollectionFn:
declare @parent varchar(8),
@sub varchar(8), @ord
int, @name
varchar(255)
set @sub
=
(Select CollectionID
from dbo.v_Collection
where Name
= @PKG_R_COL_Name)
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
Quit:
_________________________________________________________________________________________________________________________________________________________
You may then further modify this to your needs
Thought not sure on names or urls, but sincere credits to those who written the individual queries and shared with Microsoft admins around.
--- Thanks, Vasu Miriyala
