How to retrive Created Issue and resolved issue count for a list of project

[serge calderara]

Dear all,

I need to get from my Jira DataCenter instance a list of Created and Resolved Issue COUNT for a given month.

How can I get that result and then be able to export it to Excell

Thanks for help

regards

[Radek Dostál]

If you can run groovy scripts on your instance (e.g. ScriptRunner plugin) you could use ProjectManager interface to get all projects, then do a loop for each using IssueManager to get issueids, from that get (mutable) issue objects, from that the 'created' and 'resolved' fields. Collect stuff in a map, print it, easy.

Without groovy, this sounds like an SQL job. The 'jiraissue' table contains both created and resolved timestamps, so it should be possible to do some data mining on that. Depending on your db type you probably could make use of some date functions to get issues grouped per month they were created. The jiraissue table holds reference to the project's id, so joining it with the 'project' table sounds like a way to group issues per project.

However, as any documentation would state, SQL is not recommended and can result in damage if not done properly or by someone who does not know what they're doing - so anything on SQL level is always heavily recommended to get a backup first. Ideally that backup should also be tested to work (since creating a backup on a running instance can in some edge cases be volatile).

You also could automate stuff with REST given some scrappy engineering, like getting all project keys, incurring the JQL search for each key, paginating through each searchresult page, collecting data from issues' json. In any case, doable, but a little scrappy, so long as your indexes are not corrupt (which would affect issue count result for the project, but not the individual issue data itself).

All in all this sounds like a bit strange endeavor - can't really think of a good use for it, but, if it needs to be done I'd personally go with SQL, joining the 'project' and 'jiraissue' table.

[Niranjan]

Hi @serge calderara - Are you looking for created vs resolved conunt at project level or at the instance level (on all projects)?

If you are looking at project level, you can try createdvs resolved gadget.  If you are looking at instance leve, you may need to query at the DB level (you may not have access to all projects in your instance). 

Assuming you have access to all projects the below JQLs may help

created >= startofMonth() and resolution is not empty

created >= -30d and resolution is not empty

[serge calderara]

Hello, 

My goal is to create a report in excel in order to get the number of created and resolved issue for a liqt of project. I have full admin right.

By using filter and jql of course i can identified create and resolved issue but is there a way to get the count of them for each project or do I have to do that count in excell after exporting the results?

[Niranjan]

Hi,

JIRA's filtering capabilities does not provide aggregate funtions (count, sum , etc) like SQL.You can create the filter query and then use the filter query in a pie chart or a similar gadget and use project as the criteria. This may get the count. You can also manipulate the query results in excel and drill down the data as required.