Query to get users groups within named Approval Groups

[NicOla]

I have a list of App Portal Catalog Item Approval Groups. I want to find the user groups for each Approval group.

I had a look at the tables – but can’t work out a query that will get me what I want.

So … does anyone have a SQL Query to list the users groups within a named Approval Group?

[CharlesW · Accepted solution]

No idea if this is what you were looking for, but the following query should give you all of the workflows, and the approval groups attached to each workflow. 

select templatename as WorkflowName, ag.GroupName,
CASE
WHEN aptm.ID_FK= 0 THEN 'Dynamic Approval Group (manager)'
WHEN aptm.ID_FK!= 0 THEN 'Standard Approval Group'
END AS ApprovalGroupType, aptm.Position as Level
from WD_ApprovalProcessTemplate apt
inner join WD_ApprovalProcessTemplateMembers aptm on apt.ApprovalTemplateID = aptm.ApprovalTemplateID_FK
left join wd_approvalgroup ag on ag.ApprovalID = aptm.ID_FK

Manager (dynamic) approval groups do not get entered into the WD_ApprovalGroup table, so they really don't have a name, so I added an ApprovalGroupType column to indicate if it is a static approval group, or a dynamic approval group. In this case, the groupName shows up as NULL

[NicOla]

This is perfect. Thanks so much.

[jdempsey]

If you're asking what AD Security Groups are included as members within each Approval Group in App Portal, you're probably out of luck with just a SQL query.  The Approval Group memberships are stored in SQL as AD GUIDs within the WD_ApprovalGroupMember table.  You could query the list of GUIDs using something like this...

SELECT ag.[GroupName]
      ,agm.[Position]
      ,agm.[Approver]
FROM [AppPortal].[dbo].[WD_ApprovalGroup] ag
JOIN [WD_ApprovalGroupMember] agm ON ag.[ApprovalID] = agm.[ApprovalID_FK]
GROUP BY ag.[GroupName], agm.[Position], agm.[Approver]
ORDER BY ag.[GroupName], agm.[Position]

But then you'd need some code (e.g. PowerShell) to look up the GUIDs in AD and return a meaningful display name for the group/user.

[NicOla]

Thanks Jim. I just need the group names - so all good.