Client Management: Populate Dynamic Device Groups from Queries

Product

BMC Client Management

Component

Client Management

Applies to

Any version of BMC Client management

Problem

I need to populate a device group with members of other device groups.

Cause

Solution

1- Create a query:
To use them, create regular queries or free queries of the type "Devices" in the node "Queries", e.g:

User-added image

A- Regular query (Criteria based query)
The checkbox "Free Query" must be unchecked (see above). The following example lists all workstations that are members of the dynamic device group "_By Geo.Support-1-BCM.local":

To add criteria

Right click ->add criteria --> the add the condition -> Type =Workstation
Similarly add criteria --> "Direct or Indirect group member" = "_By Geo.Support-1-BCM.local"

Note: The device group _By Geo.Support-1-BCM.local is synchronized from the Active Directory.

B- Free queries
To build a free query, create a new query of the type "Devices" but this time check the checkbox "Free Query" (see first screenshot). The following example lists all devices that are NOT members of the dynamic device group "_By Geo.Support-1-BCM.local":

User-added image

(1) Copy the following query to the tab "SQL":

Select DeviceName from Devices WHERE DeviceID not in (SELECT MemberID FROM GroupMembers WHERE GroupID in (SELECT GroupID FROM Groups WHERE GroupName = '_NAME_OF_THE_MAIN_GRP_'));"

(2) Check the query
(3) Save the query
(4) Set the query to "Active"

2- Create the dynamic device group

A- Right click on the query
Then select "Create Device Group":

User-added image

B- Set it from an existing device group:
- Create or Select the device group. If it's an existing device group, it must be empty
- Right click on it and select "Assign Query..."

- Select the query that was created in the first step of this KA:

3- Other useful free queries:
This is a compilation of queries that are often requested by customers:
- All devices that are member of a device group and of all of the subgroups it may contain:

SELECT DISTINCT DeviceName FROM Devices, Groups, GroupMembers WHERE MemberID=DeviceID AND groups.GroupID=Groupmembers.GroupID AND ((Groups.GroupID IN (SELECT ChildGroupID FROM GroupHierarchies WHERE ParentGroupID=(SELECT GroupID FROM Groups WHERE GroupName = '_NAME_OF_THE_MAIN_GRP_ ' AND groups.grouptypeid=101 )))OR GroupName = '_NAME_OF_THE_MAIN_GRP_ ' AND groups.grouptypeid=101 )

- All dektops that are member of a device group and of all of the subgroups it may contain:

SELECT DISTINCT DeviceName FROM Devices, Groups, GroupMembers WHERE MemberID=DeviceID AND groups.GroupID=Groupmembers.GroupID AND ((Groups.GroupID IN (SELECT ChildGroupID FROM GroupHierarchies WHERE ParentGroupID=(SELECT GroupID FROM Groups WHERE GroupName = '_NAME_OF_THE_MAIN_GRP_ ' AND groups.grouptypeid=101 )))OR GroupName = '_NAME_OF_THE_MAIN_GRP_ ' AND groups.grouptypeid=101 ) AND (Devices.DeviceType =N'general purpose')

- All the device groups a device "_NAME_OF_DEVICE_" is a member of:

SELECT DISTINCT Groups.GroupName FROM Devices,Groups,Groupmembers WHERE (Groups.GroupTypeID = 101) AND Groups.GroupID IN (SELECT GroupID FROM GroupMembers WHERE MemberID IN (SELECT DeviceID WHERE DeviceName='_NAME_OF_DEVICE_'));

- All the devices that can be found in two different groups: "Group1" and "Group2":

SELECT DISTINCT DeviceName FROM Devices WHERE EXISTS (SELECT memberID FROM GroupMembers WHERE DeviceId = MemberID AND GroupID IN (SELECT GroupID FROM Groups WHERE GroupName = 'Group1')) AND EXISTS (SELECT memberID FROM GroupMembers WHERE DeviceId = MemberID AND GroupID IN (SELECT GroupID FROM Groups WHERE GroupName = 'Group2'));

Knowledge Article