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
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 Email
I hope you can help me out.
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
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)
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