T-SQL Query Collection Path SCCM Current Branch
Struggling to find a T-SQL query display the Location Path of a SCCM
Collection?
There are PowerShell scripts to
perform the same results, however I wrote the below query in SQL so that it can
be published to SSRS.
/* .NOTES
===========================================================================
Created on: 4th January 2017
Created by: Matthew Swanston
Contact
http://thebeardysccmguy.blogspot.com
Filename:
CollectionInformation
===========================================================================
.DESCRIPTION
Provides basic Collection Information including Collection Location Path.
CollectionName LocationPath
CollectionID
LimitToCollectionName AssignedCount
*/
with cte_CollectionInfo as (select CollectionName, SiteID, LimitToCollectionName
,CONCAT ('\' , F4.Name , '\' , F3.Name , '\' , f2.name , '\' , f.name) as LPath
,C.CollectionID
,CMC.AssignedCount
from Collections C
join CollectionMemberCounts
CMC on CMC.CollectionID = C.CollectionID
join FolderMembers FM on FM.InstanceKey = C.SiteID
join Folders F on F.ContainerNodeID = FM.ContainerNodeID
left join Folders
F2 on F2.ContainerNodeID = F.ParentContainerNodeID
left join Folders
F3 on F3.ContainerNodeID = F2.ParentContainerNodeID
left join Folders
F4 on F4.ContainerNodeID = F3.ParentContainerNodeID
left join Folders
F5 on F5.ContainerNodeID = F4.ParentContainerNodeID
where f.ObjectType = 5000
--and CollectionName = 'Test 040117'
)
select CollectionName
,REPLACE(REPLACE(LPath, '\\\', '\') , '\\', '\') AS LocationPath
,siteid as CollectionID
,LimitToCollectionName
,AssignedCount
from cte_CollectionInfo
order by CollectionName
No comments:
Post a Comment