Retrieve Project information from Jira's database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below
Summary
You may need to pull some information from projects for reporting purposes and would like to get this directly from the database
Example data:
- 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
Solution
Choose the query below corresponding to your database and Jira version:
Jira 8.8+ with PostgreSQL
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
left 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;
Jira 8.8+ with MySQL 8.0
with rank_created as (
select project,created,rank() over(partition by I.project order by I.created desc) as ranking
from jiraissue I
group by project,created
),
rank_updated as (
select project,updated,rank() over(partition by I.project order by I.updated desc) as ranking
from jiraissue I
group by project,updated
),
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, FROM_UNIXTIME(cast(A.ENTITY_TIMESTAMP as unsigned integer)/1000) as "Project created"
from project P
left join AO_C77861_AUDIT_ENTITY A on ACTION = 'Project created' and A.PRIMARY_RESOURCE_ID = P.id
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;
Jira 8.7 or earlier with PostgreSQL
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, A."created" as "Project created"
from project P
left join "audit_log" A on "summary" = 'Project created' and A."object_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;
Last modified on Apr 10, 2024
Powered by Confluence and Scroll Viewport.