I back and … SQL report to export all sub collection tree

Hello, after some time that I haven't posted anything in this blog I decided that its time to get myself together and write something.

before I start to get dirty and technical I would to update you all about what I was up to in the last 6 mounts.
so, for those of you how don’t know me, my name is gal hutman. I currently work at NESS.IT as a senior system center consultant. I started working here about 3 mounts ago , before that I worked at Bynet data communication, also as system center consultant.

today I am going to decide how to create a SQL report that export all the sub collection in a collection tree.
for example lets assume that we have the following SCCM collection tree :

Company X
  -> Headquarters
     -> Finance
     -> IT
        -> Helpdesk
        -> System
     -> Trainings
  -> Site A
  -> Site B

-> and some on and so on….

let say that we want to extract all the collection IDs for the Headquarters collection and lets say that we have an unknown number of levels in the tree collections.

the following SQL query exports all the sub collections :

declare @CollectionTable TABLE (

CollectionID char (15),

CollectionName char (100),

ParrentCollectionID char (15),

level int);

with xxx as (

select cs.subCollectionID,col.name,cs.parrentCollectionID,0 as level

from v_CollectToSubCollect cs

join v_Collection col on col.CollectionID = cs.subCollectionID

where parrentCollectionID = ‘Enter Collection ID Here’

union all

     select cs.subCollectionID,col.Name,cs.parrentCollectionID,level +1

     from v_CollectToSubCollect cs

     join v_Collection col on col.CollectionID = cs.subCollectionID

inner join xxx as x on x.subCollectionID = cs.parrentCollection ID)

insert into @CollectionTable  (CollecationID ,CollectionName ,ParrentCollectionID ,level)

select subCollectionID,Name,parrentCollectionID,level from xxx;

you can also extent this query to select all agents in the sub collection :

all you have to do is to add this query :

select resourceID from v_FullCollectionMemebership where CollectionID in ( select * from @CollectionTable ) 

Gal.

ps. excuse me there are any typos in the code, I wrote it from memory after implementing it in a client site.