Hello Team,
How to get the list projects with the project created date and when is the project issue last updated.
Database: Postgres
Thanks in Advance.
Hi @Raju Anumula
One option for project created is to use the Audit Log, assuming it's activated - to access:
You can view when one project was created using this - or export the data (button in upper-right) and review it for more than one project.
For when it last had an issue updated, I would just export all the issues for the selected projects from the issue search and locate when the last issue update was performed for each.
Alternatively you might be able to query the database your instance sits on for this data - depends on your setup.
Ste
Hello @Ste Wright ,
we have 700+ projects in our Jira instance, difficult to get details from the audit log.
I am looking for a database query to get the project list and last update in project.
we are using Postgres.
Thanks in advance.
Hi,
Did you get a query for that?
Hey @Raju Anumula and @Eyal Gottlieb
I know it's been a long time, but I'll leave this query here for anyone that needs it.
This works in posgres and Jira 8.8+
** This one will return:
<span>with</span> rank_created <span>as</span> <span>(</span> <span>select</span> project<span>,</span>created<span>,</span>rank<span>(</span><span>)</span> <span>over</span><span>(</span><span>partition</span> <span>by</span> I<span>.</span>project <span>order</span> <span>by</span> I<span>.</span>created <span>desc</span><span>)</span> <span>as</span> ranking <span>from</span> jiraissue I <span>)</span><span>,</span> rank_updated <span>as</span> <span>(</span> <span>select</span> project<span>,</span>updated<span>,</span>rank<span>(</span><span>)</span> <span>over</span><span>(</span><span>partition</span> <span>by</span> I<span>.</span>project <span>order</span> <span>by</span> I<span>.</span>updated <span>desc</span><span>)</span> <span>as</span> ranking <span>from</span> jiraissue I <span>)</span><span>,</span> count_issues <span>as</span> <span>(</span> <span>select</span> project<span>,</span> <span>count</span><span>(</span><span>*</span><span>)</span> <span>as</span> total <span>from</span> jiraissue j <span>group</span> <span>by</span> project <span>)</span><span>,</span> users <span>as</span> <span>(</span> <span>select</span> user_name<span>,</span> lower_user_name<span>,</span> email_address<span>,</span> U<span>.</span>active <span>,</span>rank<span>(</span><span>)</span> <span>over</span><span>(</span><span>partition</span> <span>by</span> U<span>.</span>user_name <span>order</span> <span>by</span> D<span>.</span>directory_position<span>)</span> <span>as</span> ranking <span>from</span> cwd_user U <span>join</span> cwd_directory D <span>on</span> U<span>.</span>directory_id <span>=</span> D<span>.</span>id <span>and</span> D<span>.</span>active <span>=</span> <span>1</span> <span>)</span> <span>select</span> P<span>.</span>pname<span>,</span> P<span>.</span>pkey<span>,</span> U<span>.</span>user_name<span>,</span> U<span>.</span>email_address<span>,</span> U<span>.</span>active<span>,</span> RC<span>.</span>created<span>,</span> RU<span>.</span>updated<span>,</span> CI<span>.</span>total<span>,</span> to_timestamp<span>(</span>cast<span>(</span>A<span>.</span><span>"ENTITY_TIMESTAMP"</span> <span>as</span> <span>bigint</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span>as</span> <span>"Project created"</span> <span>from</span> project P <span>join</span> <span>"AO_C77861_AUDIT_ENTITY"</span> A <span>on</span> <span>"ACTION"</span> <span>=</span> <span>'Project created'</span> <span>and</span> A<span>.</span><span>"PRIMARY_RESOURCE_ID"</span> <span>=</span> cast<span>(</span>P<span>.</span>id <span>as</span> <span>varchar</span><span>)</span> <span>join</span> app_user AU <span>on</span> AU<span>.</span>user_key <span>=</span> P<span>.</span><span>"lead"</span> <span>join</span> users U <span>on</span> U<span>.</span>lower_user_name <span>=</span> AU<span>.</span>lower_user_name <span>and</span> U<span>.</span>ranking <span>=</span> <span>1</span> <span>left</span> <span>join</span> rank_created RC <span>on</span> RC<span>.</span>project <span>=</span> P<span>.</span>id <span>and</span> RC<span>.</span>ranking <span>=</span> <span>1</span> <span>left</span> <span>join</span> rank_updated RU <span>on</span> RU<span>.</span>project <span>=</span> P<span>.</span>id <span>and</span> RU<span>.</span>ranking <span>=</span> <span>1</span> <span>left</span> <span>join</span> count_issues CI <span>on</span> CI<span>.</span>project <span>=</span> P<span>.</span>id<span>;</span>
** This one will return PROJECT KEY | PROJECT LEAD | LAST UPDATED
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;
** In my case it's not returning the full list (274 project out of 359). I'm getting the actual number with this simple query:
SELECT COUNT(ID) FROM project;
I hope that helps
Cheers
Hi @Ignacio A
I also came across this task to pull Jira projects last update etc, but for some reason I get "SQL Error [42P01]: ERROR: relation "jiraissue" does not exist"
Its Jira 8.2 and postgresql. any thoughts?
Thank you
Moses
Please ignore me I was running the query on a wrong database
It looks like you're new here. Sign in or register to get started.
