MSSQL Query for ticket details

Peter Cselotei - Lupus Consulting Zrt_

Hi all,

We are using JIRA with MSSQL database.

I have the task to use BI at the issue details. The problem is that I cannot create an mssql query where I can get every detail on a ticket. 

Do you have any ideas/experience, mssql query code to get the following query result?

issue ID, issue key, summary, priority, resolution [usual issuedetails],project, project category, WORKFLOW, workflowscheme.

Any help woulbd much appreciated!

Best: Peter Cselotei

Dave Theodore [Coyote Creek Consulting]

I would not recommend connecting directly to the database for this purpose.  Atlassian changes the database schema regularly with each release, so there is a good chance the SQL that works today will not work at some point in the future once you upgrade.  Other reasons why it's not a good idea to connect to the database directly:

1. Security - All of the built-in issue security is in the application. You completely bypass this and have the ability to expose data to people that are not authorized to view.
2. Security - Providing additional network access to the database allows attackers another vector to gain access to your data.
3. DOS - If your script does not close connections properly, you may consume connections that are needed by the application. This creates a denial of service (DOS) attack vector.
4. Performance - If you create expensive queries with 15 joins and run the query in a tight loop, you can very easily negatively impact the end user experience.

These are some of the high points, but there are many other reasons why connecting directly to the DB is a bad idea. The Jira REST APIs are the right tools for the job and allow you to easily do what you are looking for. Atlassian even publishes an example of exactly what you are trying to do. Since you know the JQL that you want, you can simply run that identical query in a REST call and pull the data out that you're after, then post process it to pump it in to the system doing the data analysis.

One word of warning on the REST API, though.  None of the Atlassian apps have any sort of protection against abuse built in to their APIs. You should query at sensible intervals and pull only the data that you need in order to minimize the performance impact on the system.  For example, if you only want to see what changed since the last query, don't pull out 1000 issues and parse them outside of Jira.  Use JQL to provoke Jira to respond with the issues that have changed since the last query run. I hope that helps.