Best way to connect to the JIRA database and pull data so that we can show on a management dashboard

[Jeremy Doyle]

We want to be able to pull the JIRA requests into a SQL database to display requests for Managers and what is in their current queue.  What is the best way to do that?

[Nic Brough -Adaptavist-]

Welcome to the Community!

You can't use SQL on a Cloud system, so there's actually no question here.

Even if you could, the Jira database is simply not intended for reporting, and reading it with SQL is the very worst possible way to do any reporting on Jira data.

The best thing you can do is build your reports in a Jira dashboard, or use its dashboard reporting functions in Confluence (Jira dashboards are great for people familiar with Jira and not wanting any extraneous stuff around their charts, but Confluence pages are better for management reporting, as you can include brief explanations and detail ad-hoc rather than ask non-Jira users to have to understand exactly how Jira works)

There are other options - reporting apps can extend the Jira dashboards, give you more reporting (both inside Jira and in Confluence) and some can provide their own separate dashboarding functions!

[Muffex]

"reading it with SQL is the very worst possible way to do any reporting on Jira data"

This is simply not true. When using BI analytics tools (like MS PowerBI) it is very useful and easy to export data from databases. We are currently joining data from several systems databases to create cross-system reports. With your current cloud "solution" there is almost no way to gain full access to OUR OWN DATA (which is usually accessible via the DB).

How will we be able to track activity on a granular level? (like: userX created itemY in locationZ; userA updated itemB in locationC; ...)

[Nic Brough -Adaptavist-]

I'm afraid it is absolutely true.

Please re-read the third line in my answer there, but I should have emphasised the last words as "Jira data".

BI analytics tools are great, when they are pointed at databases that are built for reporting, or have well-defined data dictionaries written by people who thoroughly understand the database they are looking at.

MS PowerBI pointed at an Atlassian database will fail, and fail hard.  It's an absolute truth that it's the worst possible way to do it.  Because the database is utterly unsuitable for reporting, and you don't understand it, so you won't be able to build a decent dictionary, or maintain it on every change (potentially as frequently as every 6 weeks)

To use your examples:

• UserX created itemY in locationZ:  You need to join at least 8 tables to get human readable data, and a LOT more if you want any information on itemY or LocationZ beyond "Charlie created XYZ-123 in London".  And that is assuming that you are starting from the most simple point of knowing which item you want to look at.
• userA updated itemB in locationC:  Now you're up to 20 tables, again without any readable data beyond "Charlie changed the colour field on XYZ-123 in London"

[Muffex]

Thanks for your answer. In the end I don't care whether it is "good way" to do sth. or not. People get tasks (like: creating a metrics dashboard) which need to be completed. (Although I agree that optimizing the solution is always/often the right way to go.) 

So, if you recommend to not use the database (which is as of now available AND stores the required information), how to approach the mentioned scenario? Especially when using the cloud version of Jira, since there is no DB access. 

Events of each user and the affected/related item, including it's path, have to be recorded. The "location" was meant as the path (e.g., space->epic->task). 

Example:

User: Bob

Event: created

Item: subtask

Location/Path: spaceA.epicB.taskC

[Nic Brough -Adaptavist-]

So you now understand that SQL is the worst possible way to report on a Jira system?

[Muffex]

Wow. :D

So removing an avaible interface from the software and then blaming the customer for using it. While not offering a suitable alternative. Good job. The fact that you refrain from answering the question underpins that. 

[Nic Brough -Adaptavist-]

I'm not refraining from answering anything.

I am trying to explain why SQL is the worst possible way you can report off Jira.  The Jira database is not useful for reporting off. 

It's complex, so most people don't understand it well enough to be able to begin to build useful reporting from it, and even when they do, most of the reports they want are likely to be hideously complex to build, many will be non-performant (to the point of crippling your database service, rendering Jira unusable), and maintaining them would be a nightmare (Do you really want to have to review every single SQL statement you have, or the entire data dictionary, every 6-8 weeks?)

I'm not blaming anyone for anything.  I'm just trying to explain that you don't want to descend into this nightmare, and that it's simply not true to claim "a BI tool based on direct database reads will work fine".

[Muffex]

You still did not offer an alternative to simply using the direct DB access.

Is there even any? 

[Nic Brough -Adaptavist-]

Use a reporting tool that understands Jira data for you.  EazyBI for example, as per the answer from @Janis Plume _eazyBI_ or have a look in the Atlassian marketplace for other options.  Or look at Atlassian's data lake option.

[Muffex]

That is exactly the problem. Please use "Tool XY" or "look around here, there is also something interesting".

