It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Report of all active Jira projects and project leads

Hi, 

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

Thank you :)

3 answers

1 accepted

0 votes
Answer accepted

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

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

Hi @Chander Inguva ,

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

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

Just add p.pname Keshni.

 

Glad i could help you

Thanks

Chander

Thank you :)

Good luck Keshni 

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

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 

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

Appreciate it.

 

Regards,

SreeKanth

Glad i could help you @SreeKanth 

 

Cheers

Chander

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) 

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

308 views 1 3
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you