Community
Products
Jira
Questions
JIRA total number of Issues and comments per project

JIRA total number of Issues and comments per project

Hi all,

What I am looking for is something that I can create a report such as:

Project 1 - 120 issues (10 not closed/resolved), total of 400 comments (50 comments on open issues)

Project 2 - ...

I have read that the JIRA toolkit plugin can "The JIRA Toolkit Plugin highlights issues that receive a lot of comments and attachments. Add these columns to list view to easily see which issues may need attention or are taking a lot of collaborative work to resolve."

which is close but first I cannot find where the plugin list such information and secondly I need totals of the number of comments and number of attachments for each project. Additionally I would like to know the total size each project takes up with all things added together.

Thanks in advance!

Robert

3 answers

1 accepted

Comments are in the jiraaction table. You should join by the jiraaction.issueid field to the jiraissue.ID field and restrict the selection using the jiraaction.actiontype = 'comment'

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Thank you. I am not a SQL expert but I will give it a try. This seems to give me the number I am looking for: SELECT count(*) FROM jira.jiraaction WHERE jiraaction.actiontype = 'comment'; Do you know how I would do this same thing for attachments as well?

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

I've found it with the JIRA CLI from Bob Swift, which puts me into an environment I am comfortable in, the command line! Thanks again!

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

If you still need the attachment information, I can give you that as well. I found the sql way for read only access to be faster and less intrusive on the overall system for reporting purposes.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Yes please that would be great!

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

The table is called fileattachment and you can join to the jiraissue table just like before

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

SELECT issueid, COUNT(*) FROM fileattachment group by issueid

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Reply

Would SQL statements to collect your information work for you?

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Reply

Yes. I already have the issue counts I just need to find out the total comments. I cannot seem to find that in the SQL database. We use MySQL.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Reply

Suggest an answer

Log in or Sign up to answer

Jira