I want projects list and count created from jira created to particluar data

srrekanth February 3, 2013

Hi,

Is there any query in mysql for gettimng project list and count from jira created data to say 31st nov 2012 i.e period wise because we want to send the report in monthly,quaterly,yearly bases.Is it is der kindly share me the query so that i will be much useful for us.

1 answer

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 3, 2013

Why do it in SQL?

Jira can answer the question - define a filter for "created after X, created before Y" and give it to a dashboard gadget for "filter statistics". If you choose to group by "project", it'll tell you the numbers. Break it into several for quarters.

If you want to do it in SQL, then you need to know the SQL and have a basic grasp of the internals (note that people here prefer not to hand you answers on a plate because that way, you don't learn anything). You need to look at the table jiraissue, and the columns created and project, but you'll then need to start joining it to get project names etc out.

srrekanth February 3, 2013

Hi nic,

I have checked in jiraissue table and project table and i have found that in jiraissue table there is only issue created date for particular project and in project table there is no date for created.

i cant use the filter option since i need to add admin as a user in all group and run the "created after X, created before Y"

Is there any way to find this data. . .

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 3, 2013

No.

The project creation date is not stored. You might be able to extract it from your web logs if you are logging accessed urls.

That's why I said Jiraissue - the best you can do is look at the first time the project was used (i.e. the earliest issue created). I apologise for not pointing that out to start with, I should have said it.

For your admin issue, no, you don't need to be admin. Create a reporting user who automatically has "browse" in every permission scheme.

Suggest an answer

Log in or Sign up to answer