What is the database relationship between users and user fields on an issue?

Question

I am using Power BI to do reports based on JIRA data.

I have been able to work out how most of the tables can be linked together, however, linking a user to fields such as creator and assignee, I have not been able to work out.

Add to this we use the support part of JIRA so we have many users who are linked to Organisations.  I have worked out how to link the Organisation to the user but not the user to the issue.

I am in no way an SQL expert so please be simple with me.

What I would love to know is the data tables to bring into Power BI and the fields to use to link each.

Thanks,

Nic Brough -Adaptavist- · Answer

Yep, and there's another 2 things (maybe more, the 2 is from memory) you need to look at.

Please, please, please, stop reading the database.

It is always the wrong way to ask Jira for information.

Eric Sara · Answer

While not perfect I found that if I take the 'PUBLIC.APP_USER' table I can link the 'user_key' to the 'reporter' in the 'jiraissue' table and this gives me the link between the user and issue.

And then from the 'PUBLIC.APP_USER' table  I can link the 'user_key' to the 'user_key' in the 'AO_54307E_ORGANIZATION_MEMBER' table and this gives me the link between the user and the org.

I then link link the 'ORGANIZATION_ID from the table 'AO_54307E_ORGANIZATION_MEMBER' to the field 'ID' in the 'AO_54307E_ORGANIZATION' field and this gives me the org name.

What this does not give me is when the user is not linked to the an org but the org is on the issue. This one still stumps me.

Nic Brough -Adaptavist- · Answer

>The UI has its place, and we use it well. However, there are times when it is not the right solution.

I'm with you on that, there's a lot that Atlassian doesn't directly provide.

And "look at the database" is the wrong answer when you run into "it doesn't quite do what we think we want"

The "right solution" is to look at the REST API, not the databaase, especially as the database is going away for most of us.

>I am merging data from JIRA with data from other systems to give my team a one-stop dashboard overview. It is not taking the place of the UI used to track and update live issues, but more management reports showing a snapshot.