Counting the number of JIRA and Confluence users

Unless you have one of the “unlimited” user licenses for Confluence & JIRA it’s important to keep a track on your user growth. The Atlassian applications don’t provide historic tracking of the user base growth so this is something which you need to introduce yourself.

JIRA

Within JIRA, seeing how many licenses are in use is relatively straightforward. By accessing the License Information Admin page (this is located at http://JIRA_ROOT/secure/admin/ViewLicense!default.jspa) at gives you the following information:

license info

So you can see here that we have 785 active users out of a limit of 2000, so nice an healthy.

Now I could gather this information on a weekly basis manually but that would be a PIA so how can I get the same information automatically?

Well there’s quite a simple database query for figuring out the number of active users:

SELECT COUNT(user_name) AS num_users
FROM
      cwd_user, cwd_membership
WHERE
      cwd_user.id = cwd_membership.child_id AND
      cwd_membership.membership_type='GROUP_USER' AND
      cwd_membership.parent_name='jira-users'

Confluence

So how about the same for Confluence?

Well within Confluence (4.3.x anyway) there’s no straightforward way through the GUI to identify the number of active users. The easiest way is therefore to go direct to the database.

In our case we’re using an MS-SQL database for our Confluence data storage so the query looks like:

SELECT count(DISTINCT cwd_user.lower_user_name) AS num_users
FROM 
     cwd_user INNER JOIN cwd_membership cwd_membership ON cwd_user.id = cwd_membership.child_user_id
     INNER JOIN cwd_group cwd_group ON cwd_membership.parent_id = cwd_group.id
     INNER JOIN spacepermissions spacepermissions ON cwd_group.group_name = spacepermissions.permgroupname
WHERE
      spacepermissions.permtype = 'USECONFLUENCE' AND cwd_user.active = 'T'