request on oracle to find out unused projects

Cedric Samen

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

Cedric Samen

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