Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,296,502
Community Members
 
Community Events
165
Community Groups

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

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, 

2 answers

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.  

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.

0 votes

Ugh, that's a horrid thing to do. 

Your best option is to stop using a direct connection to the database.  It's slow, complex, messy and won't tell you as much as the UI will because some stuff you see is done by the code, rather than the data.

If you did this properly, with the REST API, you'd find all the stuff you are looking for is automatically available when you make the simple "show me an issue" call.  If you're not going to do it properly, then take a look at the apps in the marketplace, there are loads of them that give you all sorts of reports, without you having to look at (and hence understadn) the database.

Hi Nic, 

Thanks for taking the time to respond. 

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

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. 

I have used APIs before, but more for product-to-product process integrations, not for getting data out to store and report. Is there any documentation you can point me to help with this?

Concerning the apps, again, if you can point me to one that is recommended, I will take a look.  However. I was looking to avoid them as we are about to merge with another company with around 2,000 users, so any app for my team (about 50 users) could be a significant expense. 

I get that my request may be horrid, and I also understand that some things are done by code, but I also understand that in each database, there is a logic that can be followed, no matter how horrid.  But, sometimes, that horrid is just not worth the effort, depending on how necessary the outcome is. 
Are you saying that finding, understanding and then creating the link between issue and organisation cannot be done?  Or that it can, it is just difficult?

So far, I have not had any issues with speed.  Getting data from JIRA has often been faster than from the other data sources I have needed to use. 

Any help you or anyone else in the community can give here would be appreciated. 

Thanks, 

>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. 

 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
8.12.2
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you