Database query to show where user(s)/group(s) are assigned to

[Deleted user]

I am in Jira inventorying and cleaning up the users and groups. Can you obtain this information via a database query or in another way? If it goes through the database, could someone make such a query for me?

[Radek Dostál · Accepted solution]

Well this depends what you want to look into.

From the top of my head, let's see.

Groups:

 - project roles, group-picker custom fields, filter shares, dashboard shares, permission schemes, automation rules, workflow validators/conditions/functions, filter subscriptions, global permissions, 3rd party plugins, group membership count+user activity, probably a few more relations I might be missing

Users:

 - I won't go into any of those since I assume that the user is either logging in (thus presumably using something), or not logging this, thus not active

So this depends on your criteria. There are plenty of different ways to go about cleanups, but none of them are straight-forward. I wouldn't go with the db though, because the amount of relations including multiple user directories is just too much of pain to deal with. Do you have ScriptRunner or Insight (both expose "Groovy Script Console")? That way you could look into relations in a more "object oriented" way rather than 50 different joins and subqueries.

Lastly, do you have multiple user directories, are those for sure just local groups? Because if those were sync'd from an AD then I would imagine them being recreated again shortly after.

[Deleted user]

We have ScriptRunner and Insight.

What I try to achieve is a list of groups, filled with accounts or empty, where they are assigned to. I have found some queries already: list of groups and the project assigned to, users and groups.

I think for now I can get my work done with this information.

[Deleted user]

Can you do the same with scriptrunner?

[Radek Dostál]

You can do pretty much everything with ScriptRunner (incl. sql queries in it), but you would have to be very familiar with the underlying Jira's java API and be able to code your data mining in groovy.

It's easy to work with, but there is of course some learning curve so not likely something you can pick up just like that in a matter of hours.

The reason I mention it is because w/ java API you can use actual objects, managers, services, which is far more efficient by design. I mean there are pros and cons and I'm not saying you can't get that data from the db, but I am saying it's painful.