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

Hi,

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.

Thanks,

Jayasingh

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.

Thanks,

Jayasingh

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.

Thanks,

Jayasingh

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

https://answers.atlassian.com/questions/255892/after-upgrading-jira6-1-from-jira5-0-7-services-is-not-getting-displayed

Thanks,

Jayasingh

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

365 views 4 10
Read article

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