The following query finds a list of servers associate with UserGroups and TargetGroups.
SELECT distinct ug.name as UserGroup, g.name as [Target Group],g.dynamic,ts.hostname as HostName FROM groups g
INNER JOIN groupcache gc ON g.id = gc.groupid
INNER JOIN account acc ON gc.objectid = acc.accountid
inner join targetapplication tap on acc.targetapplicationid=tap.targetapplicationid
inner join targetserver ts on tap.targetserverid=ts.targetserverid
INNER join usergroup_groups on usergroup_groups.groupid=g.id
INNER join usergroups ug on ug.id=usergroup_groups.usergroupid
WHERE gc.classid='c.cw.m.ac' and acc.deleted = '0' AND g.deleted = '0' and tap.deleted=0 and ts.deleted=0 and ug.name ='INTL - WINDOWS - DCA - INUATSUP'
The above query did not return any server. However from the Password Authority GUI we could see one server and 5 accounts when we clicked the show button.
Password Authority 220.127.116.11
The query depends on the accuracy of the groupcache table.
After investigation we found that the groupcache table does not have any record for the group (searched by group id) .
Filter definitions for dynamic groups contained % characters. This prevented the groupcache table from being updated.
For example: Hostname beginswith 'ABC%' should be Hostname beginswith 'ABC'.