Query for computers not in any group in USM

Document ID : KB000074275
Last Modified Date : 01/06/2018
Show Technical Document Details
Question:
Is there a query to extract a list of computers that are not members of any group in USM
Answer:
SELECT name FROM cm_computer_system WHERE cs_id not in (SELECT distinct cs_id FROM cm_group_member);
will give you the name of any computer not in a USM group

Please note that you may have automatic groups being created based on OS , origin or a user tag or based on application discovery If you do have these type of automatic groups they may interfere with the results you are after.

for example if you have one of the automatic groups based on OS enabled then all computers will be in at least one group.

To work around this you will need to obtain the grp_id for each of these groups with the query
SELECT grp_id,name FROM CM_GROUP
you can then exclude these groups from your query
eg In order to exclude the groups 1015 4200 4201 4202 4203 4204 4205 use
SELECT name FROM cm_computer_system WHERE cs_id not in (SELECT distinct cs_id FROM cm_group_member WHERE grp_id not in ('1015', '4200', '4201', '4202', '4203', '4204', '4205'));