Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

2 answers

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.

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

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?

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. 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Software

👋 Looking for 15-20 volunteers to test Atlassian training content

Hi everyone! Are you interested in beta testing Atlassian University’s newest (unreleased!) training course? We’re looking for 15-20 volunteers to test our newest training course, Basic reporting...

790 views 24 28
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you