Report of all active Jira projects and project leads

[Keshni]

Hi, 

What query can I use to give me a list of all active Jira projects with the project leads. 

Thank you :)

[Chander Inguva · Accepted solution]

Hey Keshni,

If you have access to Jira database you could pull data using following SQL Queries

• The first query gives project key as well as number of issues in it along with project lead
• While the second query gives last updated with project key and project lead

select p.pkey,p.pcounter AS number_of_stories,p.LEAD from project p ORDER BY pcounter ASC;

SELECT DISTINCT p.pkey,p.LEAD,MAX(i.UPDATED) as "Last Updated"
FROM jiraissue i
INNER JOIN project p
ON p.ID = i.PROJECT
GROUP BY p.pkey,p.LEAD
ORDER BY MAX(i.UPDATED) ASC

Hope this helps you get started

Thanks

Chander Inguva

[Casper Hjorth Christensen]

Hi Chander

You seem quite able with SQL, where I am not, and therefore I hope you can help me out.

I need to get the following from our MSSQL DB.

A list of all projects, with:

Last issue updated date

And the user who updated it

Project Lead

Project Lead Email

I hope you can help me out.

Thanks

[Robert Stumpe]

i needed this information too, so i just queried it from the database (mysql)

SELECT p.pname, p.pkey, u.display_name, u.email_address FROM jiradb.project p, jiradb.cwd_user u where lower(u.lower_user_name) = lower(p.lead);

this will give you the projects (without projects where the lead is no longer in jira) 

[Markeese Martin]

Does anyone have an Oracle Query?

[Seenivasan Mohanraj]

I need to extract all project info from Jira database like below using SQL query please help.

Project Key

Project Admin

Project Lead

Projecct lead email

Project name 

[Keshni]

Hi @Chander Inguva ,

Thank you so much for this!! It helped...

How can I can the project name rather than the project key?

[Chander Inguva]

Just add p.pname Keshni.

Glad i could help you

Thanks

Chander

[Keshni]

Thank you :)

[Chander Inguva]

Good luck Keshni 

[SreeKanth]

Hi @Chander Inguva 

I need to extract all project info from Jira database like below using SQL query please help.

Project Key

Project Description

Project Lead

Project Lead Email

Project Category

Project URL

Thanks

[Chander Inguva]

Hey Srikanth,

Try this SQL

select p.pkey as [Project Key], p.DESCRIPTION as [Project Description], p.LEAD as [Project Lead], cu.email_address as [Project Lead Email], pc.cname as [Project Category], p.URL as [Project URL] from project p
JOIN nodeassociation na ON na.SOURCE_NODE_ID = p.ID
JOIN projectcategory pc ON na.SINK_NODE_ID = pc.ID
JOIN cwd_user cu ON cu.user_name = p.LEAD
where na.SINK_NODE_ENTITY ='ProjectCategory'

Hope this helps

Thanks

Chander Inguva 

[SreeKanth]

@Chander Inguva  That saved my day thanks for the help.

Appreciate it.

Regards,

SreeKanth

[Chander Inguva]

Glad i could help you @SreeKanth 

Cheers

Chander

[Deleted user]

@chanderIng

Hi Chander wondering if you could come up with a query to get the distinct projects in Jira using JQL  

Thanks!  @Newelle Horn 

[Matthew Knatz]

@[deleted] 

JQL can't return aggregate data, so things like "Distinct" and "Group by"  which are standard SQL operations don't apply. To get distinct projects you can always hit the API and parse the data returned. Keep in mind that this only returns projects that the calling user has access to.

Jira Server/Data Center:

• https://<your-jira-url>/rest/api/2/project
  
  

Jira Cloud:

• https://<your-company>.atlassian.net/rest/api/3/project/search?jql=&maxResults=<some-large-number>

Reference: https://community.atlassian.com/t5/Jira-questions/Jira-REST-API-to-get-projects/qaq-p/728218

[Om Joshi]

Hi @ Chander,

I am looking to fetch same information from my PostgreSQL database, 

Project Key

Project Description

Project Lead

Project Lead Email

Project Category

Project URL

Project Create Date

Project Name

have tried to work out from your suggested queries but unable to do so. could you please advise.

awaiting your reply.