Loenhoet

Thanks, but I want to do a different thing, I'll explain further. I got AD-computers. They all use the same task sequence for installing Windows 7. But there are many types of computers in the organisation, some need only a Citrix client, while others need more software, like Office. And then there are notebooks, that also need a client to log in remotely. For this reason, we created three AD-groups, called SCCM_Citrix, SCCM_MultimediaPC and SCCM_Notebook respectively. In principle, objects can only belong to one group, but can be upgraded to a different level (the new software packages are pushed then). In the software, there is however no limitation that an object can only be a member of one group. To check my colleagues for errors, I wanted to create a collection that shows me objects that are member of multiple groups. In the beginning, there were only three groups, so I made a query for objects that were in SCCM_Citrix and SCCM_MultimediaPC, a different query for SCCM_Citrix and SCCM_Notebook and a query for SCCM_MultimediaPC and SCCM_Notebook. Please note that I'm talking about AD-groups here, but they all correspond to a single collection. But since the number of AD-Groups are growing, we wanted to use a different approach. The query should look something like: select * from SMS_R_System where count(number of AD-collections the object is in) > 1 Hope this makes everything more clear. It is just for monitoring my colleagues (they just work with the AD-groups and don't know much about SCCM).

Thanks for the reply, I was indeed using the wrong ID's. Now this is working, but I have no clue how to add the counter to the query. How can I define a extra column in the view? So I can test the function COUNT(*).

Hi, I really appreciate your help. But when I try to copy this query to the collection, it gives me an error: The query statement that you entered is not valid. Please enter a valid query statement. But if I understand right, we now should have a collection that contains the members of multiple collections. I was talking about AD-groups, but this is also good. But I want a collection that contains the objects that are in multiple (two or more) collections from a given list. Therefore I was thinking about a COUNT(*) function.

Sorry, I was a bit unclear in my previous post. I want to get all objects in one collection that are part of multiple (two or more) collections (Software1 and Software2, Software1 and Software3, Software2 and Software3, Software1 and Software2 and Software3). We did these combinations by hand, but since these groups are growing, so we want an easier way of doing this.

We got some AD-groups for specific type of systems (not everyone needs all software): SCCM_Software1, SCCM_Software2 and SCCM_Software3 for example. Now we got some collections that queries if the object is member of one of these groups. The Query looks like (for the Software1 group): select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = "DOMAIN\\SCCM_Software1" and SMS_R_System.Client = 1 Now we got a collection to check if there are objects that do not belong to any of these groups: select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Name not in (select Name from sms_r_system where systemGroupName like "DOMAIN\\SCCM_Software%") and SMS_R_System.Client = 1 Now we want to create a collection that checks if an object is a member of multiple Groups. We tried something with COUNT(*), but this results in an error. Any clues how to solve this problem?