SQL Query help: AD group usage in Jira and Confluence instance.

[Vasanthakumar Velusamy]

Team, I am trying to fetch the list of projects using particular AD group in Jira and Confluence. I have tried few query but no luck, can someone help me on this?

[Tom Lister · Accepted solution]

Hi @Vasanthakumar Velusamy 

This will list projects, roles and members

SELECT  pname as Project, pr.name as Role, roletype, roletypeparameter
FROM public.projectroleactor pra, projectrole pr, project po
where pr.id = pra.projectroleid
AND po.id = pra.pid

You can add filter to restrict to a group

SELECT  pname as Project, pr.name as Role, roletype, roletypeparameter
FROM public.projectroleactor pra, projectrole pr, project po
where pr.id = pra.projectroleid
AND po.id = pra.pid
AND roletype = 'atlassian-group-role-actor'
AND roletypeparameter = 'jira-administrators'

I don't have access to confluence db at the moment

[Vasanthakumar Velusamy]

Thanks @Tom Lister for your response. Its worked as expected.