Is there a way to find out how many JIRA users are in a project in 6.2?

[Ed Sakabu]

Is there a way to figure out how many users are associated with/involved with/have issues open/issues created in a project? We want to find out which projects have how many users associated with them. For example, we have 85 projects and 2189 active users. We want to determine how many users are "active" in each project. We know this is not a 1:1 relationship but it will give us an idea which projects are "more" active (not just by new issues, etc).

[Nic Brough -Adaptavist-]

Not really, because the definitions are too vague.

You need to start with a precise definition of "associated with". Both for a project and issues. What does "active in a project" mean? You need to think about timing as well as activity. There is a significant difference between "could do something in a project", "has done something in a project" and "has done something in a project, but can't do it now".

Now, the rules *can* be very simple - like "is in any role in the project" or "is assigned a issue in this project". Or complex like "was once in the role of user and has reported some issues but is no longer a user and has had some updates done by developers but still has open issues"

In other words, "define what in a project" means for your business. Even the most simple case is a perfectly good definition. (like "anyone who has touched an issue in the project" - that's a pretty good one. Or "could be assigned an isseue". But we don't know until you define it)

[Ed Sakabu]

Ok, "define in a project" (not what I actually said) means something that will prevent me from deleting the user's accout. In other words:

1. Is a creator of a issue.
2. Is assigned an issue.
3. Something else that prevents me from deleting the user's accout.

For example, I have 85 projects and 2189 active users. I want to determine how many users are "active" in each project. "Active" means fullfilling at least one of the above mentioned criteria. It would also be nice to be able to run a query that would list which users a "active" in eash of these project so I can give them to the project managers for follow up and or removal.

[Nic Brough -Adaptavist-]

Um, yes, you did say "users in a project" - look at the question title.

Anyway, those are nice clear rules, although reporting on them isn't as easy as it should be.

You'll find you can't delete users who have reported an issue, are currently assigned an issue, or have commented on anything. Jira will tell you why, and link to the issues that are preventing deletion, but because users could be across several projects, it's not really a project level report.

I'm not sure I can actually work out a way to list people who are not "in a project" though. The best I can do is a quick and dirty report to tell you who IS in a project, which you could then try to subtract from the full list. To do that, I would

Install the Jira toolkit addon

Add a field of type "participants", with global context (no need to put it on any screens if you don't need it)

Create a filter for "all issues"

Add a gadget to a dashboard - the most simple one could be "filter statistics" and choose "participants", that will list all the users, but I'd be tempted to use "2d filter statistics" with participants and project.

[Norman Abramovitz]

Here are some document pointers to assist in writing your sql queries.

https://developer.atlassian.com/display/JIRADEV/Database+Schema

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA

I cannot stress more, if you plan to update the database, I would do it through one of the api's (REST, SOAP, etc) or through the normal web interface and not through sql statements.

You will gain back users for your license if you disable them. I believe you only need to reassign issues verses needing to remove all references of that user if you remove that user.

[Nic Brough -Adaptavist-]

I already mentioned that in the the last comment I gave.

You can't delete a user who has reported an issue, is the current assignee or has commented on an issue.

Moving on to the SQL, you just need to read two tables. For reporter and assignee, read Jiraissue. For comments, it's Jiraaction (which you'll need to join to jiraissue on "issue id" to find out the issue they're commenting on)

[Ed Sakabu]

Hi Norman,

Actually I would prefer a direct SQL query rather than having to install and maintain an add-on that will have to be updated with the next version. The add-ons also don't lend themselves to exporting the data to be E-mail to project coordinators. I'll probably have to figure out an SQL query that will give the user names and counts of users that have opened an issue, commented on an issue, etc per project. I guess what I really need to figure out is what is the criteria that prevents a user from being delete able and what the schema actually looks like. Users tend to roll every quarter so the date of last login could help, but isn't a guarantee.

[Norman Abramovitz]

Can you run sql queries against the actual database? Would it be more helpful to get a list of users that has not logged into Jira for awhile since your end goal is to disable inactive users?