IBM BPM - How to find a group membership of dynamic group(type 4 groups)

We stuck in situation once where dynamic group was locking everything and no users can log into the portal. We struggled to get details about dynamic groups, like what all is included in dynamic groups, we were especially interested in which all LDAP groups were part of that dynamic groups.

By following the below steps you can get details about Dynamic groups or which all groups are added in dynamic groups.

First you need to have name of the dynamic groups. Mostly dynamic group name will be something like 'Filtered set of users' so in our example lets take same name.

Step 1.) 
select * FROM LSW_DYNAMIC_GROUP where NAME like 'Filtered set of users'

take dynamic_group_id from there and use it in below query.

Step 2.)
select * from LSW_DYNAMIC_GROUP_CONSTRAINT where DYNAMIC_GROUP_ID=<Group_ID>

Here you may find one or more rows depending on creation of your dynamic group. if it is only created with users, there will be list of users in Value and you can use that.
If PG is being used as part of dynamic group you will get a row with value in Library_element_type = 24. here Library Element type is same as PO_type in lsw_po_versions and details for other type can be found using below blog entry I wrote earlier,

http://randombpmsolutions.blogspot.co.uk/2015/08/ibm-bpm-855-po-types-table-bpm-db-part.html

Step 3.)
You can get table name based on Library_element_type and then find details in perticular table. In our case for type 24 we can check below table

select * from LSW_PARTICIPANT where PARTICIPANT_ID='<LIBRARY_ELEMENT_ID>'

and from here you can go to LSW_PARTICIPANT_GROUP table to get group_ID

select GROUP_ID from LSW_PARTICIPANT_GROUP where PARTICIPANT_ID='<LIBRARY_ELEMENT_ID>'

Step 4.)
once you have group ID, you can put it in below query to get LDAP or internal groups used in dynamic group.

select gg.*, g.GROUP_NAME from LSW_GRP_GRP_MEM_XREF gg, LSW_USR_GRP_XREF g where gg.GROUP_ID=g.GROUP_ID and gg.CONTAINER_GROUP_ID=<GROUP_ID>


IF you have more then one entry of type 24 or any other in LSW_DYNAMIC_GROUP_CONSTRAINT then you can repeat step 3 and 4 to get all details