Find all projects where a specific role is "unused"

[ITST]

Hi everybody, 

can anyone help to get a SQL query for JIRA that I am able to find all projects where a specific role is "unused". 

For example: all projects where the Developer Role is empty? (containing no user and no groups)

I am very appreciate any help.

Kind Regards

Elke

[Nic Brough -Adaptavist-]

The only real way to do that is to look at the admin pages for each project.  You'll need to look at the people in the roles, the notification and permission schemes and edit the workflow to see if a role is used in functions.

There is a big question though - why?  What problem are you trying to solve by discovering where a project is not making use of a role?

[ITST]

I need to know which project has an unused developer role. It's just a check, but it's not possible to check it project by project because we have more than 500 projects and this is impossible. I only need to know, which project has an empty developer role and need to be adapted. 

I am sure, that there is a way via SQL.

It's not because of an problem, it's only to know that the project setup is complete in our way of definition.

BR Elke

[Nic Brough -Adaptavist-]

I'm sorry, but that does not explain why you think you need to know when a role is not in use in a project.   What would it actually tell you?   What do you want to do with the information?

If it's because you want to add developers to the project, that's fine, but you need to look at how the project uses the role before you do that, and that means looking at the whole project, checking notification scheme, permission scheme, workflow and automations.  "Role is empty" without the project context tells you precisely nothing of any use.

If you're trying to identify when a role is unused, then actually all you need to do is ask "is it used even once by any project?".  If it is, then there's no use for any more detail on where - you aren't going to delete the role.

I think you might be trying to answer the wrong question here.