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.

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 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 Sep 22, 2022

Was this helpful?

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