DB query: customfeld type and relation to project

[Oleksandr Muliar]

Hi!

Making some queries from JIRA DB failed to find relations, please assist me.

1)How customfields are related to project table? Ideally I need to query the list of fields created or related to separate project.

2)How to distinguish different types of custom field?

I have some custom fields in table “customfield” there are two key fields “customfiedltypekey”, “customfieldsearchkey” there must be a way to distinguish the type of the field, LOV for instance.

Thank you!

[Oleksandr Muliar]

I have finished doing my queries - it works eventually.

Now i would like to compare it with API performance.

As of start point - there is a mess in my head with that API.

I have specific project in jira ( i know its pkey from project table).

is there specific API service which will query all issues of the project with its all fields including custom fields?

Thank you very much! 

[Daniel Ebers]

Hi Oleksandr,

coming across exactly the same challenge on several Jira server instances from my experience digging into database and/or checking via API is time consuming task.

While we are not affiliated in any way to this App's vendor it helped us a lot:
https://marketplace.atlassian.com/apps/1219634/power-admin

Please feel free to compare with manual API approach. Maybe there are other Apps out there accomplishing the same - we did not check, to be honest.

For an 'automated' process as of DWH export approaches you would have to check if it meets your needs. My use case is always a clean up and pre-migration planning with Power Admin.

Cheers,
Daniel

[Nic Brough -Adaptavist-]

Seriously, reading the database is the worst possible way to get information from Jira.

Could you explain why you are trying to do this?  We can probably give you a far better way to achieve your goals.

[Oleksandr Muliar]

Hi.

We have jira. 7.5.0. where we create some projects, that projects require some custom fields and dictionaries. After we would like to use the data per jira tickets in some DWH reporting. As of now I my task is to collect the complete set of fields for certain project – so it could be exported further.

Please note, I am not familiar with parameterization of custom fields and GIRA in general, I du understand that there are APIs or web services but that is unreachable for me right now.

Thank you!

If you can give me proper web service for collecting jira tickets – would applicate that.

Thanks and regards

Alexander

[Nic Brough -Adaptavist-]

You should use the REST API to get the data from Jira.

For comparison and ease of use, let me just say that to fetch the data for a single simple issue with SQL needs a query with at least 18 joins, some 2-3 layers deep.  You have to understand exactly how the data hangs together and issue different SQL for different types of data.  Even if you understand the data, you can very easily build SQL that will cripple your database performance.

Or you can issue a single REST call that can't do any harm and gives you all the data you need.

Start with https://developer.atlassian.com/server/jira/platform/rest-apis/