nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Chander Inguva

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

All comments

Chander Inguva

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

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

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)

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

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

Markeese Martin

Does anyone have an Oracle Query?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

jira-server

query