Maintain separate database(row->jira issue) and periodically refresh it according to jira changes

[Disha Trivedi]

I have a requirement where I need to pull data for all the issues across all the projects and store it in a database where each row represents and issues and columns could look like [project, issue_type, status, summary, assigne, reporter,etc].

This database needs to be refreshed periodically so that if new issues are created, then add their corresponding rows or update the rows corresponding to the issues that have been updated.

I can think of two ways to solve this:
1. python script querying jira api:
I already have a python script which pulls data from all the issues across all the projects in a similar format and stores it in a csv. However, the script takes 1-2 hours to run. And wouldn't be very ideal for my use case, as I'll have to query all the tickets everytime since I wouldn't have any other way to figure out which issues were added/updated. Another issue with this method is that we won't get the latest data unless queried right after the db was updated.

2. Jira automation rules:
I can create an automation rule with a scope across all the projects, and figure out the triggers on which I'll trigger this rule. However, I'm not sure if its possible to insert data to some external database using jira automation rules. Moreover, does it support python scripting?

Will scriptrunner be of any help in my requirement?

Please advice on the best way to get this done. Your help is much appreciated, thanks!

[Aron Gombas _Midori_ · Accepted solution]

For 2, among the automation components there is an automation action you can use to send an HTTP request to an external URL. With that it is possible to post every issue update and handle that on the other side. On the other side, you just build a small web application that handles the incoming changes and updates your database accordingly.

I am more worried about its scalability. If there is a large number of changes in a short period, then automation rules will be rate-limited, it will major burden on Jira, etc.

If you have access the Jira database, then you could just try to implement a single SQL query to retrieve the expected result and create a new table from that periodically.

All in all, what are you trying to do?

[Disha Trivedi]

So I read up about automation actions. We're using Jira cloud(premium plan) and I found the limits in this page . And like you said, I might not be a feasible option, since there'll be a lot of changes, as multiple projects are involved. To give you an idea, I queried all the tickets from certain projects in March which were created from 1st December using my script, there were approximately 6k tickets.

I think as far as accessing the database is concerned, since we're using Jira cloud, we probably don't have access to the database. Please correct me if its otherwise.

So what I'm trying to do is gather information from all the issues(task,subtask,bug,epic,etc) and store it in a comprehensive database with predefined columns(Ex: issue_key,issue_type,summar,assignee,reporter,story_points,labels,components,etc), where each rows represents an issue be it of any type. And then we'll be building a custom dashboard from that database according to the requirement. And we're planning to refresh that database once in a day. By refresh I mean, adding new rows for newly created issues, and updating existing rows if value in any of the columns changes.

Also, thank you for the prompt response.

[Aron Gombas _Midori_]

You're right, on Cloud you can't have direct access to the underlying database. So that option is ruled out.

If I were you, I would try these approaches:

1. Atlassian is working on Atlassian Analytics, its latest solution for custom reporting and BI. Maybe you could build your dashboards with this. (Note that it is available for Enterprise deployments only.)
2. Using the Jira Cloud REST API. It is basically the "equivalent" of the database-level approach. You can write a script that periodically gathers info from the REST API, and transforms that into your own data source. But, limits apply also here!
3. Or, you could even try the "just export data" approach. We have a customizable Excel exporter app, and then you can convert from the spreadsheet to your data source. Note that this app relies on the standard REST API, so maybe you need to slice your data and instead of exporting 6000 issues in one, export 6 spreadsheets with 1000 issue in each (just an example).

[Disha Trivedi]

Well, ours is premium and we're not moving to enterprise anytime soon, 1 is out of question and 3 is also paid after a 30 day free trial.
And as I've already worked quite a bit on a script using Jira Cloud REST APIs, I think 2 would be the most suitable approach for me.

Thanks for the help!

[Aron Gombas _Midori_]

(Your efforts and time to build 2 are not free, so 3 may still be a viable option. But you will see!)

[Disha Trivedi]

3 is not my call, but will see, again thanks :)