How to retrieve Project Information from database? Script is missing projects.

[Josh Higgs]

Hello,

I am trying to find all my projects that do not have a Project Lead specified. I am using the script in the following article - https://confluence.atlassian.com/jirakb/retrieve-project-information-from-jira-s-database-1044111930.html - however this does not return any of the projects without a project lead. 

---

with rank_created as (
	select project,created,rank() over(partition by I.project order by I.created desc) as ranking
	from jiraissue I 
),
rank_updated as (
	select project,updated,rank() over(partition by I.project order by I.updated desc) as ranking
	from jiraissue I 
),
count_issues as (
	select project, count(*) as total 
	from jiraissue j group by project
),
users as (
	select user_name, lower_user_name, email_address, U.active ,rank() over(partition by U.user_name order by D.directory_position) as ranking
	from cwd_user U
	join cwd_directory D on U.directory_id = D.id and D.active = 1
)
select P.pname, P.pkey, U.user_name, U.email_address, U.active, RC.created, RU.updated, CI.total, to_timestamp(cast(A."ENTITY_TIMESTAMP" as bigint)/1000) as "Project created"
from project P
join "AO_C77861_AUDIT_ENTITY" A on "ACTION" = 'Project created' and A."PRIMARY_RESOURCE_ID" = cast(P.id as varchar)
join app_user AU on AU.user_key = P."lead" 
join users U on U.lower_user_name = AU.lower_user_name and U.ranking = 1
left join rank_created RC on RC.project = P.id and RC.ranking = 1
left join rank_updated RU on RU.project = P.id and RU.ranking = 1
left join count_issues CI on CI.project = P.id;

---

Can someone please advise what I need to change to pull down these projects?

Thanks.

[Mohamed Benziane]

Hi,

In case if you could use API you can try these REST API

/rest/api/2/project

/rest/api/2/project/{projectIdOrKey}

You will have a key for the lead project

https://docs.atlassian.com/software/jira/docs/api/REST/8.16.2/#api/2/project-getProject