Report of all active Jira projects and project leads

Keshni March 19, 2019

Hi, 

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

Thank you :)

4 answers

1 accepted

3 votes
Answer accepted
Chander Inguva
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 19, 2019

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 November 12, 2019

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

1 vote
Robert Stumpe September 12, 2019

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) 

0 votes
Seenivasan Mohanraj March 25, 2024

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 

0 votes
Keshni March 19, 2019

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 19, 2019

Just add p.pname Keshni.

 

Glad i could help you

Thanks

Chander

Like Bipin Brahmanandan likes this
Keshni March 19, 2019

Thank you :)

Chander Inguva
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 19, 2019

Good luck Keshni 

SreeKanth July 11, 2019

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 11, 2019

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 

Like # people like this
SreeKanth July 12, 2019

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

Appreciate it.

 

Regards,

SreeKanth

Chander Inguva
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 12, 2019

Glad i could help you @SreeKanth 

 

Cheers

Chander

J Newelle Horn June 28, 2022

@chanderIng

 

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

 

Thanks!  @Newelle Horn 

Like # people like this
Matthew Knatz July 6, 2022

@J Newelle Horn 

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

Like Robert Stumpe likes this
Om Joshi July 20, 2022

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.

Suggest an answer

Log in or Sign up to answer