Jira ServiceDesk - Getting issues from the database.

Question

Hi,

We are using Jira ServiceDesk version 4.2.1 running on Core 8.2.1.

The database is a Postgres database.

We have a near time replication of the database that we want to perform analysis on the issues in projects.

Looking at the tables jiraissue has some of what we are looking for, but it appears to be missing custom fields which I believe requires joining other tables.

Has anybody got details of how to construct a query to get those joined etc so that from a single issue you can pull all those fields.

I have looked at the API and although that would get me the fields easier we would then need a database to store those records to allow for a BI engine to perform the analysis.

Any help or pointers would be welcomed.

Thanks.

Steven Oakley · Answer

I had already seen that KB, I was able to get the first and last example the middle two returned nothing.

I will investigate further.

Thanks.

Andy Heinzer · Answer

Hi Steven,

I see that you are looking to be able to see your Jira issues and their field values from a SQL query.  It can be kind of tricky to build a single SQL query to gather all this information, mostly because different plugins to Jira can store their field data in completely different tables.  But also because lots of different fields in Jira will permit the issue to have more than one unique value at a time for that field.

For example, components, versions, labels, are just a few of the fields that an issue can have multiple values for.  So when selecting against the jiraissue table and then trying to join to these other tables, we're likely to get back duplicates of some of the data in order to get back all the unique values that might exist for an issue.  But also if you take a closer look at the customfieldvalues table, you will notice that different custom fields will use different columns to store data of different types, such as string, number, text, etc.  This can complicate the joins needed to get back all custom field values without duplicating issues returned.

With that in mind, we do have a helpful knowledge base guide to find most custom-fields and their values in separate queries over in To find or view a Jira issue from the database using SQL.  It might be possible to try to join these queries into a single request, but I'm not sure of exactly how this could be done in a single query. I'm not sure that there is a good way to get exactly what you want without it looking like some issues get selected more than once to get back all the values in those fields.

If you were looking for specific custom fields in a single query, perhaps I could help you try to generate a query that might be able to show you only what you are looking for.

Please let me know.

Andy