Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

request on oracle to find out unused projects

Cedric Samen
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
December 8, 2022

Going from this link 

I needed to update the script so that it display project name and project id but i was not successful up to now

Can you please provide some help?Please emergency

1 answer

0 votes
Cedric Samen
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
December 9, 2022

I found my solution and it works like a charm on Jira DC 8.20 Oracle db

select
ss.*
,case when ss. "Last Update" < CURRENT_DATE - 180
then 'NO'
else 'YES'
end as "Updated in the last 6 months?"
from (
select distinct
p.id,
p.pname as "Project Name",
p.pkey as "Project Key"
,case when p.pkey = p.originalkey
then NULL
else p.originalkey
end as "Project Archived?"
,cu.lower_uset_name as "Project Lead"
,cu.lower_email_address as "Lead Email"
,case when cu.active = 1
then 'YES'
else 'NO'
end as "Lead Active?"
,MAX(ji.updated) as "Last Update"
,count(distinct ji.id) as "Issue Count"
from jiraissue ji
right join project p
on p.id = ji.project
join app_user au
on p.lead = au.user_key
join cwd_user cu
on au.lower_user_name = cu.lower_user_name
left join propertyentry pe
on p.id = pe.entity_id
and pe.property_key = 'jira.archiving.projects'
GROUP BY p.id,p.pname,p.pkey,
CASE WHEN p.pkey = p.originalkey THEN NULL ELSE p.originalkey END,
CASE WHEN p.id = pe.entity_id THEN 'YES' ELSE 'NO' END,
cu.lower_user_name,
cu.lower_email_address,
CASE WHEN cu.active = 1 THEN 'YES' ELSE 'NO' END
)ss

Suggest an answer

Log in or Sign up to answer