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

[New]

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!

[Alexey Matveev]

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

[New]

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.

[Alexey Matveev]

select * from 

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

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

[New]

Hi Support,

But it can't show the project issue key?

It only shows the timestamp?

[New]

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!

[Alexey Matveev]

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

[New]

Hi Support,

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

Thank You!

[New]

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!

[Alexey Matveev]

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.