Merge all sub-collection to another new single collection

[sudheeppanand]

Hi,

 

I have a required to merge all sub-collection to another new single collection as in below scenario.

Main Root collections are Root A and Root B which doesnt have any members, but sub-collection. Now in each subcollection, there are multiple members, say 100-200 computer objects.

 

Root A

A1

A2

A3

 

Root B

B1

B2

B3

 

Now I required to merge all these subcollections of Root A and Root B sub-collections to a new collection called Root_All.

And in future, whenever we will be adding more subcollection in Root A and Root B, the machines in those new sub-collection also should visible under the Root_All collection.

 

We have access only to use the SQL SCCM query in the collection. Please help on this.

 

Regards

Sudheep

[Peter van der Woude]

What are the sub collections based on?

[sudheeppanand]

Hi Peter,

 

Thanks for response. The scenario is as below. We are working in a company organization, where training provides to end users in class rooms.

 

 

In Our SCCM environment, we have organized the collections as below for example:

 

 

A manually created root collection MainBuilding_Collection, under this 2 subcollection named Building1_Collection and Building2_Collection under each collections, class room named subcollection (like B1Class1, B1Class2) where its included computers in each class rooms.

 

In each class room B1Class1, B1Class2, etc. , the machine is manually imported after taking the class room machine inventory.

 

As there are more than 200 class rooms, this inventory collection is happening day by day, say each day we will collect the data from each class room. We will be rolling out an application (will install the application as service) in all those machines. Hence each day, we will be advertising the application for newly created collection.

 

Here the issue is, in future due to OS issues, IT enggs team will be formatting the machine and hence this application need to re-install. So we are planning to have a new collection (named as App_NotInstaled) with SQL query from MainBuilding_Collection where machines doesn’t detect the installed service. In this App_NotInstaled collection, we will advertise the package on every day 10.00AM basis.

 

Hence in case if the machines are getting formatted, on next day after the machine reported to SCCM server, it will include in the App_NotInstalled collection, hence @ 10.00AM, when advertisement happens it will install the application in those machines.

 

As MainBuilding_Collection is root collection with subcollection, we are not able to do SQL query with App_NotInstalled collection (only the machines not installed the service, will be included in this collection)

 

We are using MDT for deployment, currently we don’t have the access to do any modification to image to include the service, but need to depend on SCCM rollout as mentioned above.

 

So I am pointing that, the machines in each class room (which considered as subcollections), where the specific service is not installed, shoud come in App_NotInstaled collection

 

hmmm, I think, I explained too much as a story. Hope you could understand the situation and help me.

 

Regards

Sudheep

[Peter van der Woude]

It indeed became a long story... (I haven't tested this one) probably something like this should already give a collection with the members of the RootA and RootB collections:

 
SELECT * FROM SMS_R_System 
WHERE SMS_R_System.ResourceId in (SELECT ResourceId FROM SMS_CM_RES_COLL_%ROOTA_ID%) 
OR ResourceId in (SELECT ResourceId FROM SMS_CM_RES_COLL_%ROOTB_ID%)