Extracting data from JIRA through ODBC

[Foad Moraid]

Hi community,

I'm trying to extract Jira data through ODBC in order to build an PowerBI dashboard visualizing KPI, status, hours etc..

I have no problem accessing all the tables and loading them into SSIS/PowerBI, the problem is that we have a custom filed in Jira that I can't find in the tables. 

Does anybody know in which table/view the custom fields are stored?

Or how I can find some documentation on Jira data model? 

Thanks!

Foad

[Rambabu Patina _Appfire_ · Accepted solution]

Hi @Foad Moraid, Welcome to the community.

Here is the documentation to find out the custom fields related data at https://developer.atlassian.com/server/jira/platform/database-custom-fields/

• customfield - Contains the definition of the fields
• customfieldoption - Contains any defaults or other information that might be set in the field
• customfieldvalue - Contains the actual values held by the fields.

Hope this helps you.

Thanks,
Ram.

[Foad Moraid]

Hi @Rambabu Patina _Appfire_

Thank you for your answer!

One question though!
In the table/view FIELDS, all our custom fields and their ID are stored. But I can't figure out how to join that to Issues..?
The reason for joining with Issues is so I know what CustomFields values our Issues have in order to create a filter on it in PowerBI.

Thanks!

Foad

[Rambabu Patina _Appfire_]

@Foad Moraid You can join with the tables 'jiraissue' and 'project' to retrieve the custom field values for the issues. For example:

<select query>
from CustomFieldValue CFV
inner join CustomField CF on CFV.CustomField = CF.Id
inner join JiraIssue JI on CFV.Issue = JI.Id
inner join Project P on JI.Project = P.Id

You can find more examples on the page database-custom-fields.

Thanks,
Ram.

[Foad Moraid]

Thanks @Rambabu Patina _Appfire_

I can't find any CustomField or CustomFieldValue table. Do I have to ask our Jira admin to make it visible through ODBC? Or is it a view that has to be created in Jira first?

These are the Jira tables/views I have access to:

Attachments
BoardIssues
Boards
BoardSprints
Comments
Configuration
Dashboards
Epics
Fields
Filters
Groups
IssueAffectedVersions
IssueChangelogs
IssueComponents
IssueFixVersions
IssueLinks
Issues
IssueSubtasks
IssueTransition
IssueTypes
MyPersmissions
Project
ProjectCategories
ProjectComponents
ProjectVersions
RoleDetails
Roles
SecurityLevels
Sprints
SprintsIssues
Statuses
Users
Watchers
Workflows
Worklogs
Votes

[Tom Lister]

Hi

many items in that list are not recognisable Jira tables. Check with the dbas what they have given you access to. It could be a set of predefined views. They can advise on how they have set that up to provide data. 

[Foad Moraid]

I was suspecting it could be a access issue. I'll ask our Jira-admin.
Thanks @Tom Lister

[TE228476]

Hello Foad, Did you find an answer for your problem?

I am having the same issue, I can not see the CustomField or CustomFieldValue table.

Regards 

Juan N.  

[Cesar Sanchez]

Not sure if you found a solution but with Cdata Odbc you need select "true" on custom fields in ODBC settings