How to report collection membership of computers within a chosen (master) collection?

book

Article ID: 181856

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

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.

Notification Server Collection Picker (Built-in)

  <alwaysPromptParameters>True</alwaysPromptParameters>
  <queries>
    <parameter type="custom" assemblyName="Altiris.NS.StandardItems, Version=6.0.6074.60, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f" typeName="Altiris.NS.StandardItems.Query.ItemPickerParameter" filterClass="a725fb57-09e1-4e9f-bb13-b4600094cf61" excludeDescendents="False" autoUpdateIfCollection="True" prompt="True" name="MasterCollection" substituted="true">
      <description><![CDATA[use %MasterCollection%]]></description>
      <prompt><![CDATA[Master Collection:]]></prompt>
    </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.

Report xml download:

Computer collection membership report - filtered by a master collection [Collection picker].xml
Computer collection membership report - filtered by a master collection [Drop down].xml

Attachments

ComputerCollectionMembershipReport_filteredbycollectionDropdown.xml get_app
Computercollectionmembershipreport_filteredbycollectionCollectionpicker.xml get_app