nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Rambabu Patina {Appfire}

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.

All comments

Rambabu Patina {Appfire}

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

custom-field

jira-software

database