Custom JIRA reports via SQL

Question

I want to create a report based on a timeframe from the JIRA (ServiceDesk) database that can provide:

* timestamp ticket opened
* timestamp opened and by whom
* timestamp changed and by whom
* timestamp ticket closed
* Subject/topic of the task

and possibly other basic details.    I don't see this capability in any plugins I've looked at, and I don't believe we should actually need a plugin for this, as it's all in a MySQL database.   A simple tabular SQL report would suffice.

Anyone doing something like this?   What would the relevant fields be I would be concerned with.

Thanks.

Forrest Aldrich · Answer

I would need to craft something in the search language, something similar to :

updated >= -2w order by updated DESC

While this is handy, I want to have this in a tabular format that I can print out reasonably or include in a document.   I know these can be exported to PDF.    Are the schema fields documented somewhere.

I had thought a simple MySQL query would do the job.

Michael Clinton · Answer

Couldn't this all be handled by searches inside the Service Desk system?

I just did an issue search and filtered by my Service Desk project name. Then I included the following columns:

* Summary
* Created
* Creator
* Resolution (resolution status/name)
* Resolved (date)
* Updated (last updated)

Then you could filter down by date range.