Query to find out the projects with zero issues which got created 6 months ago?


Can any 1 please help me to find out the jira projects with zero issues which got created before 6 months ago. We are planning to make the cleanup activity in jira before migrating to latest version.

Kindly suggest.



1 answer

1 accepted

0 vote

I'm afraid there isn't an easy way to do this, because it's a negative query - you're looking for the absence of data. Jira does not store a project created date

The only way I know of doing it without code is:

  • Login as a user who has read access to ALL projects (check your permission schemes for this)
  • Go into search and blank out any query that is there, and save the filter. This should basically mean "all issues"
  • Nip to the dashboard and add a filter statistics gadget, which uses your new "all issues" filter, and groups by "project"
  • You now have a list of all projects which have at least one issue
  • Now go to the full list of projects in the admin area.
  • And the boring bit - subtract the list of projects with 1 or more issues from the full list of projects

(I did it the other way - wrote a housekeeping report as a plugin)

Hi Nic,

Thanks for your reply.

I am able to fetch the projects with zero issues using query. But i am not able to fetch the projects which was created 6 months back.Query displays nearly 250+ projects with zero issues but we have to delete the projects which got created 6 months back. As of now the query displays all the projects.



Correct. You can't look for data that is not there.

There are a couple of tricks I've used in the past though

1. Hack the database

Add a date column to the project table, then add a trigger to the database that sets the date whenever a row is inserted. This works from the point of implementation, but:

  • you have to remember to account for it during upgrades
  • Jira doesn't use it (unless you write code)
  • It tells you nothing about existing projects

2. More advanced SQL and reasonable guesswork

I mentioned a housekeeping report - the one I wrote has some additions to what I wrote above. It explicitly exposes the "project ID", which is the counter for projects. Whilst the numbers themselves aren't a lot of use, the fact that you can rely on the number rising for each project creation means you can know the order in which projects were created.

You can then start to estimate - my report also extracts the oldest issue in any project that has issues. This is NOT the project creation date, but it is (barring move or delete), the project was-first-used date.

Combining a known order of project creation with some knowledge of some of the actual project usage dates enables you to get something resembling "6 months old".

It could well be inaccurate of course, unless people ALWAYS create an issue on the same day the project is created AND that issue is never moved or deleted. But it does get you pretty close.

(I haven't published the report, it was for a client, but you can see how to reconstruct SQL for it)

Hi Nic,

Many thanks for the detailed info.

I will try your way to acheive this.



Hi Nic,

I have raised 1 issue regarding upgrade of jira6.1 from jira5.0.7.

Can you please suggest some solution to resolve. PFB Issue




Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,237 views 14 19
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot