Hey Keshni,
If you have access to Jira database you could pull data using following SQL Queries
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Does anyone have an Oracle Query?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Chander Inguva ,
Thank you so much for this!! It helped...
How can I can the project name rather than the project key?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Just add p.pname Keshni.
Glad i could help you
Thanks
Chander
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Good luck Keshni
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Chander wondering if you could come up with a query to get the distinct projects in Jira using JQL
Thanks! @Newelle Horn
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@[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:
Jira Cloud:
Reference: https://community.atlassian.com/t5/Jira-questions/Jira-REST-API-to-get-projects/qaq-p/728218
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.