COUNT(*) in Query

[Loenhoet]

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?

[Eswar Koneti]

So You want to query the list of computers which are part of multiple AD security groups ? isnt it ?

if so ,you can add the all the security group to the first collection directly like below :

 

 

 select *  from  SMS_R_System where (SystemGroupName = "SCHENKER\\APP_Adobe_Reader_9.4_Sve" or SystemGroupName = "SCHENKER\\APP_Adobe_ShockwavePlayer_11.5.8.612")
and Client = 1

you can add the all the security groups to the above query with OR condition

OR Else

You can use list of Values criteria like below:

select *  from  SMS_R_System where SystemGroupName in ( "SCHENKER\\APP_Adobe_Reader_9.4_Sve", "SCHENKER\\APP_Adobe_ShockwavePlayer_11.5.8.612_Int" )

 

Add all the security Groups to the above collection.

[Loenhoet]

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.

[Eswar Koneti]

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.

 

Ahh okay. Try this

 

select *  from  SMS_R_System where ResourceId in (select ResourceID from SMS_CM_RES_COLL_COLLID) or ResourceId in (select ResourceID from SMS_CM_RES_COLL_COLLID) or ResourceId in (select ResourceID from SMS_CM_RES_COLL_COLLID)

 

If you have more collection to add ,just add or ResourceId in (select ResourceID from SMS_CM_RES_COLL_COLLID) at the end of Query.

 

Where COLLID is the collection ID which you need to replace.

[Loenhoet]

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.

[Eswar Koneti]

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.

 

 

Have you given the collection IDs in place of COLLID.

Yes,the collection query which i gave above ,will list all the computers which are present in multiple collections which we have specify in COLLID.The collection basically looks for ResourceID in other collections.

About Count(*) function--->If you right click on collection,it will show the count and same can be the displayed in reports.

[Loenhoet]

Have you given the collection IDs in place of COLLID.

Yes,the collection query which i gave above ,will list all the computers which are present in multiple collections which we have specify in COLLID.The collection basically looks for ResourceID in other collections.

About Count(*) function--->If you right click on collection,it will show the count and same can be the displayed in reports.

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(*).

[Eswar Koneti]

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(*).

 

You dont require to add the counter function to the collection query since if you right click on the collection,you have an option called show count .It gives you different counts (total resource,non obsolte clients etc)

[Loenhoet]

You dont require to add the counter function to the collection query since if you right click on the collection,you have an option called show count .It gives you different counts (total resource,non obsolte clients etc)

 

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).