Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


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
Community Members
Community Events
Community Groups

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

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!

1 answer

1 accepted

0 votes
Answer accepted
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Apr 28, 2023

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?

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_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 02, 2023

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

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!

Like Aron Gombas _Midori_ likes this
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 02, 2023

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

Like Disha Trivedi likes this

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

Like Aron Gombas _Midori_ likes this

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events