SAP: Database get Group relationships

Products

• CA Infrastructure Management
• CA Performance Management - Usage and Administration
• DX NetOps

Issue/Introduction

I am trying to get all groups which are not populated by automatic rules and which are not parent of another group. 

I am querying the MySQL DB directly, but I can not find the table/attribute which describes the group relationships.

Can you please help me and tell me the right attribute/table?

Environment

Release : 3.7

Component : IM Reporting / Admin / Configuration

Resolution

A) This is a simple MySQL query to list the groups' names:

select ItemID,ItemName,ItemDesc from t_group;


B) I modified it, just to display the Groups from A to E:

select ItemID,ItemName,ItemDesc from t_group where ItemID in (6868,6869,6870,6871,6872); 

+--------+----------+----------+
| ItemID | ItemName | ItemDesc |
+--------+----------+----------+
|   6868 | GroupA   | Group A  |
|   6869 | GroupB   | Group B  |
|   6870 | GroupC   | Group C  |
|   6871 | GroupD   | Group D  |
|   6872 | GroupE   | Group E  |
+--------+----------+----------+
5 rows in set (0.00 sec)

C) In the following MySQL query you can see the relationship between the groups:

mysql> select * from item_members where ChildID in (6868,6869,6870,6871,6872);
+----------+---------+----------------+---------------+---------------+--------------+-------+------------+
| ParentID | ChildID | ParentItemType | ChildItemType | ParentSubType | ChildSubType | Flags | UpdatedOn  |
+----------+---------+----------------+---------------+---------------+--------------+-------+------------+
|        1 |    6868 |              1 |             1 |             8 |            2 |     0 | 1599853951 |
|     6868 |    6869 |              1 |             1 |             2 |            2 |     0 | 1599853968 |
|     6868 |    6870 |              1 |             1 |             2 |            2 |     0 | 1599854000 |
|     6870 |    6871 |              1 |             1 |             2 |            2 |     0 | 1599854023 |
|        1 |    6872 |              1 |             1 |             8 |            2 |     0 | 1599854170 |
+----------+---------+----------------+---------------+---------------+--------------+-------+------------+
5 rows in set (0.00 sec)

The ParentID=1 is the root of the Groups list (All Groups).
ChildID=6868 (GroupA) and ChildID=6872 (GroupE) are under ParentID=1, hence they are under All Groups.
ChildID=6869 (GroupB) is under ParentID=6868 (GroupA).
ChildID=6870 (GroupC) is under ParentID=6868 (GroupA).
ChildID=6871 (GroupD) is under ParentID=6870 (GroupC).