How to get jira project that has not updated before six months?

Hi Support,

We want to write a SQL to get jira project that has not updated before six months?

For example,today is 2018/01/23,that I want to get current date - 6 months that means I need to get the last issue updated before 2017/07/23,Is it possible to get the information for postgresql database?

Thank you in advance!

1 answer

0 votes

I think the SQL query would be like this

select * from 

(select project, max(updated) lastUpdate from jiraissue group by project) a

where lastUpdate < to_date("YYYY/MM/DD", "2017/07/23")

Hi Support,

But we want to dynamic use this SQL,not just use to_date?

How could we revise to current - 6 months ?

SQL error message as below(our database is postgresql)

Thank you.

error_0123.PNG

select * from 

(select project, max(updated) lastUpdate from jiraissue group by project) a

where lastUpdate <
now() - interval '6 month'

Hi Support,

But it can't show the project issue key?

It only shows the timestamp?

Hi Support,

select * from 

(select project, max(updated) lastUpdate from jiraissue group by project) a

where lastUpdate <
now() - interval '6 month'

How could I get project issue key from this SQL ?

Thank you!

select b.pkey, a.lastUpdate from 

(select project, max(updated) lastUpdate from jiraissue group by project) a, project b

where lastUpdate <
now() - interval '6 month'

and a.project = b.id

Hi Support,

How could I add filter to SQL to filter that project not in A and B category?

Thank You!

Hi Support,

Any suggestions about it?

After get the project information,I need to batch update project permission to read-only permission through scripts?

Is it possible to use scriptrunner to do it? I need to set it to crontab job at the start of month.

Thank you!

That is exactly the problem with SQL queries. You should dig yourself into Jira SQL database and find out how to do certain things. In the end your SQL queries will get very large and it will be difficult to support your SQL quieries especially for people who will "inherit" your code. You should use Jira Rest Api to get all the information. It will be a much cleaner solution. You can read more about Jira Rest Api here:

https://developer.atlassian.com/cloud/jira/platform/rest/

But answering your question about the components you should join the nodeassociation table. Here is an example:

SELECT ji.issuenum, na.sink_node_id component_id
FROM   jiraissue ji
JOIN   nodeassociation na
ON     na.source_node_id = ji.id
       AND na.sink_node_entity = 'Component'
       AND na.source_node_entity = 'Issue'
WHERE  ji.project = 19130

Concerning Scriptrunner. Yes, it is possible to update project permissions to read-only. But that would be another question. I believe that your current question was answered. Users will look for questions and answers and this question will pop up for searching project with no updates. That is why you should make another question for Scriptrunner.

Hava a good day.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,731 views 17 21
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you