Retrieve Project information from the database of Jira Server or Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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;Was this helpful?