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.
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:
(I did it the other way - wrote a housekeeping report as a plugin)
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:
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)
I have raised 1 issue regarding upgrade of jira6.1 from jira5.0.7.
Can you please suggest some solution to resolve. PFB Issue
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
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG