JIRA total number of Issues and comments per project

[Paul DeSousa]

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

[Norman Abramovitz · Accepted solution]

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'

[Paul DeSousa]

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?

[Paul DeSousa]

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!

[Norman Abramovitz]

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.

[Paul DeSousa]

Yes please that would be great!

[Norman Abramovitz]

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

[Norman Abramovitz]

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

[Norman Abramovitz]

Would SQL statements to collect your information work for you?

[Paul DeSousa]

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.