Retrieving JIRA Database Statistics

Still need help?

The Atlassian Community is here for you.

Ask the community

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

The content on this page relates to platforms which are not supported. Consequently, Atlassian Support cannot guarantee providing any support for it. Please be aware that this material is provided for your information only and using it is done so at your own risk.

In the System Info page, the Database Statistics section shows the data information of an instance. 

Similar information can be found in the following files:

  •  atlassian-jira.log or the catalina.out file upon JIRA startup. 
     ___ Database Statistics ____________________
    
         Issues                                        : 1
         Projects                                      : 1
         Custom Fields                                 : 7
         Workflows                                     : 4
         Users                                         : 1
         Groups                                        : 6
         Attachments                                   : 0
         Comments                                      : 0
  <database-statistics>
    <Archived-Issues-.Total.>0</Archived-Issues-.Total.>
    <Archived-Projects>0</Archived-Projects>
    <Attachments>0</Attachments>
    <Comments>0</Comments>
    <Components>3</Components>
    <Custom-Fields>12</Custom-Fields>
    <Groups>6</Groups>
    <Issue-Security-Levels>0</Issue-Security-Levels>
    <Issue-Types>11</Issue-Types>
    <Issues>3</Issues>
    <Priorities>5</Priorities>
    <Projects>4</Projects>
    <Resolutions>4</Resolutions>
    <Screen-Schemes>11</Screen-Schemes>
    <Screens>30</Screens>
    <Statuses>10</Statuses>
    <Users>1</Users>
    <Versions>2</Versions>
    <Workflows>5</Workflows>
  </database-statistics>

How to retrieve Database Statistics from database?

The data is retrieved via the following queries.

--total number of users
SELECT COUNT(ID) FROM cwd_user; 
 
--total number of groups
SELECT COUNT(ID) FROM cwd_group; 
 
--total number of attachments 
SELECT COUNT(ID) FROM fileattachment;

--total number of issues
SELECT COUNT(ID) FROM jiraissue;

--total number of projects
SELECT COUNT(ID) FROM project;
 
--total number of comments
SELECT COUNT(ID) FROM jiraaction;

--total number of custom fields
SELECT COUNT(ID) FROM customfield;
 
--total number of issue security schemes
SELECT COUNT(ID) FROM issuesecurityscheme;
 
--total number of screen schemes
SELECT COUNT(ID) FROM fieldscreenscheme;
 
--total number of components
SELECT COUNT(ID) FROM component;
 
--total number of issue types
SELECT COUNT(ID) FROM issuetype;
 
--total number of priorities
SELECT COUNT(ID) FROM priority;
 
--total number of resolution
SELECT COUNT(ID) FROM resolution;
 
--total number of screens
SELECT COUNT(ID) FROM fieldscreen;

--total number of statuses
SELECT COUNT(ID) FROM issuestatus;
 
--total number of versions
SELECT COUNT(ID) FROM projectversion;

--total number of workflows
SELECT COUNT(ID) FROM workflowscheme;

--total number of archived issues
SELECT COUNT(ID) FROM jiraissue WHERE archived = 'Y';

--total number of archived projects
SELECT COUNT (p.id) from propertyentry pe join project p on pe.entity_id=p.id where property_key = 'jira.archiving.projects';  

Last modified on Jan 10, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.