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

[jayasingh]

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

[Nic Brough -Adaptavist- · Accepted solution]

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)

[jayasingh]

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

[Nic Brough -Adaptavist-]

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)

[jayasingh]

Hi Nic,

Many thanks for the detailed info.

I will try your way to acheive this.

Thanks,

Jayasingh

[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

[sandeep sankalapur]

Join two tables `project` and `audit_log`

select PNAME, created, LEAD, PKEY, PCOUNTER, AUTHOR_KEY as project_creator from
(select * from project where pcounter = 0) A
INNER JOIN (SELECT * FROM audit_log WHERE SUMMARY = 'Project created' and created < 'provide-actual-date') B
ON A.PNAME = B.OBJECT_NAME order by created asc