I’m trying to figure out if there’s a way to get the finish dates for different versions in a JIRA project. I want to do this by querying the Postgres database that JIRA uses.
Here’s what I’m hoping to do:
Set up a script that checks the database regularly
Pull out the end dates for all versions in a specific project
Compare the dates to see if they’ve changed
Send an alert if someone messes with the dates
I tried using the Plugenta audit log thing, but it can’t track version date changes. Any ideas on how to make this work? I really need to keep an eye on these dates to make sure nobody’s moving them around without telling anyone.
Has anyone done something like this before? I’d love to hear your thoughts or see some example queries if you’ve got them. Thanks!
I’ve tackled this issue before, and here’s what worked for me:
Instead of relying solely on database queries, I found it more effective to use JIRA’s REST API. It provides a cleaner interface and is less likely to break with JIRA updates.
You can set up a script that periodically calls the /rest/api/2/project/{projectKey}/versions endpoint. This returns all version info, including release dates.
To track changes, store the results in a separate database or file. Compare new data with the previous run to detect modifications.
For alerts, I used a simple email notification system. You could also integrate with Slack or other messaging platforms if that fits your workflow better.
This approach gave me more flexibility and proved more reliable in the long run. It might be worth exploring for your use case.
hey sofiap, i’ve dealt with similar stuff before. you could try querying the ‘projectversion’ table in the jira db. it usually has the release dates. something like:
SELECT vname, releasedate FROM projectversion WHERE project = ‘your_project_id’
might work. just remember to run it regularly to catch changes. good luck!
I’ve implemented a similar system for tracking version dates in JIRA. While querying the ‘projectversion’ table is a good start, you might want to consider creating a separate table to log historical changes. This way, you can easily compare current dates with previous ones.
For the alert system, you could set up a scheduled job that runs your query, compares the results with the last known state, and triggers a notification if discrepancies are found. Consider using a tool like Airflow or a simple cron job for this.
Remember to handle timezone differences and ensure your script has the necessary database permissions. Also, be mindful of query performance, especially if you’re dealing with a large JIRA instance.