Question How to report collection membership of computers within a chosen (master) collection?
Answer In order to achieve this you need to work directly with the CollectionMembership table in order to list the computers in the master collection. This is done with the following SQL query:
select resourceguid from collectionmembership where collectionguid = '%MasterCollection%'
This can be then used as a filter in a more complex query that returns collection membership (computer name, collection name):
select cmpt.name, clln.name from collectionmembership cm join item cmpt on cm.ResourceGuid = cmpt.Guid join item clln on cm.CollectionGuid = clln.guid where collectionguid != '%MasterCollection%' and resourceguid in ( select resourceguid from collectionmembership where collectionguid = '%MasterCollection%' ) order by cmpt.name, clln.name
Note! As you noticed above the SQL Query uses a Notification Server parameter ('%MasterCollection%). As such it will not run directly in SQL Management studio or SQL Query Analyser.
A couple of report xml files are attached to this AKB [See on the right hand pane or at the end of the article for download link] to show how the %MasterCollection% could be retrieved by a Collection Picker item or by a drop down list. Below are excerts of both xml files related to the NS parameter.
Note! In some cases selecting a collection from the picker can be cumbersome as the user could have access to all built-in collections and hundreds of custom collections. This is why we added a sample to select collections in a drop down from a SQL query.
Drop down selector populated by an SQL query:
<alwaysPromptParameters>True</alwaysPromptParameters> <queries> <parameter type="dropdown" listType="queryresults" prompt="True" name="MasterCollection" substituted="true"> <description><![CDATA[use %MasterCollection%]]></description> <valueType><![CDATA[NVarChar]]></valueType> <prompt><![CDATA[Select a master collection:]]></prompt> <query><![CDATA[select name, guid from vCollection where name like 'g%' order by name]]></query> </parameter>
Note! On the above drop down query we filter the collections listed in the drop down by name. In this case we only return collection starting with the letter g as the customer collection naming convention dictated that custom collection should start with the letter g.