Get the created date and last update date of the projects

[Raju Anumula]

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.

[Ignacio A]

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:

• project name
• project key
• project lead
• if that lead's account is active or inactive
• last issue update date
• last issue create date
• total number of issues
• date project was created

with rank_created as (
	select project,created,rank() over(partition by I.project order by I.created desc) as ranking
	from jiraissue I 
),
rank_updated as (
	select project,updated,rank() over(partition by I.project order by I.updated desc) as ranking
	from jiraissue I 
),
count_issues as (
	select project, count(*) as total 
	from jiraissue j group by project
),
users as (
	select user_name, lower_user_name, email_address, U.active ,rank() over(partition by U.user_name order by D.directory_position) as ranking
	from cwd_user U
	join cwd_directory D on U.directory_id = D.id and D.active = 1
)
select P.pname, P.pkey, U.user_name, U.email_address, U.active, RC.created, RU.updated, CI.total, to_timestamp(cast(A."ENTITY_TIMESTAMP" as bigint)/1000) as "Project created"
from project P
join "AO_C77861_AUDIT_ENTITY" A on "ACTION" = 'Project created' and A."PRIMARY_RESOURCE_ID" = cast(P.id as varchar)
join app_user AU on AU.user_key = P."lead" 
join users U on U.lower_user_name = AU.lower_user_name and U.ranking = 1
left join rank_created RC on RC.project = P.id and RC.ranking = 1
left join rank_updated RU on RU.project = P.id and RU.ranking = 1
left join count_issues CI on CI.project = P.id;

** 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

[Moses Ebrahimi]

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

[Moses Ebrahimi]

Please ignore me :) I was running the query on a wrong database

[Ste Wright]

Hi @Raju Anumula

One option for project created is to use the Audit Log, assuming it's activated - to access:

• Go to Jira Admin (cog in top-right corner) and press System
• Under System Support, select Audit Log

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

[Raju Anumula]

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.

[Eyal Gottlieb]

Hi,

Did you get a query for that?