I want to have the actual data which should be exportable. I want to be able to compare them with data from other systems. The APIs from MS (MS Graph) or HCL (SPI) allow me to get event logs. If Atlassian doesn't provide such APIs, you almost HAVE to go for the database. The current database structure is not a challenge for the average data analyst. Accordingly, I don't want software "that understands something for me", I can do that myself.

Furthermore, the individual reporting of a software is not sufficient for our needs. The data from different systems must be integrated in order to obtain meaningful results.

[Nic Brough -Adaptavist-]

mmm, I have to take issue with "The current database structure is not a challenge for the average data analyst." - I've shown the Jira database to some well-above average data analysts and they've all agreed - don't use it. 

It is an expert-level data analyst I'm quoting when I say "The Jira database is not intended for reporting, and reading it with SQL is the very worst possible way to do any reporting on Jira data"

The data will also completely defeat anyone trying to analyse it - can you tell me which bits of data that you see on screen and want to report on are not even in the database?  The ones that mean you can't report on them without replicating the logic behind them?

Anyway, yes, I understand you want to report, and report consistently.  The best way to do this is use a tool that understands the Jira data.

[Muffex]

Well, thanks again for your answer. I won't continue to argue about the database because I found event-log-like data quite fast.

However there are only few low level logs of user activity. Like "userX created issueA" or "userX created commentC". 

Events like "userX read/opened issueA" are not logged at all. At least I could not find this.

We are particularly interested in events that reflect user activity. Additionally, there won't be DB access in the future so we have to find another way to get the required information. 

[Nic Brough -Adaptavist-]

Yes, as it is with the database, you do not get what you think you want from the logs because they do not have the data you want.

You are going to need to go back to your actual report requirements and take a look at what you really want, then build something that can extract it.

[Muffex]

We are probably going to build a Forge application.

[Nic Brough -Adaptavist-]

Forge is a lot more powerful than the Connect option (although that still has a place).  Fortunately, it still has no database access, so we still can't get it too horribly wrong when we write stuff.

I reckon this is exactly the right route for you - Forge will give you Atlassian data with a decent structure that is easy to work with (the opposite of database access)

[Julius Zatroch]

Hi Nic.

Working on customer cloud migration we are strugling with  Atlassian guys to get a list of not correctly migrated request types.... I can imagine with only READONLY access to Jira Cloud database we can get answer faster....

Isn't possible to have Local Database connection in your ScriptRunner for Jira Cloud?  In Server/DC version is available and limited only for reading database, so you cannot do anything wrong.

[Nic Brough -Adaptavist-]

No, it's not possible because Cloud gives us no way to write code that could read the database.

[Muffex]

Which is a shame. ;) 

[Nic Brough -Adaptavist-]

No, it's a good thing.

I've seen far too many Jira systems either with horribly useless reporting, or regularly being taken down (non performant) because people have tried to use the database for reporting.

[June Lee]

Hi Nic,

We are using Jira v8.19.1 (obsolete version). We are consider to move to Jira cloud version if I can clarify the following questions:

a. We are using Power BI to build a dashboard to show the latest comment of project ticket in Jira v8.19.1. The main reason to do this is because Jira's dashboard report does not able to show latest comment of project ticket. Is this issue is now resolved in Jira's cloud version? 

b. Does Cloud Jira system able to secure sensitive data such as patient demographics and pathology test data?

c. Is it possible for us to migrate Jira v8.19.1 to Cloud Jira, with ticket attachments and screenshots?

[Nic Brough -Adaptavist-]

a) No, there's no field been added to the core.  There are plenty of apps that can provide it, and reporting tools can also extract it

b) What do you mean by "secure sensitive data"?  What are you trying to achieve?

c) Yes

[June Lee]

Hi Nic,

a) Noted there is no way for us to capture the latest comment field from the latest Jira Dashboard reporting tool. Am I right to say all the other apps and reporting tools can extract it but we have to pay for it?

b) As stated in my previous comment, patient data

c) How to migrate ticket attachments and screenshots from old Jira to new Jira?

Thanks for your help to support my queries.

[June Lee]

Example of Patient data: Patient name, DOB, Gender, address, contact number and etc. Since we used Jira's Test Manager app for testing, we often attached the diagnostic report too. These are classified as sensitive data. My management is questioned me how the Cloud version of Jira to secure the patient data if we are going to use Jira to manage our projects in the future. 

[John Funk]

Hi @Jeremy Doyle  - Did you ever get this solved?

[Janis Plume _eazyBI_]

Hi,

As mentioned already, users do not have access to the Jira Cloud database. I can suggest trying out the eazyBI app for Jira. It provides the flexibility to cover most of your reporting needs without direct access to the Jira database.

Kindly,

Janis, eazyBI support