How to find Projects Statistics based off Issue Type Schemes
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
In Jira, you can have issue type schemes to share issue types across multiple projects, have the benefit of sharing configurations and have a consistent instance across the board, making it easier to maintain and keep the projects standardized.
In some cases, administrators might be interested to identify which issue types are having the most issues created, and how many issues are within a set of projects. Jira doesn't provide that information natively, so it's necessary to query the database directly for that.
Environment
Jira 8 or higher;
The queries presented on this KB are for aPostgresdatabase and you may need to adjust them to your RDBMS.
Solution
Number of issues per issue type per project for a specific issue type scheme
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT p.pname as "Project Name"
,fcs.configname AS "Issue Type Scheme"
,it.pname as "Issue Type"
,COUNT(i.id) as "Issue Count"
FROM jiraissue i
INNER JOIN project p ON i.project = p.id
INNER JOIN issuetype it ON it.id = i.issuetype
INNER join configurationcontext cc on p.id = cc.project
INNER JOIN fieldconfigscheme fcs ON cc.fieldconfigscheme = fcs.ID
WHERE 1=1
AND fcs.configname = 'KANBAN: Kanban Issue Type Scheme'
GROUP BY p.id, it.id, fcs.configname
ORDER BY p.pname;This is an example output:
1
2
3
4
5
6
7
8
9
10
11
12
Project Name | Issue Type Scheme | Issue Type | Issue Count
-------------+----------------------------+------------------------------------------------------------------------+--------------------------------
SDE | KANBAN: Issue Type Scheme | Sub-task | 5709
SDE | KANBAN: Issue Type Scheme | Epic | 2282
SDE | KANBAN: Issue Type Scheme | Story | 2413
SDE | KANBAN: Issue Type Scheme | Bug | 2387
SDE | KANBAN: Issue Type Scheme | Task | 2425
TEST | KANBAN: Issue Type Scheme | Sub-task | 5461
TEST | KANBAN: Issue Type Scheme | Epic | 9999
TEST | KANBAN: Issue Type Scheme | Story | 4443
TEST | KANBAN: Issue Type Scheme | Bug | 2223
TEST | KANBAN: Issue Type Scheme | Task | 2211Was this helpful?